Connecting to MySQL

Posted on 2006-05-25
Last Modified: 2010-04-07
Please help me, I'm new to MySQL. I downloaded "MySQL 5.0.19" and the "mysql-connector-odbc-5.0.0-alpha-win32" - ODBC driver.


MySQL username - root
MySQL Password - 4dm1n

I created a DB with "MySQL Administrator"
DB Name - test
Table - name

In VB6

Form with one button, and the following code:

Private Sub Command1_Click()
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    cnn.Open "server=DICK-NB;db=test;driver=MySQL ODBC 3.51 Driver;uid=root;pwd=4dm1n"
    Set rst = cnn.Execute("SELECT Count(*) As cnt FROM")
    MsgBox rst("cnt")
    Set rst = Nothing
    Set cnn = Nothing
End Sub

I get the following Error:
Run-time error '-2147467259 (80004005)'
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Please help me.

Question by:DickSwart6
    LVL 13

    Expert Comment

    by:John Mc Hale
    Hate to ask the obvious, but have you set up a DSN for server DICK-NB and used?

     cnn.Open "dsn=MyDSNNAME;db=test;driver=MySQL ODBC 3.51 Driver;uid=root;pwd=4dm1n"
    LVL 1

    Expert Comment

    Hi DickSwart6,
     I aint really got a comment that could help you mate other than you mayby shouldnt post your root password on the forum, any forum for that matter.

    i found using WAMP(windows-apache-mysql-php) a handy tool - easy to set up and build databases with.

    You can get it as an all in one installer - google it its freeware

    good luck

    Author Comment

    fredthered, is it nesasery to create a DNS to connect, can't I just use ADO?
    LVL 13

    Expert Comment

    by:John Mc Hale
    It all depends on whether or not ADO can resolve the 'Server' part of your original syntax. Why not give the DSN method a blast and see if this makes any difference. If it does, then chances are that ADO cannot resolve the name DICK-NB to mean the physical MySQL server.

    Author Comment

    Please don't laugh, I havn't tried DNS because I don't know how. Can you help me.
    LVL 13

    Expert Comment

    by:John Mc Hale
    DSN = Data Source Name

    If you're using a Windows variant, a DSN is a data source that you create using the ODBC Data Sources tool in the Control Panel (In Windows XP, this is located in the Administration Tools category).

    1. Click the Start Button
    2. Goto Settings
    3. Click on Control Panel
    4. Locate the Data Sources (ODBC) applet
    5. Start applet and select either User DSN or System DSN tab (User DSN only available to currently logged on user, System DSN for any user using the computer)
    6. Click the Add... button, which brings up the Create Data Source dialog.
    7. Select your MySQL driver and configure it for your system. The Data Source Name is what is referred to as the DSN!

    Hope this helps.

    Author Comment

    If I create DNS and install my app on other PC on the network is there anythin I should do on there PC's to be able to see the DB?
    LVL 13

    Expert Comment

    by:John Mc Hale
    Yes create a DSN on that computer as well, configured to 'see' your database(server). System DSN's are probably  best in a networked environment as they allow any user who can log on to the computer, to access the data source.
    LVL 2

    Accepted Solution


    You shouldn't need to set up a DSN.  I think the problem is you are trying to use the 3.51 version of the ODBC driver in your connection string, but you have installed the 5.0.0 version.  I would recommend that you install the current production version of the driver (3.51.12).  I tested your connection string with that driver and it works.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now