ADODB recordset in VBA says excel field is empty when it's not

Question

I have an excel sheet I need to import in my Access database. The sheet looks like this:

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

I then transfer this file into 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: certain fields have text in them. The field value should then be item0001, but it's reportedly NULL

When the field has a regular number it works fine.

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

If you're running this code within Access, why not just use DoCmd.TransferSpreadsheet? — David-W-Fenton
– David-W-Fenton, Commented Sep 27, 2010 at 17:51

GSerg · Accepted Answer · 2010-01-14 16:51:21Z

5

Be more specific in your Extended Properties portion (and don't omit inner quotes there).

In particular, try Extended Properties="Excel 8.0;HDR=Yes;IMEX=1" to allow mixed numbers&text data.

More details at http://connectionstrings.com/.

answered Jan 14, 2010 at 16:51

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ADODB recordset in VBA says excel field is empty when it's not

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related