Results 1 to 9 of 9
  • Thread Tools
  1. 5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,012
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Linking To Tables in MySQL (2000+)

    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
    Andrew

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: Linking To Tables in MySQL (2000+)

    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...)

  4. 5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,012
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking To Tables in MySQL (2000+)

    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
    Andrew

  5. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking To Tables in MySQL (2000+)

    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 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.
    <pre>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
    </pre>


    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.

  6. Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking To Tables in MySQL (2000+)

    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.
    Charlotte

  7. 5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,012
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking To Tables in MySQL (2000+)

    <P ID="edit" class=small>(Edited by charlotte on 21-Mar-05 14:54. to activate link)</P>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-...rt2.html#part6

    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
    Andrew

  8. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking To Tables in MySQL (2000+)

    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?

  9. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking To Tables in MySQL (2000+)

    > 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.

  10. Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking To Tables in MySQL (2000+)

    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."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •