import excel data into access table

[ABCBAC]

Dear All,

I used ADO , to connect to excel file and read it row by row , i has a
problem of text filed in excel, ex: if the field has values like
'abac' , ,my vba module return a null value, if the field has value
like '43cxcx' it return correct as it is???

Can some experienced this ..? Please help

thanks
Bac

 

[Stefan Hoffmann]

hi,

I used ADO , to connect to excel file and read it row by row , i has a
problem of text filed in excel,

Using ADO or Jet for reading Excel data has some quirks:

a) The data type of each column is determined only by inspecting the
first five data cells without the optional header row.
So this may result in wrong data types.

b) There are some kinds of formattings which lead to misinterpreted data.

What do you mean with row by row:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * " & _
"FROM [Excel 8.0;Database=YourFile;Hdr=Yes].[Sheet1$]"
?

ex: if the field has values like
'abac' , ,my vba module return a null value, if the field has value
like '43cxcx' it return correct as it is???

Maybe you can post your code?


mfG
--> stefan <--

 

[onedaywhen]

a) The data type of each column is determined only by inspecting the
first five data cells without the optional header row.
So this may result in wrong data types.

b) There are some kinds of formattings which lead to misinterpreted data.

Things are not as simple as you seem to think: you may have your
TypeGuessRows registry key set to 5 but I certainly don't

Take a look at:

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

Jamie.

--