VBA ADO Major error

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

[George Nicholson]

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

--
George Nicholson

Remove 'Junk' from return address.


"Peter Lux" <[email protected]> wrote in message
news:[email protected]...
> 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.
>