#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15

    Connecting to database using ADODB


    I am trying to connect to MYSQL database so that part property data can be fetched, however I am running into this problem...any ideas what I am doing wrong?

    Code:
    Set oConnection = CreateObject("ADODB.Connection")
    Set oRecordSet = CreateObject("ADODB.Recordset")
    oConnection.Open = "Driver={MySQL ODBC 3.51 Driver};" & _
         "Server=qcae-pcb;Database=qc_lib_exp_rlslib;User=qc_dx;Password=abc123;"
    strSQLQuery = "SELECT * FROM resistor WHERE PartNumber = '123456'"
     oRecordSet.Open strSQLQuery, oConnection, 3, 3
    PROBLEM:
    Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    I have the datasource identified, and driver, username, password are all correct.
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Usually you need to set up the ODBC specifically for different data environments.

    For example, Oracle databases are often defined in a tnsnames.ora file. There's also different connection strings for Oracle, DB2, etc. You might need to consult a DBA for more specific information on accessing your db with ODBC.

    ie to connect to Oracle I use

    Code:
    cCon.Open "Driver={Microsoft ODBC for Oracle};Server=xxxx;Uid=xxxx;Pwd=xxxx;"
    Wherease DB2 I need

    Code:
    con0.Open "Provider=IBMDADB2;DSN=xxxx;User ID=xxxx;Password=xxxx;"
    In both cases there were additional things I needed to do to have ODBC see my database servers. These instructions came from our DBAs.

    Unfortunately I don't use MySQL so I can't really give you much there, but a MySQL forum might be able to help you out.

    You might first want to check to make sure that you have the MySQL ODBC driver installed.
    Last edited by medialint; May 25th, 2012 at 02:06 PM.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Thanks for the reply; I got an answer at a different forum which fixed the problem. Since I am on Windows 7, I needed to go to use the correct version of CScript.exe or WScript.exe on Windows 7 by going to C:\windows\syswow64\cscript.exe <name_of_vbs.vbs>

    That fixed it.
  6. #4
  7. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,441
    Rep Power
    4539
    Probably a dumb question, but did you download and install the mysql ODBC driver on your computer? And if the mysql server is on a different computer is port 3306 open in the db server firewall?
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    Since you have a syswow64 directory, I have to assume that you have a 64 bit operating system. Microsoft does not provide 64 bit drivers for many of the older database systems, meaning that they have to run using 32 bit drivers. By default, the 64 bit system utilizes %SystemRoot%\system32\odbcad32.exe to manage ODBC, which will not show any of the older drivers. To get access to those, you will need to use %SystemRoot%\syswow64\odbcad32.exe.

    J.A. Coutts
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Originally Posted by Doug G
    Probably a dumb question, but did you download and install the mysql ODBC driver on your computer? And if the mysql server is on a different computer is port 3306 open in the db server firewall?
    By downloading the .msi from mysql website. No, the driver is installed local to my pc.
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2009
    Posts
    65
    Rep Power
    15
    Originally Posted by couttsj
    Since you have a syswow64 directory, I have to assume that you have a 64 bit operating system. Microsoft does not provide 64 bit drivers for many of the older database systems, meaning that they have to run using 32 bit drivers. By default, the 64 bit system utilizes %SystemRoot%\system32\odbcad32.exe to manage ODBC, which will not show any of the older drivers. To get access to those, you will need to use %SystemRoot%\syswow64\odbcad32.exe.

    J.A. Coutts
    I realize this and the driver(s) were showing when I clicked in odbcad32.exe in syswow64 folder. The error message was misleading and went away when I used the correct WScript/CScript .exe.

IMN logo majestic logo threadwatch logo seochat tools logo