VBA ADO Major error

P

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

George Nicholson

A shot in the dark, but I'd double check the DSN definition you are using
for connection.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top