Trouble connecting to MySQL db from VB6 using ODBC

I've installed MySQL (version 5.0.51a) and am trying to learn to use
it in a program written in Visual Basic 6. The VB6 program has been
using both ADO and DAO to manipulate data in an MS Access MDB file.

Using the examples shown here:
http://dev.mysql.com/doc/refman/5.0/...ramming-vb-ado
I'm trying to use ODBC to access the same data (I imported it into the
MySQL database) and have succeeded with the ADO part. But, I'm getting
an error when I try to open a DAO connection (it's an unspecified
error - a message box pops up that contains only a red "X" - no error
message).

To track this down a little further, I copied the example code from
the page linked above into a new VB program. When it gets to the line:
Set conn = ws.OpenConnection(..., it generates a run time error 3146 -
"ODBC - call failed".

I've googled around quite a bit on this but haven't been able to find
a resolution. One thing said to be sure that MDAC is up-to-date.
AFAIK, it is - although I don't know how to check that specifically
(the computer itself is running WinXP Pro with all service packs and
updates on it).

FWIW, I'm using the DAO 3.6 object library.

Anyone here have any experience with this situation that might be able
to point out what I'm missing here?

Thanks

On Fri, 28 Mar 2008 10:31:36 -0700, Martin <ironwoodcanyon@gmail.com>
wrote:

>I've installed MySQL (version 5.0.51a) and am trying to learn to use
>it in a program written in Visual Basic 6. The VB6 program has been
>using both ADO and DAO to manipulate data in an MS Access MDB file.
>
>Using the examples shown here:
>http://dev.mysql.com/doc/refman/5.0/...ramming-vb-ado
>I'm trying to use ODBC to access the same data (I imported it into the
>MySQL database) and have succeeded with the ADO part. But, I'm getting
>an error when I try to open a DAO connection (it's an unspecified
>error - a message box pops up that contains only a red "X" - no error
>message).
>
>To track this down a little further, I copied the example code from
>the page linked above into a new VB program. When it gets to the line:
>Set conn = ws.OpenConnection(..., it generates a run time error 3146 -
>"ODBC - call failed".
>
>I've googled around quite a bit on this but haven't been able to find
>a resolution. One thing said to be sure that MDAC is up-to-date.
>AFAIK, it is - although I don't know how to check that specifically
>(the computer itself is running WinXP Pro with all service packs and
>updates on it).
>
>FWIW, I'm using the DAO 3.6 object library.
>
>Anyone here have any experience with this situation that might be able
>to point out what I'm missing here?
>
>Thanks

OK - got it working.

If I use a DSN, it all works properly. I didn't particulary want to
use a DSN but, if that's the only way to do it...