Question regarding SQL Server Migration Assistant for Access

Question

I have an Access 2000 format database that is split into two files. JudgementTracking.mdb has all the forms and reports and JudgementTracking_be.mdb that has all the tables. I at first used the assistant to upgrade the backend Access database. Now when I open the frontend it says the tables no longer exist in the backend access database project. I go to => Database Tools => Linked Table Manager, thinking all I have to do is change the references of the linked tables to SQL Server? However when you prompt for a new location. I tried setting it to a System DSN in C:\Program Files\Common Files\ODBC\Data Sources . I browse to their and check "Always prompt for new location". When I try to open that dsn it says "Unrecognized database format...path to dsn" This seems like a relatively straight foward operation. I'm thinking it should be simple. I must be missing something...?

Answer 1

I think what may have happened is that when SSMA upsized the db, you selected the option to have Access be upgraded as well and attached to the new SQL db, but because this was the BE, that doesn't work so well.

At this time I would delete the table attachments from the FE, then link to the SQL Server tables using the ODBC linked table features in Access.

Are you still at A2000? That's now 2002 - 2003 - 2007 - 2010 - 2013: 5 versions and 13 years behind the times. It may be time to cash in on your luck and consider an upgrade. Note that the Access Runtime is now free - has been for several versions, so only developers need to spend money on the new version.

---

-Tom. Microsoft Access MVP

Answer 2

I think you need to delete all existing Linked Tables in the FE (since the "source" for them has been set as Access/JET database) and re-create the Linked Tables using the Menu Command "Get External Data..."

See the Microsoft Office article Import or link Access to SQL Server data for the steps.

 

Alternatively, you can use DSN-Less Linked Tables as per the following articles:

* Microsoft KB How to create a DSN-less connection to SQL Server for linked tables in Access

* Doug Steele's Using DSN-Less Connections

---

Van Dinh