Hi there:

I'm transfering an Excel spreadsheet into an Access table. I thought I'd copy the spreadsheet to a recordset and then transfer this into the table.

The first few pieces of data in one of the fields I'm transfering are: 1, 1, 2, 2, 3, 3. These show up in the Access table no problem. The next couple are: 1B, 1C. These also show up. For the remaining 300 + records, if the data is made up of letters alone or letters and numbers (eg 1B), it transfers fine. If it is a number alone, I get a blank.

I think I've established that the problems is in the recordset and where these numbers should be I just have nulls. All the other fields transfer fine. Anyone have any ideas about what's going on? Any workarounds? Data set's not particularly big and never will be - perhaps I shouldn't be using a recordset?

Thanks!

James

Hi all:

I solved this problem today - the solution was to change the connection string to something like this:



My previous connection string did not include the IMEX=1 extended properties. On the MSDN forum where I found the solution, it said that setting the IMEX=1 has the effect of telling "the driver to always read "intermixed" data columns as text". That's the effect it had for me; it's actually a little more complicated than that, as you can read here:

PRB: Excel Values Returned as NULL Using DAO OpenRecordset

Thanks for all your suggestions - UA rules!

James