Trying to connect to MySQL db with DAO using ODBC

On Fri, 28 Mar 2008 13:26:30 -0500, "Ralph"
<nt_consulting64 RemoveThis @yahoo.com> wrote:

>
>"Martin" <ironwoodcanyon RemoveThis @gmail.com> wrote in message
>news:s4cqu3p1ouihgl41b4d3nd77p6knkgmvt0@4ax.com...
>> 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/en/myodbc-examples-programming.html#myodbc-examples-programming-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
>
>You didn't show the connection string; I assume from the link you are using
>the "MySQL ODBC 3.51 Driver". But which one?
>
>The first thing I would do, is download as many of the last couple of driver
>versions I could find (eg, MyODBC 3.51.04 and 3.51.10, etc.).
>Then open the ODBC Data Source Administrator and see if I could create a
>working DSN from one of the drivers. (Makes it easy to select drivers, build
>the connection, and test all in one place.) And piddle till I found one that
>worked.
>
>MySQL Driver builds/versions are notorious for not working across O/Ss, SPs,
>and Database versions. Don't be surprised that what works with ADO will not
>work with DAO and vice versa. You might even consider going back to DAO 3.5.
>
>hth
>-ralph
>

Yes, the connection string I'm using is copied from the example in the
link with only the UID and PWD changed to match the real ones.

The ODBC driver I'm using is version 3.51.24.00 I downloaded (from
MySQL.com) and installed it just this morning. I didn't know there
were different versions available - I assume this is the latest thing.

I did as you suggested and created a DSN. It worked perfectly! I then
went back to my VB programs and used the DSN to connect (instead of
the connection string) and, guess what, they worked, too !!

So, I'm guessing that, for some unknown reason, I *have* to use a DSN
to make this whole thing work. But, I'd rather go DSN-less.

Thanks for the help. Greatly appreciated.
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

OK - now that I'm connected to the database, I'm trying to adapt the
various procedures over to the world of MySQL. I've run into another
stumbling block.

One of my tables has two indexes. Before I do a "seek" on this table,
I'm specifying the index to use, thus:

rsMainData.Index = "Barcode"
rsMainData.Seek "=", SomeValue$
If rsMainData.NoMatch Then
' do something...
Else
' do something different
End If

But, with the ODBC connection to the MySQL table, the statement
specifying the index generates an error.

Is it possible to do this? Am I just using the wrong syntax?
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

"Martin" <ironwoodcanyon.RemoveThis@gmail.com> wrote in message
news:1fiqu3pg5c5ppjjahj297rn74ckleejr0b@4ax.com...
> On Fri, 28 Mar 2008 13:26:30 -0500, "Ralph"
> <nt_consulting64.RemoveThis@yahoo.com> wrote:
>
<snipped>
>
> Yes, the connection string I'm using is copied from the example in the
> link with only the UID and PWD changed to match the real ones.
>
> The ODBC driver I'm using is version 3.51.24.00 I downloaded (from
> MySQL.com) and installed it just this morning. I didn't know there
> were different versions available - I assume this is the latest thing.
>
> I did as you suggested and created a DSN. It worked perfectly! I then
> went back to my VB programs and used the DSN to connect (instead of
> the connection string) and, guess what, they worked, too !!
>
> So, I'm guessing that, for some unknown reason, I *have* to use a DSN
> to make this whole thing work. But, I'd rather go DSN-less.
>
> Thanks for the help. Greatly appreciated.

I should have mentioned it - More than one person has reported that a DSN
works while the 'apparently' equivalent DSN-less connection string does not.

Just a guess on my part, but you might try and open the DSN file and then in
your code set or add all the properties (name and value) exactly as you see
them in the file. Spelling included. And remove any properties that you
might have thought was a good idea at the time that aren't in the file.

As you discovered the latest 'n greatest when it comes to MySQL drivers is
not always the best strategy. If you go to the website you will find almost
an endless stream of 'build' versions - each fixing something and then just
as likely, unfortunately, breaking something else.

It usually doesn't take long to fix - just find the right match. The real
problem is if you work in a multiple developer environment, sooner or later
somebody is going to be 'helpful' and 'upgrade', then mysterious things
start happening. Be sure to bracket your code with multi-lined comments that
say: "DON'T TOUCH THIS". <bg>

-ralph
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

"Martin" <ironwoodcanyon DeleteThis @gmail.com> wrote in message
news:tjuqu3phc1r1aanoqhoqo5r77hibct535c@4ax.com...
>
> OK - now that I'm connected to the database, I'm trying to adapt the
> various procedures over to the world of MySQL. I've run into another
> stumbling block.
>
> One of my tables has two indexes. Before I do a "seek" on this table,
> I'm specifying the index to use, thus:
>
> rsMainData.Index = "Barcode"
> rsMainData.Seek "=", SomeValue$
> If rsMainData.NoMatch Then
> ' do something...
> Else
> ' do something different
> End If
>
> But, with the ODBC connection to the MySQL table, the statement
> specifying the index generates an error.
>
> Is it possible to do this? Am I just using the wrong syntax?
>

As this is a more a MySQL question you might want to cross-post to
comp.databases.mysql
mysql.general
You will likely find more MySQL users there.

-ralph
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

On Sat, 29 Mar 2008 09:36:34 -0500, "Ralph"
<nt_consulting64.DeleteThis@yahoo.com> wrote:

>
>"Martin" <ironwoodcanyon.DeleteThis@gmail.com> wrote in message
>news:tjuqu3phc1r1aanoqhoqo5r77hibct535c@4ax.com...
>>
>> OK - now that I'm connected to the database, I'm trying to adapt the
>> various procedures over to the world of MySQL. I've run into another
>> stumbling block.
>>
>> One of my tables has two indexes. Before I do a "seek" on this table,
>> I'm specifying the index to use, thus:
>>
>> rsMainData.Index = "Barcode"
>> rsMainData.Seek "=", SomeValue$
>> If rsMainData.NoMatch Then
>> ' do something...
>> Else
>> ' do something different
>> End If
>>
>> But, with the ODBC connection to the MySQL table, the statement
>> specifying the index generates an error.
>>
>> Is it possible to do this? Am I just using the wrong syntax?
>>
>
>As this is a more a MySQL question you might want to cross-post to
> comp.databases.mysql
> mysql.general
>You will likely find more MySQL users there.
>
>-ralph
>

Well, actually, I asked my original question (that started this
thread) over on comp.databases.mysql but never got a reply.
Apparently, it's not a very active board.

I'll get it a try on mysql.general

Thanks.
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

"Martin" <ironwoodcanyon.TakeThisOut@gmail.com> wrote in message
news:tjuqu3phc1r1aanoqhoqo5r77hibct535c@4ax.com...
>
> OK - now that I'm connected to the database, I'm trying to adapt the
> various procedures over to the world of MySQL. I've run into another
> stumbling block.
>
> One of my tables has two indexes. Before I do a "seek" on this table,
> I'm specifying the index to use, thus:
>
> rsMainData.Index = "Barcode"
> rsMainData.Seek "=", SomeValue$
> If rsMainData.NoMatch Then
> ' do something...
> Else
> ' do something different
> End If
>
> But, with the ODBC connection to the MySQL table, the statement
> specifying the index generates an error.
>
> Is it possible to do this? Am I just using the wrong syntax?
>

As this is a more a MySQL question you might want to cross-post to
comp.databases.mysql
mysql.general
You will likely find more MySQL users there.

-ralph
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC 

"Martin" <ironwoodcanyon RemoveThis @gmail.com> wrote in message
news:mipsu39nok1bp118fnvnu8c2gonuemjjj0@4ax.com...
> On Sat, 29 Mar 2008 09:36:34 -0500, "Ralph"
> <nt_consulting64 RemoveThis @yahoo.com> wrote:
>
> >
> >"Martin" <ironwoodcanyon RemoveThis @gmail.com> wrote in message
> >news:tjuqu3phc1r1aanoqhoqo5r77hibct535c@4ax.com...
> >>
> >> OK - now that I'm connected to the database, I'm trying to adapt the
> >> various procedures over to the world of MySQL. I've run into another
> >> stumbling block.
> >>
> >> One of my tables has two indexes. Before I do a "seek" on this table,
> >> I'm specifying the index to use, thus:
> >>
> >> rsMainData.Index = "Barcode"
> >> rsMainData.Seek "=", SomeValue$
> >> If rsMainData.NoMatch Then
> >> ' do something...
> >> Else
> >> ' do something different
> >> End If
> >>
> >> But, with the ODBC connection to the MySQL table, the statement
> >> specifying the index generates an error.
> >>
> >> Is it possible to do this? Am I just using the wrong syntax?
> >>
> >
> >As this is a more a MySQL question you might want to cross-post to
> > comp.databases.mysql
> > mysql.general
> >You will likely find more MySQL users there.
> >
> >-ralph
> >
>
> Well, actually, I asked my original question (that started this
> thread) over on comp.databases.mysql but never got a reply.
> Apparently, it's not a very active board.
>
> I'll get it a try on mysql.general
>
> Thanks.

Sorry I can't help as I only occasionally work with clients that use MySQL.
I'm experienced with connection/enterprise issues, but very little with the
actual internal workings itself. My gut reaction is you can only have one
index, but I really don't know.

As popular as MySQL seems to be, I've noted that 'helpers' for it seems a
little sparse. But they must be out there somewhere. If you find a good
site, please come back and let me know. I would like to be able to properly
re-direct people.

-ralph
 >> Stay informed about: Trying to connect to MySQL db with DAO using ODBC