import excel data into access table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ABCBAC - 11 Nov 2006 08:37 GMT

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

Reply to this Message

Stefan Hoffmann - 11 Nov 2006 11:04 GMT

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 <--

Reply to this Message

onedaywhen - 13 Nov 2006 15:59 GMT

> 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.

--

Reply to this Message