Major ADO error between Excel and Access?

[Peter Lux]

I've been looking at this all day and I'm about ready to scream.
I have a form in Excel 2003 that pulls data from Access 2003. I'm using ADO
and can connect to the database, no problem. The problem is the data fetch
is WRONG. (Like "buggy" wrong):

Let's say I have a table with 3 character (text) fields in them, column1 is
10chars wide, column2 is 15 chars, column3 is 3 wide
Now data looks like:
column1 column2 column3
foo boodle drr
and in Excel I do
Dim cAcc As New ADODB.Connection
Dim rsAcc As New ADODB.Recordset
Dim cx as String
cx = "DSN=nudbodbc;user=sa"
cAcc.Open cx
Set rsAcc = cAcc.Execute(" Select column1, column2, column3 from table1")
TextBox1.Text = rsAcc(0)
TextBox2.Text = rsAcc(1)
TextBox3.Text = rsAcc(2)

'This is where it's WRONG'
Right from the immediate window in VBA:
rsAcc(0):
foo 
rsAcc(1):
boodle  dr (WTF?! should be just 'boodle' and yes the [] things show in
the text box)
rsAcc(2):
dr (Again WTF? it should be 'drr')

It looks like it grabs the field length and fills in with whatever data it
has REGARDLESS of which column it came from. That's a major error if you ask
me.

I've duplicated this in more than one database, so I know it's not the sid
that's the problem. I've dropped and recreated the table so that's not it.
I've also tried various itterations of rsAcc.Fields(i) and
rsAcc.Fields.Item(i) and get the same result both times. I've tried other
tables, "fetch everything" statements. It seems NOT to affect numeric
columns though. (WTF?!)
Is this a service pak issue? Has anyone else seen this buggy behaviour.

 

[Brian Wilson]

I've been looking at this all day and I'm about ready to scream.
I have a form in Excel 2003 that pulls data from Access 2003. I'm using
ADO
and can connect to the database, no problem. The problem is the data fetch
is WRONG. (Like "buggy" wrong):

Let's say I have a table with 3 character (text) fields in them, column1
is
10chars wide, column2 is 15 chars, column3 is 3 wide
Now data looks like:
column1 column2 column3
foo boodle drr
and in Excel I do
Dim cAcc As New ADODB.Connection
Dim rsAcc As New ADODB.Recordset
Dim cx as String
cx = "DSN=nudbodbc;user=sa"
cAcc.Open cx
Set rsAcc = cAcc.Execute(" Select column1, column2, column3 from table1")
TextBox1.Text = rsAcc(0)
TextBox2.Text = rsAcc(1)
TextBox3.Text = rsAcc(2)

'This is where it's WRONG'
Right from the immediate window in VBA:
rsAcc(0):
foo 
rsAcc(1):
boodle  dr (WTF?! should be just 'boodle' and yes the [] things show
in
the text box)
rsAcc(2):
dr (Again WTF? it should be 'drr')

It looks like it grabs the field length and fills in with whatever data it
has REGARDLESS of which column it came from. That's a major error if you
ask
me.

I've duplicated this in more than one database, so I know it's not the sid
that's the problem. I've dropped and recreated the table so that's not it.
I've also tried various itterations of rsAcc.Fields(i) and
rsAcc.Fields.Item(i) and get the same result both times. I've tried other
tables, "fetch everything" statements. It seems NOT to affect numeric
columns though. (WTF?!)
Is this a service pak issue? Has anyone else seen this buggy behaviour.

cx = "DSN=nudbodbc;user=sa"
This doesn't help us much, as we don't know what my be contained in that
dsn. However, if you are creating an ADO connection to an mdb file, why
would would you not use a connection string like the following:
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyData.mdb;" & _
"User Id=admin;Password="""";"

Can I also make sure that you are trying to open data that is held in the
mdb file, not in a linked table (user=sa? SQL Server?)

When you say that your columns are x wide, have you created them with the
normal Access GUI? Normally with Access, you define a field with a maximum
length of 10 rather than a fixed width, but you could by-pass the GUI and
write:
CREATE TABLE MyTable (F1 CHAR(10), ...
to create a fixed-width table. Is that what you have done?

Of course, even if that's what you did, the string should be padded with
spaces, and I just checked on my machine to show that's what happens.
Perhaps you could let us know how you put the test data in there...

 

[Peter Lux]

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyData.mdb;" & _
"User Id=admin;Password="""";"

I'll try to do a direct connect 2 see if the connection is what's causing
the fetch. I've always created an ODBC connection before and never had a
problem.

Can I also make sure that you are trying to open data that is held in the
mdb file, not in a linked table (user=sa? SQL Server?)

No, it's not linked. In fact I created a new mdb just to be sure.

When you say that your columns are x wide, have you created them with the
normal Access GUI? Normally with Access, you define a field with a
maximum length of 10 rather than a fixed width, but you could by-pass the
GUI and write:
CREATE TABLE MyTable (F1 CHAR(10), ...
to create a fixed-width table. Is that what you have done?

Originally, this was happening on a Customer table that has data coming from
our production Sqlbase database. It was created as a table is based on
schema I have in the production database. I thought it might be the way that
table was created so I created "table1" as per my original. I created as a
'create table' sql statement, not through the interface. So I have a fixed
width on some of the columns. Additionally, the data for the original
Customer table is 'inserted' into the access db thru a interface I coded in
SqlWindows. I even went back to it and made sure I was doing " trim
(fieldname) " before the insert. Didn't make a difference. So it's affected
whether or not the data comes from another database or inserted through
Access.

And like you said:

Of course, even if that's what you did, the string should be padded with
spaces, and I just checked on my machine to show that's what happens.
Perhaps you could let us know how you put the test data in there...

For the 'foo/boodle/drr' data, I just entered it into datasheet view in
Access.

 

[Peter Lux]

I'll try to do a direct connect 2 see if the connection is what's causing
the fetch. I've always created an ODBC connection before and never had a
problem.

That seems to be the reason. I checked my ODBC Access version and it says
4.00.6304.00 WHICH would lead you to believe that it would work with Access
2003.

As soon as I made the connection with the above, then it worked fine. Is my
odbc driver version the 'Access 2000' version??

 

[Brian Wilson]

That seems to be the reason. I checked my ODBC Access version and it says
4.00.6304.00 WHICH would lead you to believe that it would work with
Access 2003.

As soon as I made the connection with the above, then it worked fine. Is
my odbc driver version the 'Access 2000' version??

Hi Peter
Glad you got it fixed. I'm afraid I'm not the best person to ask for this
sort of setup question, but if you look at any recent example coding to
create an ADODB.Connection to an mdb file and extract some data, you will
find odbc is no longer used. There must be published material discussing
the pros and cons of odbc versus oledb, but I always use a connection string
in the above-mentioned format and have never had much reason to question
it.