Post reply

legacy ODBC access access through sql server 2005

  • July 7, 2006 at 11:08 am

    #648044

    I have been able to created linked servers in SQL2005 but it is a roundabout way.  Follow these steps:

    1. Create an ODBC Connection to MAS200 using the ProvideX ODBC Driver. You will need to create a new connection for each Company in MAS
    2. Create an Access 2003 Database (Yes I know I said Access 2003, I told you that it was a roundabout way of doing this)
    3. In the database window of your Access DB, right click and select Link Tables. 
    4. Point the Look In to ODBC Connection
    5. Select the ODBC Connection created in step 1
    6. Select all of the tables you want linked and check the Save Password option. 
    7. Click the link tables button (May be the ok button)
    8. Go to SQL Server Management Studio and connect to the server you wish to link the MAS tables to
    9. Expand Server Objects
    10. Right Click on Linked Servers
    11. Select New Linked Server...
    12. Name the linked server
    13. Provider should be Microsoft Jet 4.0 OLE DB Provider
    14. Data source should be pointed to the access database created in step 2
    15. On the security tab, you need to select the option Be made using the login's current security context
    16. Click OK

    You can now run queries against the linked MAS200 tables like this:

    Select * From OpenQuery(LinkedServerName, 'Select * From AR1_CustomerMaster')

    I tried with SQL2000 linking directly to MAS but I would get intermittent "Catastrophic Failures".  This way you still end up with a few but they are very infrequently.  If you tend to get them or it your query is not running as fast as you expect, you can stop and restart SQL Services and they usually go away.

    I hope that this helps...Scott

Viewing post 1 (of 2 total)

You must be logged in to reply to this topic. Login to reply