'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Access Tables Not Linked After SQL Server Migration
Question details
After migrating an Access database to Azure SQL using SQL Server Migration Assistant (SSMA), some tables fail to link and contain no data, despite the tool reporting no errors.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Migrating a legacy local Access database to Azure SQL using the SQL Server Migration Assistant.
- Observed behavior
- SSMA completes without errors, but certain tables are not linked in Access and the corresponding tables in Azure SQL are empty.
Ensure you create a complete backup copy of your local Microsoft Access database before modifying table structures, deleting fields, or attempting the SQL migration again.
Verify Primary Keys and Modify Table Structures
Ensure all Access tables meet Azure SQL migration requirements by assigning primary keys and removing incompatible data types.
SQL Server Migration Assistant (SSMA) requires each table to have a properly defined primary key to successfully link them. Furthermore, legacy fields like timestamps may cause the data migration to fail silently, resulting in empty tables on Azure SQL.
Open your local Access database, right-click the table that failed to link, and select 'Design View'.
Ensure that a unique identifier field exists. Right-click the appropriate field and select 'Primary Key' to assign it.
Look for legacy timestamp fields or unsupported data types and remove or convert them before saving the table structure.
Open SQL Server Migration Assistant, reconnect to your database, and perform the data migration and linking process again.
Manually Link Tables Using Linked Table Manager
Use the built-in Linked Table Manager in Access to manually establish connections to successfully migrated tables in Azure SQL.
A Free, Lightweight Office Alternative for Your Daily Work
While resolving complex Microsoft Access database and SQL Server migration issues requires specific Microsoft tools, you can handle all your everyday document, spreadsheet, and presentation tasks using WPS Office. It offers a seamless transition from Microsoft 365 with excellent format compatibility and a familiar interface, all for free.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the on-screen instructions to set up WPS Office on your device.
- 3. Open Your Office Files: Launch WPS Office and open your existing Word, Excel, or PowerPoint files directly without any conversion.
Frequently Asked Questions
Why does SSMA show no errors but leave Azure SQL tables empty?
SSMA may successfully create the table schema in Azure SQL without throwing errors. However, if the table lacks a primary key or contains unsupported data types like legacy timestamps, the actual data migration and automatic linking process will fail silently.
How do I check if my Access table has a primary key?
Open your table in Design View in Microsoft Access. Look for a small key icon next to the field name on the left side. If no key icon is visible, the table does not have a defined primary key.
Can I link tables to Azure SQL without using SSMA?
Yes. If the tables and data already exist in Azure SQL Server, you can use the Linked Table Manager under the External Data tab in Access to manually establish an ODBC connection to the SQL database.
