First Published 8 July 2024

SQL Server Migration Assistant (SSMA) is a powerful wizard-based tool that helps manage the import of tables and (optionally) queries from Access into SQL Server.

SSMA is not intalled as part of SQL Server but is available as a separate download from Microsoft SQL Server Migration Assistant for Access


It can also (optionally) be used to link those tables back ino the source database, replacing the original Access tables.


SSMA will also inform you of any issues with your tables e.g. complex datatypes such as multivalued or attachment fields.
The process is normally straightforward and problem free.

This article outlines an obscure problem raised by member ksachen at Access World Forums: SSMA fails to import tables

As the AWF member stated: "I can't import my tables in SSMA. I have 15 Tables in my Access database, but only one table appears in SSMA"


The AWF member had unsuccessfully looked at datatypes, relationships and indexes. He/she had also tried removing the table data.
None of those actions helped. The remaining tables all remained invisible in SSMA

There was no useful information to explain this problem online.
My purpose in writing this article is to provide a solution for anyone else experiencing this issue in the future.

It took a collaborative effort by former MVPs, George Hepworth and Philipp Stiefel, as well as myself to first identify the problem, then find both a workaround and a solution.

On looking at the user database, we were able to confirm that there were no issues with either the structure or data of the tables that SSMA was not detecting.

Making a copy of the tables, with or without data, did not help. Nor did exporting the tables to a new database

However, creating a new copy of the tables using make table queries DID work.
The new tables were now visible in SSMA and could be successfully imported into SQL Server.

The cause turned out to be a table property called SSMATableState with value: back up copy.
This property had been added to the tables in a previous migration using SSMA and was preventing SSMA from showing and migrating the tables again.
The property was identified by running the built-in Access Database Documenter tool and examining the table properties.
Removing the property made those tables visible again in SSMA


The following code can be used to remove the SSMATableState property from all tables:

CODE:

Sub RemoveSSMATableState()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim prop As DAO.Property

Set db = CurrentDb

      For Each tdf In db.TableDefs
            If (tdf.Attributes And dbSystemObject) = 0 Then
                  For Each prop In tdf.Properties
                        If prop.Name = "SSMATableState" Then
                              tdf.Properties.Delete "SSMATableState"
                              Exit For
                        End If
                  Next prop
            End If
      Next tdf

End Sub

Although this works, the mystery remained as to why this property existed in these tables.
The screenshot below shows what you would expect to see using SSMA for a specific Access database


Exporting tables to SQL Server using SSMA does not automatically create the property in the Access tables.


Further investigation showed that the SSMATableState property had been created in a previous migration using SSMA.

SSMA offers to link those tables back to the source database and, if that is done, it then makes a local backup copy of each table.


The linked tables have the same names as the original Access table and each have an SSMATableState property with value: linked and backed up


The local tables are renamed with a prefix SSMA$ and suffix $local. These get the SSMATableState property with value: back up copy


This property with value back up copy prevents the table being re-exported using SSMA.
If the wizard is run again with all the above tables, only the linked tables are visible


This is done to prevent old local table data overwriting newer linked table data on the server.

So it is likely that at some time in the past, someone had done the following:
1.   Exported using SSMA
2.   Accepted the option to link back
3.   Deleted the linked tables (or exported them to a new database)
4.   Renamed the local tables removing the SSMA$ prefix
5.   Forgot all about steps 1-4 and then tried to use SSMA to export the tables again at a later time

NOTE:
If the linked tables are converted back to local tables, they retain the SSMATableState property which (perhaps confusingly) still has the value: linked and backed up.
If you now try to export the tables using SSMA, the local back up copies starting with SSMA$ remain invisible in SSMA but the previously linked tables that are now local are available for export again!

Properties are transferred from the source table to the destination table when tables are copied or imported into a new database.
However tables created using a make table query do not retain the properties of the original table(s)



Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you



Colin Riddington           Mendip Data Systems                 Last Updated 8 July 2024

Return to Access Blog Page

Return to Top