Table migration to sql server

[Derek]

Hi Guys

I am migrating tables from Access database to sql server using upsizing wizard in Access. I have done the following steps:

1. exported all the tables from MS Access Backend file to sql server. When this is done then it automatically adds suffix 'Local' to all the existing tables names in Access. Do I need to delete those tables now from MS Access BE?

2. Another thing is I need to change the exiting table links in Access frontend to sql server tables. How can i do that?

3. Can I directly export/link tables from Access FE to sql server ? Access FE has linked tables to Access BE at the moment?

Many Thanks

 

[arnelgp]

You need to delete the old linked table.
You link the table on sql using odbc connection.

 

[Derek]

You need to delete the old linked table.
You link the table on sql using odbc connection.

Yes i deleted all the old links in the Access frontend and tried ODBC connection from External data -> ODBC database but the Link tables windows displays all the 'system tables' or 'Information_schema' tables of sql server . Not sure why its not showing all the tables that I have migrated in the window.

Can you pls help me in this?

 

[arnelgp]

Take a quick tour

https://support.office.com/en-us/ar...database-a5a3b4eb-57b9-45a0-b732-77bc6089b84e

 

[isladogs]

For info, another method of linking tables (Access or SQL) is to use DNS-less connection strings rather than ODBC connections.
See this link for info https://www.connectionstrings.com/access/

Both methods work equally well. Its just a matter of personal preference

 

[Pat Hartman]

When you create the DSN, choose your database name rather than "Master" as the default database.

When you link the tables, they will be prefixed by dao_. You will need to remove the prefix or all your queries will break. If you had done the upsizing before splitting, the wizard would have also relinked the server tables without the prefix. Now you need to do it manually.

 

[kevlray]

I like using Microsoft's SQL Server Migration Assistant (SSMA) for Access. We do not have to create an ODBC for each workstation. I am not an expert with the tool. I do not use it often enough and it might be good for me to read the documentation.