Remote Mysql Recordset

[ASherbuck]

I've been reading many posts by Banana concerning remote back ends and connection strings and something that's been throwing me off is when I try to create a recordset based off my remote mysql server.

I snatched my connection string from Carl Prothman's awesome site. The code is as follows:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlstr As String
Dim MyConnectionString As String


MyConnectionString = "Driver={mySQL};" & _
           "Server=www.SERVER.org;" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=DATABASENAME;" & _
           "Uid=USERID;" & _
           "Pwd=PASSWORD"
           
Set db = OpenDatabase("", , , MyConnectionString)

When I run that I get the DSN administrator asking me to choose a DSN - Just like if I had gone through Access -> External Data -> ODBC. I can select my DSN and it will work.

When I set the DSN name in the code like such:

Set db = OpenDatabase("DSNNAME", , , MyConnectionString)

It throws an error that "C:\My Documents\DSNNAME" DSN doesn't exist.

What I would like to do is somehow circumvent the DSN Administrator in code so user's won't have to figure out how to worry about learning the few extra steps.

 

[Banana]

Three things:

1) You seem to be missing the leading prefix in your connection:

"ODBC;Driver=...

2) Is this actually what you are using?

Driver={mySQL}

I don't think I ever saw such statement. I usually use something like this:

DRIVER={MySQL ODBC 5.1 Driver}

For the 5.1 driver. 3.51 driver had a slightly different word that eludes me at the moment.

3) You may find it preferable to use the IP instead of DNS to save you on the DNS lookup and potential problems. This is particularly true when you are on a private network and not all client computers may have the same configuration.

A complete sample connection from one of my project:

ODBC;DRIVER={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Option=205866019;Stmt=;Database=MyDatabase;

HTH

 

[ASherbuck]

Thanks for the response Banana,
I've made the changes to reflect:

MyConnectionString = "ODBC;Driver={MySql ODBC 3.51 Driver};" & _
           "Server=XXX;" & _
           "Port=3306;" & _
           "Option=131072;" & _
           "Stmt=;" & _
           "Database=XXX;" & _
           "Uid=XXX;" & _
           "Pwd=XXX"

But I am still getting the same problem.

When I use:

Set db = OpenDatabase("", , , MyConnectionString)

I get the DSNSource popup in DSNSource.jpg - Like I am using the External data option in the Access 07 Ribbon

when I use:

Set db = OpenDatabase("FreeSql", , , MyConnectionString)

I get the cannot find Freesql dsn error in CannotFind.JPg. If I add an extension and make it:

Set db = OpenDatabase("FreeSql.dsn", , , MyConnectionString)

The error gives me to proper directory of and name of the DSN but still says it cannot find it.


I am using FreeSql.org to host my test project and I have managed to use this DSN to link a table in access using Access 07's -> External Data -> ODBC and I have been able to read/write the table without problem. So I know that the host is working fine.

I've triple checked the uid/pw server/database name and I know the port is all correct. I have also pinged www.freesql.org and used that IP address as my server but to no avail.

 

[Banana]

Just to be clear, have you been able to successfully create a new DSN using the same credentials and use it? I know you have freesql.dsn already, but wanted to rule out any potential corruption with that specific file.

Secondly, what happen if you hop in to mysql client and try to login to the freesql from there? Can you do that as well on the same computer?

 

[ASherbuck]

I have been able to create an identical DSN from scratch and use that the same as the original.

I have connected to the database using SIDU - opensource browser based client and it correctly displayed the only table I have in the back end. If you're interested I have no problem giving the credentials out to this since it's not for anything important and its totally free anyway.

 

[Banana]

go ahead and send it in a PM. My curiosity is piqued.

 

[Banana]

Okay.

I had long forgotten about this quirk, but here's what you actually want:

Set db = OpenDatabase("", dbDriverNoPrompt, False, MyConnectionString)

I'll be sending you back the complete connection via PM just in case. For benefits of the others, here's a generic form:

"ODBC;" & _
"DRIVER={MySQL ODBC 5.1 Driver};" & _
"Server=XXX;" & _
"Port=3306;" & _
"Option=XXX;" & _ 
"Stmt=;" & _
"Database=XXX;" & _
"Uid=XXX;" & _
"Pwd=XXX"

Next, there is also issue: The MySQL runs on a Linux server. Normally this shouldn't be significant, but there is a difference in case sensitivity of the filesystem.

From mysql client:

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydatabase.mytable' doesn't exist
mysql> select * from MyTables;
<output the data>
9 rows in set (0.01 sec)

mysql>

The variables associated with the case sensitivity are:

mysql> show variables like '%lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   | 
| lower_case_table_names | 0     | 
+------------------------+-------+
2 rows in set (0.09 sec)

mysql>

More information. I'd want to see if I can set the variable to 1 or 2 to avoid any potential issues whenever we don't have the right case.

I think that about covers it. See if this helps you get going.

 

[ASherbuck]

That nailed it. Thanks so much for your support with this.

 

[Banana]

I'm glad we worked something out.

Best of luck.