Linking To Tables in MySQL (2000+)

Topic

New Reply

I have a requirement to link directly to a table in MySQL Remote database.
I have acquired a MySQL ODBC Driver for Windows ODBC-3.51.11-1
The MySQL Database can only be accessed via an SSH connection and I can do that and talk to the required tables in a command window.
The procedure against the table is a simple append.

However, I ideally want to be able to link to and see this table from inside access.

Does anyone have any ideas how this can be done
or
If it can be done?

Thanks

Reply | Quote

Replies

Have you tried linking a table in the MySQL database in Access? (File | Get External Data | Link Tables…, then select “ODBC databases” in the “Files of Type” dropdown list, activate the Machine Data Sources tab; if you see a MySQL data source, select that, otherwise click New…)

Reply | Quote

Yes.
I am having trouble defining the ODBC DSN.
I have to first run a command script to open the connection with a local secure key.
This works fine.
However, I cannot seem to get the connection to work from the ODBC setup.

I am contacting the folks on the other end of the wire
to see if they can assist with this.

I’ll keep the world informed.

Andrew

Reply | Quote

> linking a table in the MySQL database in Access?

I tried this, too via Access. I can see the entry for my data source, but it too generates a “failed” status when i try to login/connect.

I know the figures, user & password are correct, because I can consistently get in using those values via the web. I suspect that the /mysql and the port number are lodged in the wrong place.

Reply | Quote

Edited by HansV to break extremely long lines.

> Does anyone have any ideas how this can be done
Possibly. But I don’t have the whole idea (yet)
By an amazing coincidence I d/l the MyODBC-3.51.11-1-win.exe this morning and have been struggling to get connected ever since.

The .exe appeared to install itself correctly. I can see it from Control Panel, data Sources (ODBC).

From various web sites, amongst them http://dev.mysql.com/doc/mysql/en/without-dsn.html%5B/url%5D I have cobbled together this code, which returns me an error “Unknown MySQL server host” after a delay of about three seconds. The delay leads me to believe that messages have travelled across the web.

Sub test()
    Dim strCnn As String
    'http://dev.mysql.com/doc/mysql/en/without-dsn.html
    '"Driver={MySQL ODBC 3.51 Driver}; Server=yourserver; Port=3306; Option=3; " & _
        "Socket=; Stmt=; Database=yourdatabase; Uid=youruser; Pwd=yourpassword;"
    strCnn = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=http://homeoscar.homeip.net; " & _
        "Port=12000; DATABASE=oscar_mcmaster; "
    Call OpenIt(strCnn)
End Sub
Public Function OpenIt(strCnn As String)
    Dim cNn As ADODB.Connection
    Set cNn = New ADODB.Connection
    cNn.Open (strCnn)
End Function

Just to complicate matters, when i access the database through my browser, the address line shows up as http://homeoscar.homeip.net:12000/mysql/ed…=oscar_mcmaster, and I’m not sure how to splice the /mysql with the port number.

If you can get soemthing simpler working along these lines, I’d be grateful to hear about it.

Reply | Quote

Why do you have those spaces in your connect string and provider string after the semicolons, Chris? I can’t say they don’t work because I can’t remember having tried them, but they aren’t what I normally expect to see.

Reply | Quote

Pasted directly from sample code on the web; and removing them dosn’t make any difference, either, but thanks anyway.

The current theory is “that the MySQL server is not setup for direct remote connections. If you are browsing a site that’s connected to the DB on the same host then the networking is a non-issue as it’s all running locally yet you can browse it remotely. Being able to see the data via the web is no indication that the DB is setup for direct remote connections.“

Reply | Quote

(Edited by charlotte on 21-Mar-05 14:54. to activate link)

I have got mine working just fine.
I got a copy of the PLINK Utility which lets you point at a connection and use localhost as the server to use
You need to set up a DSN pointing to localhost. I used port 3306 with mine but I guess anyone will do

Have a look at this link

http://www.vbmysql.com/articles/ssh-tunnel-part2.html#part6%5B/url%5D

It told me pretty much everything I wanted to know about getting the link ready

Then I set up a DSN using the ODBC Driver pointing to the required database

The one I link to is in Europe and it connects almost as fast as if it was on a local server.

There is a pause required between opening the link and then using any tables data so I initiate the link on mine
when the application starts.

You can also download a sample vb6 app from here.
Obviously you need to change the PLINK parameters but mine works just Dandy now

Reply | Quote

Andrew, thanks for the response. I’m not sure that I’m connecting by an SSH, whatever that is, but I understand that one of the necessary steps is to set up a user/system/file DNS in the ODBC tables in Control panel. Is that correct?

The MySQL has a TEST button, and this test shows that I’m not connecting at all. I suspect it’s my confusion with the port, and the “/mysql” at the end. I’ve tried various combinations, to no avail. Please can you spot an error, or suggest an alternative way of generating the ODBC link?

Reply | Quote