MySQL and ADO

[zMisc]

I am looking at using MySQL in my application (currently works with Access
and SQL Server 2000).

Is it possible to access MySQL database using ADO?

What is the connection string for this?

Also, it is possible to change/modify the table structure/create new
tables/indexes via Visual basic codes?

Any help greatly appreciated.

Tks
JY

[Thomas Bartkus]

"zMisc" <yo********@hot mail.com> wrote in message
news:F5******** **********@news-server.bigpond. net.au...

I am looking at using MySQL in my application (currently works with Access
and SQL Server 2000).

Is it possible to access MySQL database using ADO?
Of course :-)
You will want the MySQL ODBC driver available at
http://dev.mysql.com/downloads/
Look for "Connector/ODBC 3.51"
-download, then install.
What is the connection string for this?
First use the Windows control panel _administrative Tools_Data Sources
(ODBC) dialog to "Add" a new System DSN (Data Source Name). When you select
the "MySQL ODBC 3.51" driver, you get a dialog box to fill in relevant
information and select parameters for a connection. Hit the [Test] button
to see if you can connect.

Now you have a DSN you can use to connect. You can use that just fine. But
if you want to code lower,this is what I do to "discover" a proper
connection string. I use Access as a tool.

1) create a new query in design view.
2) right click in the design panel and from the submenu select "SQL
Specific- PassThrough". This get's you a blank window to enter sql text.
3) Right click on the window title bar and select [Properties] from the sub
menu.
4) Plant your cursor in the "ODBC Connect Str" box.
5) Click on the dialog expansion button to the right.
6) Pick the "Machine Data Sources" tab from the dialog box that pops up.
7) Select the DNS that you created above.
8) When it asks you to save the pasword in the connection string - say yes.

Now a complete and proper connection string is entered for you in the text
box. I just copy that.

I do suggest you enter a simple query and test it to be sure the connection
string works.

ODBC;DATABASE={ default database name};DESCRIPTI ON={some optional
description};DR IVER=MySQL ODBC 3.51 Driver;
OPTION={nnnn};P WD={password};P ORT={nnnn};SERV ER={nn.nn.nnn.n n};UID={user
name}

This rigamarole is particularly useful to get at that OPTION integer value.
It is determined by the options you set when you created the DSN using the
ODBC setup dialog. I know of no other way to figure out what that integer
value should be, although it's probably documented somewhere :-) Were it
not for that mystery, I could just fill in the connection string by hand.
Also, it is possible to change/modify the table structure/create new
tables/indexes via Visual basic codes?

I'm not sure how how much you can get away with using VB, ADODB and ADOX
libraries.
But what you can *always* do is pass SQL statements *directly* to MySQL.

You can define pass-through queries in Access and create PassThrough type
recordsets in VB which simply pass along your SQL to MySQL with no
interpretation by Microsoft software at all. This means that any legal
MySQL statements can be executed.

- which means you can do anything MySQL permits :-)
Thomas Bartkus