CODEWITHSUNDEEP
sql-serverms-access
PolMac
PolMac

Reputation: 1

Error using SSMA to migrate Data from Access to SQL Server

I am trying to use the SSMA to migrate two Databases from Access 2007 to SQL Server 2014. The SSMA recognizes the Access DB's but the tables and queries show as zero. I have ammended the permissions to allow admin control and these are copies of the original DB's but the tables don't show up. Is there something I could be missing?

Upvotes: 0

Views: 2017

Answers (2)

Armando Costa
Armando Costa

Reputation: 21

I had the same problem, and after some hours looking on this it seems that one property "SSMATableState" on the tables hides them in SSMA. Probably SSMA added that after being used, I guess...

In Access add a module and this code, run it to remove that:

Sub tabsSSMAfix()
    Dim t As TableDef, a, p
    Dim db As Database
    Set db = CurrentDb
    For Each t In db.TableDefs
        If (t.Attributes And dbSystemObject)=0 Then
            Debug.Print t.Attributes 
            Debug.Print t.SourceTableName
            
            For Each p In t.Properties
                Debug.Print t.Name; " "; p.Name; "="; p
                If p.Name = "SSMATableState" Then
                    t.Properties.Delete "SSMATableState"
                End If
            Next p
            Debug.Print
        End If
    Next t
End Sub

Upvotes: 2

benjamin moskovits
benjamin moskovits

Reputation: 5458

Had the same problem recently. Make sure you are using the connection called: Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider.

You know you are using the right provider when you can preview the data.

Upvotes: 0

Related Questions