VBA Access/Excel Recordset problem

Saint_Mel

Been a while since I was programming but just trying to write a small Access/VBA app and have enountered an annoying problem. Probably something simple I'm forgetting but cant figure it out. Can anyone help?

MS Acccess app will read in an Excel sheet and then populate a database table with some of the data.

Setup ...

Access table: ID(Number), Type (Text), Description (Text)

Excel: Cell format is General for all fields and changing to Text manually isnt an option. Example of data
ID.....TYPE...DESC
1..........A.......abcdefg
2..........A.......hijklmnop
3..........A.......qrstuvwxyz
4..........A.......1
5..........A.......2
6..........A.......3


CODE:

rsDetails.Open "SELECT * FROM [SHEET1$]", sConnectionString
sID = rsDetails.Fields(0)
sType= rsDetails.Fields(1).Value
sDescription = rsDetails.Fields(2).Value

All seems easy enough except when I run this code in a loop until EOF is true
the Description value for records 1,2 and 3 are read in as NULL

However, when I edit the Excel sheet and change Description of row 4,5 or 6 in the spreadsheet to a letter/string of letter ... then the records with text for Description are fine but the ones containing a number are read in as NULL?

Ie: run the code with the data as above and it reads into the recordset as
rsDetails.Fields(0).Value = 1
rsDetails.Fields(1).Value = A
rsDetails.Fields(2).Value = NULL

same for record 2 and 3 ... and then

rsDetails.Fields(0).Value = 4
rsDetails.Fields(1).Value = A
rsDetails.Fields(2).Value = 1

If I change the excel data as so:

ID.....TYPE...DESC
1..........A.......abcdefg
2..........A.......hijklmnop
3..........A.......qrstuvwxyz
4..........A.......xxxxxxx
5..........A.......2
6..........A.......3

and run the same code, then all is fine until it reaches row 5, then the data is
rsDetails.Fields(0).Value = 5
rsDetails.Fields(1).Value = A
rsDetails.Fields(2).Value = NULL

??? Any ideas?

Saint_Mel

Seems to be something to do with the fomat of the cells in Excel being General, bit it only seems to affect that particular column?

Ginger

Fairly sure its to do with Excel and its attempt to identify the datatype from the first couple of rows...

In you connection string, add the option IMEX=1;

Also see this support page

Should sort it out for you

Saint_Mel

Added the Extended option for IMEX but still the same NULL returned.
Cheers for that tip though, hadnt realised that option before.

Ginger

OK, check the number of rows to use to verifiy the data type.. check the reg key to find that

Saint_Mel

Possible silly question but which reg key. I';ve searched for the one listed in the support pages but no joy and also searched for IMEX but nothing?

Also, as the format is the same for all excel rows, I have changed values in the ID to characters and numbers but the NULL value still just appears for 1 specific coloumn?

Ginger

Oki doki then

Try the following keys

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

TypeGuessRows: setting the value to 0 (zero) will force ADO to scan
all column values before choosing the appropriate data type.

ImportMixedTypes: should be set to value 'Text' i.e. import mixed-type
columns as text:

Using IMEX=1 in the connection string (as you have done) ensures the
registry setting is applied.

Saint_Mel

Cool. got those and they are set as per your post.

Getting an automation error now though, Cannot update. Database ot object is read only.
For my Connection
CursorType = adOpenDynamic and LockType = adLockOptimistic

Ginger

Set your keylock to ForwardOnly ..

Saint_Mel

Same error given back

Ginger

Remove the cursor and locktype declarations ..

Saint_Mel

Tried that, and every combination of cursor and lock type but still getting the same error