Connecting to MySQL from ADO in VB

[markmercer]

I am trying to connect to an online MySQL database from VB6 using ADO
and I'm getting the following error. "data source name not found and no default driver specified"

here's the code I'm using to try to connect.

        Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    connectionstring = "Driver={MySQL ODBC 5.1 Driver};Data Source=URL,3306;Database=DBNAME;User=USERID; Password=PASS;Option=3;"
    conn.Open connectionstring
    rs.Open "SELECT * FROM card_prices", conn

no doubt its simply my inexperience with this type of connection. I have plenty of experience with MySQL but none with these connection string jiggys.

P.S. I've tried downloading the MySQL ODBC 5.1 Driver and installed it but no effect. Do I have to install the version matching my system or the one matching the server?

[techgnome]

http://www.connectionstrings.com/mysql

It's uid and pwd.... not User and Password....

-tg

[markmercer]

that's irrelevant. I've tried it both ways, same error.

http://www.connectionstrings.com/mysql#p31

the error i'm getting is not to do with those fields.

[MarkT]

P.S. I've tried downloading the MySQL ODBC 5.1 Driver and installed it but no effect. Do I have to install the version matching my system or the one matching the server?

The driver on your machine must match what you are using in your connection string.

[markmercer]

ok thats the one I have installed

[DataMiser]

that's irrelevant. I've tried it both ways, same error.

http://www.connectionstrings.com/mysql#p31

the error i'm getting is not to do with those fields.

If you look at the strings at that link none of them match what you have

you have Data Source= where they all use Server= instead

[markmercer]

If you look at the strings at that link none of them match what you have

you have Data Source= where they all use Server= instead

well your right.
I HAD used examples directly off that site but that was before I installed the driver. now I have tried this one:

Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDataBase;User=myUsername; Password=myPassword;Option=3;

and I get a different error (to me that means I'm making progress).
the error is "Can't connect to MySQL server on myServerAddress"

I wonder if I have to submit an address other than the root domain. Have any of you guys ever done this with a domain managed by Cpanel?

[techgnome]

So you're trying to access a mySQL database that is on a web server somewhere? That presents a whole new set of problems. You';ll need to check with the host... a lot of them (if they are any good) only make their databases available from within their domain... and not directly accessable to the outside world. If that's the case, then you'll need create something that acts as a web service and can be put onto the webserver, take requests, and return data.

-tg

[markmercer]

I am a web developer, and I've been using this host for many years now. I know that the server will allow incoming connections because in the past I've opened a connection to it from another site. But I wouldn't be surprised if it was still some configuration thing that's interfering.

I've been talking to the host (as i know him personally) but its hard to find answers when you don't know what questions to ask. I'll contact him again and see what else we can try.

Perhapse from here on out the answer lies between me and his configuration.
Still if there's any more advice you guys can offer, please post.
Thanks.