SSMA fails to import tables (1 Viewer)

[ksachen]

I cant import my tables in SSMA. I have 15 Tables in my access database, but only one table appers

Like you see in this screenshot

http://imgur.com/VZsD3RC

I checked the informations from these thread, also the youtube video(clicked through).
access-programmers.co.uk/forums/threads/sql-server-migration-assistent-for-access-not-all-table-from-access.330695

The SSMA Part didnt helped, cause there where no problems like that. The VBA Method would be maybe an option, seems more complicated (where can i download, the link unter youtubevideo leed to no specific file)

I read before the introduction of microsoft for the migration process.
The database is running without any problems in daily use. Wanna get it on azure SQL Server, cause of availability from "outsite" and use it with feature from azure (combination of OCR and Power Plattform)

What is functioning:
- SSMA is functioning, its related to my database, cause

• Northwind Test DB is fully avaiable in SSMA (Tables and queries)
• also the queries of my database are avaiable

- Connect to Azure SQL Server

What i tried (without sucess):
- new accdb's with copied in tables:

• "played" around with datatypes
• only one table
• delete any relationship
• removed index'
• each above without any datas

- Different version of access, also different PCs with SSMA
- Instant of SSMA, i tried Upsizing Wizard (crys about different SQL Version)

Any Ideas what and where i can check, whats the problem ist. I could imagine its a problem of access rights, or something like that is blocking SSMA.

 

[theDBguy]

Hi. Welcome to AWF!

Are you just trying to experiment with SSMA or actually trying to upscale an Access table into SQL Azure? Just curious...

 

[ksachen]

Hi. Welcome to AWF!

Are you just trying to experiment with SSMA or actually trying to upscale an Access table into SQL Azure? Just curious...

Both
I try to get the tables to azure, but first for experimenting with azure function in connection with database in SQL.

Mid term goal is the full migration to be independent from localserver, but it has no urgency in the next month for that.

 

[GPGeorge]

I cant import my tables in SSMA. I have 15 Tables in my access database, but only one table appers

Like you see in this screenshot

http://imgur.com/VZsD3RC

I checked the informations from these thread, also the youtube video(clicked through).
access-programmers.co.uk/forums/threads/sql-server-migration-assistent-for-access-not-all-table-from-access.330695

The SSMA Part didnt helped, cause there where no problems like that. The VBA Method would be maybe an option, seems more complicated (where can i download, the link unter youtubevideo leed to no specific file)

I read before the introduction of microsoft for the migration process.
The database is running without any problems in daily use. Wanna get it on azure SQL Server, cause of availability from "outsite" and use it with feature from azure (combination of OCR and Power Plattform)

What is functioning:
- SSMA is functioning, its related to my database, cause

• Northwind Test DB is fully avaiable in SSMA (Tables and queries)
• also the queries of my database are avaiable

- Connect to Azure SQL Server

What i tried (without sucess):
- new accdb's with copied in tables:

• "played" around with datatypes
• only one table
• delete any relationship
• removed index'
• each above without any datas

- Different version of access, also different PCs with SSMA
- Instant of SSMA, i tried Upsizing Wizard (crys about different SQL Version)

Any Ideas what and where i can check, whats the problem ist. I could imagine its a problem of access rights, or something like that is blocking SSMA.

I've successfully migrated Northwind Starter and Northwind Developer editions to SQL Server, so I'm quite sure the two accdbs are not the problem.

How did you select the Access tables you want to migrate? I have noticed that this step can be a bit tricky. For example, in that screenshot I see the collapsed node for the tables with the checkbox indicating you want all of them migrated. I've noticed that the sequence in which you connect to the SQL Server/SQL Azure instance and select the tables to migrate can make a difference. Only when the options for Convert Schema, etc. light up on the ribbon will the migration of all tables actually selected at that point occur.

Also, at a higher level in your screenshot, I see a second database selected as well, "Info" which appears to be partially masked perhaps?

I do not know whether you even can migrate both source accdbs to the same SQL Azure database, although I would never try that anyway because it seems a bit too ambitious for my tastes. In any event I would start with one or the other, work it out successfully, and then try two at a time.

Migrating queries should only be done, in my opinion, on a case by case basis, by the way. That's partly because parameterized queries are not going to work out well, and you probably want to optimize your views in SQL Server.

 

[ksachen]

I've successfully migrated Northwind Starter and Northwind Developer editions to SQL Server, so I'm quite sure the two accdbs are not the problem.

I do not know whether you even can migrate both source accdbs to the same SQL Azure database

Thats what i meant with whats functioning. Northwind is fully in SSMA and i already migrated it successfully on an SQL Server.
All thinks with Northwind was only to test, if SSMA is functioning correctly with Northwind in compare to my database.

Queries

included also queries only for testing, to see if something of my DB is imported by SSMA successfully.

"Info" which appears to be partially masked perhaps?

thats the DB i want to migrate

here the db alone and fully expanded:

http://imgur.com/S2XZaJe

From my 15 tables only one the table -> "Anderungs" <- is appering in SSMA. And fortunately is the most useless, cause its a work in progress table.

And i can migrate that table, but without the other tables its pointless.

 

[GPGeorge]

Thats what i meant with whats functioning. Northwind is fully in SSMA and i already migrated it successfully on an SQL Server.
All thinks with Northwind was only to test, if SSMA is functioning correctly with Northwind in compare to my database.


included also queries only for testing, to see if something of my DB is imported by SSMA successfully.


thats the DB i want to migrate

here the db alone and fully expanded:

http://imgur.com/S2XZaJe

From my 15 tables only one the table -> "Anderungs" <- is appering in SSMA. And fortunately is the most useless, cause its a work in progress table.

And i can migrate that table, but without the other tables its pointless.

I see. I misinterpreted this statement to mean that the tables ONLY appeared in SSMA. Migrating the tables is a separate step.

" Northwind Test DB is fully available in SSMA (Tables and queries)"

The only other suggestion I have is to remove the Access accdb which you do not plan to migrate. Otherwise, it appears you are comfortable using SSMA.

We'd have to examine the accdb in question to see why tables in it are not being listed, unless they are, in fact, linked tables from another accdb?

 

[ksachen]

The only other suggestion I have is to remove the Access accdb which you do not plan to migrate. Otherwise, it appears you are comfortable using SSMA.

We'd have to examine the accdb in question to see why tables in it are not being listed, unless they are, in fact, linked tables from another a

yes, thats the point i cant figure out.

Problem with linked Tables was on of my first ideas. But also when i just use the backend .accdb oder i put everything in one .accdb, its the same problem.

What really raises my eyebrow is:
Even If i copy only one table into a new database, and i put this new database in SSMA. Still this table is not appering.

Thats why i tried these thing:

• "played" around with datatypes
• only one table
• delete any relationship
• removed index'
• each above without any datas

It "feels" there is something going on with access(not the programm, in terms of owner/security) rights.

 

[GPGeorge]

The things you mentioned above would not, so far as I understand, make any difference in whether the tables could be migrated. There are certain field types that can't be migrated--e.g. attachments and lookups--but that should not prevent the table from being migrated.

Can you share the problem accdb? I can't diagnose the problem without being able to inspect it.
If necessary, you could just supply the accdb without data. Leave the tables themselves as they are in the production accdb, with primary keys and relationships intact.

 

[ksachen]

ok thanks, will prepare the clean or bullshit data version tomorrow

 

[The_Doc_Man]

ok thanks, will prepare the clean or bullshit data version tomorrow

We ask that you try to not idly toss off vulgarities in a technical thread - though you don't have to be totally dead-pan serious either. Please just be careful.

 

[GPGeorge]

After a couple of days of trying different approaches and consulting with three other Access developers, I can't identify the actual problem with the majority of tables in the sample accdb (SSMA sees and migrates only 1 table of many tables) I suspect it may be related to data or datatype, but that's sort of a residual guess after ruling out everything else I can think of.

There is a work-around, I believe. I would use Make-Table queries to create new versions of each table with its current data. Those replacement tables should migrate to SQL Server in SSMA under their new names, as they do in my test accdb. Of course, you could do the renaming first so only the currently used names are used.

There are also other tools, perhaps, to migrate the data, which you could try in addition to SSMA. I will keep coming back to this as I find time. In the meantime, the work-around of creating replacement tables might keep you moving ahead for now.

 

[ksachen]

Thanks for trying many things, reach out for other help and discussion in the messages.

it might be related to having migrated from an earlier mdb to an accdb.

Earliest Version was build with office 2007, but always as an accdb.

I will try the create again these steps replacementtables and also look up if any of my old versions of the accdb behave same or diffrent in SSMA

 

[ksachen]

Workaround:

I would use Make-Table queries to create new versions of each table with its current data.

Yes, this is functioning , that table is shown in SSMA.
I will proceed this with the other tables and check out if everything is function than in the local version and then proceed with the migration to sql.

I also put all my old yearly versions into SSMA and the first two versions were correctly show with all tables. I will check for the difference and can also send you a version with nonsense data @GPGeorge

Again thank you very much

 

[isladogs]

As one of the developers approached by @GPGeorge about this issue, I was equally baffled.
I couldn't see any issues in either your table structure or data but could also only see one table in SSMA

Copying the table structure with or without the data didn't help.
The Database Documenter didn't show any issues
However, creating completely new tables using make table queries also worked for me.

I can only assume somehow the original table structure had somehow got corrupted in an obscure way that made them invisible to SSMA.
Very odd indeed!

 

[GPGeorge]

The problem turned out to be a property on tables, added to the tables in a previous migration using SSMA, that was preventing SSMA from showing and migrating the tables again.

I've provided an explanation privately.

 

[ksachen]

added to the tables in a previous migration using SSMA, that was preventing SSMA from showing and migrating the tables again.

Did that in the winter for first testing and proceed with some other stuff, but also at that time only one table was visible.

Funny thing is, when i check the property also for my old versions (4 years ago) of the database, there is also the SSMATableState, but i never used that tool before.

So I tried the vba way and could fix it with this:

tables with property problem had:
SSMATableState: back up copy

Migratable tables had no entry for SSMATableState

chat gpt code to show the property of "YourTableName"
code was put in a module
and excuted in the immediate windows

Tested on 4 Tables, all 4 are visible in SSMA. Also no problems after these steps with the database itself incompare to before.

Sub ShowTableProperties()
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prp As DAO.Property
    Dim propName As String
    Dim propValue As Variant
   
    ' Open the current database
    Set db = CurrentDb
   
    ' Specify the table name
    Dim tableName As String
    tableName = "YourTableName" ' Change to your table name
   
    ' Get the table definition
    On Error Resume Next
    Set tbl = db.TableDefs(tableName)
   
    If tbl Is Nothing Then
        MsgBox "Table not found!", vbExclamation
        Exit Sub
    End If
   
    ' Loop through each property of the table
    Debug.Print "Properties of Table: " & tableName
    For Each prp In tbl.Properties
        propName = prp.Name
        propValue = prp.Value
       
        ' Handle different data types of properties
        Select Case VarType(propValue)
            Case vbString
                Debug.Print propName & ": " & propValue
            Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
                Debug.Print propName & ": " & propValue
            Case vbBoolean
                Debug.Print propName & ": " & IIf(propValue, "True", "False")
            Case vbDate
                Debug.Print propName & ": " & Format(propValue, "yyyy-mm-dd hh:nn:ss")
            Case Else
                Debug.Print propName & ": " & "Unknown type or value"
        End Select
    Next prp
   
    ' Clean up
    Set tbl = Nothing
    Set db = Nothing
End Sub

Code to Show the Properties

Sub DeleteTableProperty()
    Dim db As DAO.Database
    Dim tbl As DAO.TableDef
    Dim prp As DAO.Property
    Dim tableName As String
    Dim propName As String

    ' Open the current database
    Set db = CurrentDb
   
    ' Specify the table name and property name
    tableName = "YourTableName" ' Change to your table name
    propName = "SSMATableState" ' Change to the name of the property you want to delete

    ' Get the table definition
    On Error Resume Next
    Set tbl = db.TableDefs(tableName)
   
    If tbl Is Nothing Then
        MsgBox "Table not found!", vbExclamation
        Exit Sub
    End If

    ' Check if the property exists
    Set prp = Nothing
    On Error Resume Next
    Set prp = tbl.Properties(propName)
    On Error GoTo 0

    If prp Is Nothing Then
        MsgBox "Property '" & propName & "' not found!", vbExclamation
    Else
        ' Delete the property
        tbl.Properties.Delete propName
        MsgBox "Property '" & propName & "' has been deleted.", vbInformation
    End If

    ' Clean up
    Set prp = Nothing
    Set tbl = Nothing
    Set db = Nothing
End Sub

 

[isladogs]

Here is some much simpler code for removing the SSMATableState property from all tables

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

I'd never noticed the SSMATableState property until today but have since done some experimenting.
Exporting tables to SQL Server using SSMA does not automatically create the property in the Access tables.

However, 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
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 get the SSMATableState property with value: back up copy
This prevents the table being re-exported using SSMA

So my guess is that at some time in the past, you or someone else did the following:
1. Exported using SSMA
2. Accepted the option to link back
3. Deleted the linked tables
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 backup copies starting with SSMA$ remain invisible in SSMA but the previously linked tables that are now local are available for export again!

Hope that all makes sense!


However