Using ADODB to connect to remote MySQL DB

Dev Shed Forums Sponsor:

October 1st, 2012, 12:46 PM

Nullified

Contributing User

Join Date: Dec 2003

Posts: 159

Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30

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?

__________________
Option9 Hosting LLC. - Quality Web Hosting Services
Personal, Business & Mature Content Shared Web Hosting

October 1st, 2012, 11:36 PM

couttsj

Contributing User

Join Date: Aug 2011

Posts: 165

Time spent in forums: 2 Days 16 h 44 m 37 sec
Reputation Power: 43

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

October 2nd, 2012, 11:54 PM

Doug G

Grumpier Old Moderator

Dev Shed God 19th Plane (14000 - 14499 posts)

Join Date: Jun 2003

Posts: 14,237

Time spent in forums: 1 Month 4 Weeks 14 h 47 m 12 sec
Reputation Power: 4445

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
======
It is a truism of American politics that no man who can win an election deserves to. --Trevanian, from the novel Shibumi

October 4th, 2012, 11:18 AM

Nullified

Contributing User

Join Date: Dec 2003

Posts: 159

Time spent in forums: 19 h 23 m 37 sec
Reputation Power: 30

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.

Using ADODB to connect to remote MySQL DB

Developer Shed Advertisers and Affiliates