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

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31

    Using ADODB to connect to remote MySQL DB


    This is my first time doing this so I am unfamiliar. I have added reference Microsoft ActiveX Data Objects 6.1 and even tried using the previous library verisions
    Code:
        Private Sub homeFRM_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim DBconnect As New ADODB.Connection
            Dim DBcommand As New ADODB.Command
            Dim DBrecordset As New ADODB.Recordset
            DBconnect.Open("DRIVER={MySQL ODBC 3.51 Driver};SERVER=ipaddress;DATABASE=dbname;UID=dbuser;PASSWORD=dbpassword")
            DBcommand.CommandText = "SELECT id FROM tbltickets WHERE status = Open"
            DBrecordset.Open(DBcommand.CommandText, DBconnect)
            DBrecordset.Close()
            DBconnect.Close()
        End Sub
    If I put a message box above DBconnect.Open it pops up just fine. If I put it below it never pops up, but the form does and during debug now output errors show. I would like to check the status of the connection but it appears the connection is not working. Is my syntax correct? How do I reference a port number? IP:PORT as the server?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Posts
    289
    Rep Power
    45
    When using ODBC, it is not necessary to define the database or the driver, as that is already defined in the DSN.

    DBconnect.Open("DSN=dsnName;UID=dbuser;PASSWORD=dbpassword")

    J.A. Coutts
  4. #3
  5. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,439
    Rep Power
    4539
    couttsj, the op is using a "dsn-less" connection string.

    To make mysql work with ADO, you need to have the mysql drivers installed on the computer that's executing the script. Review the connection string and verify you're using the correct identifier for your version of the mysql odbc dricers.

    The mysql port must be open to the computer that has the mysql db on it, so check all firewalls / routers / port forwards / etc between the two machines.

    The mysql database permissions must allow your host to connect with the db user and password you're using.

    Once all this stuff is in place your connection should work. You still may need to tweak some of the mysql specific options in the connection string, refer to the mysql odbc driver documentation for specifics.

    Comments on this post

    • medialint agrees
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    159
    Rep Power
    31
    Sorry that I forgot to update, but I switched my application to c# and it's so much easier to do what I wanted to do.

IMN logo majestic logo threadwatch logo seochat tools logo