VBA ADODB Record Set Says Excel Field Is Empty When It Is Not

I have an Excel worksheet that I need to import into my Access database. The sheet is as follows:

DATE RECEPTION DENOMINATION ITEM N ° QUANTITE RECUE
01/06/2010 DVD-Sex & the City PCR-PA21550167 5
01/06/2010 DVD-Avatar Natie 2 PCR-PA21550209 10

Then I transfer this file to the database using adodb:


Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim intField As Integer
Dim strFile As String

strFile = fncOpenFile
If strFile = "" Then Exit Sub

With cnn2
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strFile& "; " & "Extended Properties=Excel 8.0"
    .Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [PCR$]"
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenDynamic
rs2.LockType = adLockOptimistic

rs2.Open cmd2

While Not rs2.EOF
        strNaam = rs2.Fields(3).Value
Loop

Now my problem: some fields have text in them. The field value must be item0001, but is reported to be NULL

When a field has a regular number, it works great.

The strange thing is: there are other text fields in the worksheet, and they work FINE.

+1
source share
1 answer

" " ( ).

, Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" .

http://connectionstrings.com/.

+4

All Articles