Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005

    Unanswered: Connecting MS SQL and MySQL

    We have a legacy database running on MS SQL Server 2000 with an Access 2003 project (ADP) as a front end. We have a remote MySQL server which we download data from into the MS SQL database.

    At the moment we have a standalone Access MDB file which uses a DSNLess connection to the MySQL server (so it attaches the MySQL tables, runs the queries and then detaches the table so we're not keeping a connection open) and linked tables to the MS SQL server so I can run append queries to download data from a MySQL table to a MS SQL table.

    It's automated to do this every 30 minutes at the moment but I'd like to be able to do it instantly at a user's request. At first I thought I'd just incorporate the DNSLess connection code into the front end ADP project but it looks like I can't attach tables from the MySQL server in an ADP project becuase unlike in an MDB file as all the ADP project's tables are in the SQL database.

    I'm guessing I might be able to use linked tables on the SQL server and link the MySQL server there but I'm wondering if this would mean the connection to the remote server would be permanently open?

    Does anyone have any advice on the best way to link a remote MySQL server to MS SQL 2000 and be able to do on-demand transfers from MySQL to SQL 2000? Maybe a DTS package triggered by a user action?


  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    I would setup a linked server from your SQL Server to your MySQL database, and then I would have a stored procedure in your SQL Server database that copies the data over the linked server into SQL Server and I would have your user interface execute that SQL Server.

    I do not want to get to involved with what you are doing but you might have issues if you have too many users doing this kind of thing at the same time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Oct 2005
    Thanks Thrasymachus,

    Yes, I'd need to test that as I could have around 15 users and although each one would be downloading different records it might cause problems.

Posting Permissions

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