SSMA fails to import tables (3 Viewers)

[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