473,378 Members | 1,482 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.


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.


Dec 8 '05 #1
1 13881
"zMisc" <yo********@hotmail.com> wrote in message
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
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
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};DESCRIPTION={some optional
description};DRIVER=MySQL ODBC 3.51 Driver;
OPTION={nnnn};PWD={password};PORT={nnnn};SERVER={n n.nn.nnn.nn};UID={user

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

Dec 8 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: francescomoi | last post by:
Hi. I'm trying to build 'MySQL-python-1.2.0' on my Linux FC2: ---------------------------------- # export PATH=$PATH:/usr/local/mysql/bin/ # export mysqlclient=mysqlclient_r # python setup.py...
by: mikey | last post by:
Hi all, I'm having great problems trying to install the latest MySQl RPM package onto my Red Hat Linux OS. There is already MySQL v 3.0 pre-installed with the RH Linux distribution disk but I...
by: Yun Guan | last post by:
Hello mysql gurus, I am trying to run perl on mysql database on Red Hat box. I want to install DBI and DBD:mysql using CPAN: perl -MCPAN -e shell cpan>install DBI The above succeeded, but...
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
by: Saqib Ali | last post by:
I installed mySQL and have it running.... but I think I made a mistake somewhere along the line...... I believe I did follow the instructions that were provided with the distribution at:...
by: Alex Hunsley | last post by:
I am trying to install the DBD::mysql perl module. However, it claims I need mysql.h: cpan> install DBD::mysql CPAN: Storable loaded ok Going to read /home/alex/.cpan/Metadata Database was...
by: ./Rob & | last post by:
Hi gang: I'm experiencing a problem with MySQL -- I updated MySQL from version 4.1.0 to 4.1.10 and now when I login as root it doesn't show all the databases I should have access to, nor it...
by: trihanhcie | last post by:
I m currently working on a Unix server with a fedora 3 as an os My current version of mysql is 3.23.58. I'd like to upgrade the version to 5.0.18. After downloading from MYSQL.COM the package on...
by: manish deshpande | last post by:
Hi, When i'm installing MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm by the following command: rpm -i MySQL-server-standard-5.0.24a-0.rhel3.i386.rpm the following error is being shown: ...
by: menzies | last post by:
Hi, I"m new to this forum, but I have been trying all day to install DBD::mysql onto my Intel MacBook. I've read lots of forums pages and none have gotten me to a successful 'make test' or a...
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.