MYSQL connection error

[slaphead109]

I am trying to remotely connect to a MySQL database version 4.1.14 via VB6.

The connection string I am using is :-

CONN_STR = "Driver=SQL Server;Server=" & SERVER_IP & "," & ";Database=" & DataBase & ";Uid=" & UID & ";Pwd=" & PWD & ";"

However, I believe I am not declaring the driver correctly. I have used "SQL Server" which was used in the example from www.connectionstrings.com.

I have downloaded the ODBC 3.51 driver and it has auto installed somewhere (no idea where).

How do I use it in my code?
Do I need to add a reference to it in my project (It doesn't seem to be in the list)?

Has anyone any experience of connecting to a remote MySQL database please?

[Hack]

What error are you getting?

[slaphead109]

After some messing with the string, I now use "Driver={MySQL ODBC 3.51 Driver} etc I get the error [MySQL][oDBC 3.51 Driver]Access denied for user 'username'@'%' to database 'DBName'

which sounds as if I am reaching the remote server, but maybe the driver is incorrect?
I have a reference in my project to Microsoft ActiveX Data Objects 2.8 library, and have service pack 5.

[slaphead109]

If it helps any, the @'%' after 'username' aren't part of my username. The error is reported as a SQL_ERROR when I test the connection using the MYODBCConfig utility, and is worded the same as the error sent back when I try to connect via VB6.

Maybe the 3.51 driver is a problem with the MYSQL version?

[si_the_geek]

You should be using the MySQL driver (it is very different to the MS SQL driver), but I dont know what version you need.. the documentation for the driver (or the MySQL website) should provide the answers.

The error you are getting does mean that you are communicating with the server.. the problem is with the security, either what is set up on the server, or what you are specifying in the connection string.

Is your connection string in the following format (with underlined parts replaced as apt)?

Driver={MySQL ODBC 3.51 Driver};Server=data.domain.com;Port=3306;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;

Does the user/password specified in the connection string match what has been set up within the database itself?

[slaphead109]

Thanks Si, your connection string was spot on.

What I was doing was entering the table name and not the database name, so I was getting to the server but obviously getting an error message.

It's always the little things cause the biggest headaches, cost me the best part of a day.

Thanks for your help, one and all