Mark LaGrange
asked on
SQL Server Migration Assistant not reading accdb tables
Hello - I apologize for the lengh of this problem description, but I wanted to be clear on what I did to try to solve it.
I am trying to upsize an Access 365 accdb to SQL Server 2017 Express, using Migration Assistant for Access. This is a huge application, developed over the years by a semi-technical manager. The ultimate goal is to re-write the front end, but the app is crashing so often, we are migrating the back end to SQL Server to see if that will help in the short term, while I re-write the front end. (I know; in a perfect world, it should all be done at the same time)
I installed SQL Server Express 2017, Management Studio v18, and Migration Assistant 8.4 (also having to install Access 2013 Runtime to get the DAC's)
Then I migrated a copy of the production Access backend, with ~125 tables, up to SQL Server. All was good. Went suspiciouosly easy, in fact.
A few days later, I did a walk-through with the manager to link the SQL Server tables to the application front end, and as we were prompted, table by table, for the primary keys, he would point out the field(s).
In each case, I would flip back to the back end accdb, and set the primary key in the given table.
In about 2/3 of the tables, the manager did not know or could not remember what the pkey should be, so I would add an AutoNumber field to the table, set it as the pkey, and continue on.
I cancelled out of the prompts for each pkey-less table. This was a throw-away run. My intent was to get the pkey's set back in the back end accdb, repeat the upsize, re-link the tables, THEN have the manager smoke-test the front end.
Now today, I am trying to upsize the back end db again, now that it has "primary key"s.
But when I try to upsize this back end db again, SSMA does not pick up any of the tables in the back end db. (actually, it does pick up ONE table; one I created myself, as a kind of checklist).
I created two more dummy tables; SSMA saw those also, but none of the other pre-existing tables.
I followed a suggestion I found to increase the batch size on Project Settings > General > Migration to 999999999; no help
I noticed that the Access version was still 32-bit. I couldn't remember if I had installed 32-bit versions of Access 2013 runtime and SSMA, so I un-installed them, ran CCleaner, and then installed the 32-bit versions, just to be sure. Still no help. It would still only read those tables I had created myself.
The system admin got on and used a MS tool that completely cleans out all Office apps on a given pc, and re-installed 64-bit Office 365. Then I un-installed the 32-bit versions of A2013rt and SSMA, ran CCleaner, and installed the 64-bit. No help there, either. SSMA still just saw the 3 tables I had created recently.
Out of ideas, I went back and got another copy of the production back end db, ran that through SSMA, and GREAT GOOGLY MOOGLY!, it read all the tables in THAT copy.
So what effect did my adding AutoNumber fields to just SOME of the original tables have to make SSMA not read ANY of the original tables?
Sorry again for the long explanation. Thanks in advance for any help.
I am trying to upsize an Access 365 accdb to SQL Server 2017 Express, using Migration Assistant for Access. This is a huge application, developed over the years by a semi-technical manager. The ultimate goal is to re-write the front end, but the app is crashing so often, we are migrating the back end to SQL Server to see if that will help in the short term, while I re-write the front end. (I know; in a perfect world, it should all be done at the same time)
I installed SQL Server Express 2017, Management Studio v18, and Migration Assistant 8.4 (also having to install Access 2013 Runtime to get the DAC's)
Then I migrated a copy of the production Access backend, with ~125 tables, up to SQL Server. All was good. Went suspiciouosly easy, in fact.
A few days later, I did a walk-through with the manager to link the SQL Server tables to the application front end, and as we were prompted, table by table, for the primary keys, he would point out the field(s).
In each case, I would flip back to the back end accdb, and set the primary key in the given table.
In about 2/3 of the tables, the manager did not know or could not remember what the pkey should be, so I would add an AutoNumber field to the table, set it as the pkey, and continue on.
I cancelled out of the prompts for each pkey-less table. This was a throw-away run. My intent was to get the pkey's set back in the back end accdb, repeat the upsize, re-link the tables, THEN have the manager smoke-test the front end.
Now today, I am trying to upsize the back end db again, now that it has "primary key"s.
But when I try to upsize this back end db again, SSMA does not pick up any of the tables in the back end db. (actually, it does pick up ONE table; one I created myself, as a kind of checklist).
I created two more dummy tables; SSMA saw those also, but none of the other pre-existing tables.
I followed a suggestion I found to increase the batch size on Project Settings > General > Migration to 999999999; no help
I noticed that the Access version was still 32-bit. I couldn't remember if I had installed 32-bit versions of Access 2013 runtime and SSMA, so I un-installed them, ran CCleaner, and then installed the 32-bit versions, just to be sure. Still no help. It would still only read those tables I had created myself.
The system admin got on and used a MS tool that completely cleans out all Office apps on a given pc, and re-installed 64-bit Office 365. Then I un-installed the 32-bit versions of A2013rt and SSMA, ran CCleaner, and installed the 64-bit. No help there, either. SSMA still just saw the 3 tables I had created recently.
Out of ideas, I went back and got another copy of the production back end db, ran that through SSMA, and GREAT GOOGLY MOOGLY!, it read all the tables in THAT copy.
So what effect did my adding AutoNumber fields to just SOME of the original tables have to make SSMA not read ANY of the original tables?
Sorry again for the long explanation. Thanks in advance for any help.
If you are running your initial project, SSMA will typically only migrate changes. There is a setting to override this, or you may simply create a new project in SSMA.
That said, John is right. Migrating to SQL Server is no cure. As an absolute minimum, I would walk through the tables and ensure they are all ready to migrate, indeed locating the primary key. If the manager don't remember, that's life, but don't guess, and don't just add an AutoNumber. A primary key can even be compound - built from two or more fields.
Also, specify a Timestamp field to be created in every table in SQL Server. It doesn't take much but will save you from a lot of trouble later.
But first, bring the current application to a level where it doesn't crash.
That said, John is right. Migrating to SQL Server is no cure. As an absolute minimum, I would walk through the tables and ensure they are all ready to migrate, indeed locating the primary key. If the manager don't remember, that's life, but don't guess, and don't just add an AutoNumber. A primary key can even be compound - built from two or more fields.
Also, specify a Timestamp field to be created in every table in SQL Server. It doesn't take much but will save you from a lot of trouble later.
But first, bring the current application to a level where it doesn't crash.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scary task. 125 tables and nary a PK. I agree with the others. Do not pass go. Do not collect $200 until you get the schema under control. Upsizing the tables as they are won't solve any problems.
Regarding your question though, are you sure you were not trying to upsize the FE? SSMA will not upsize linked tables.
Regarding your question though, are you sure you were not trying to upsize the FE? SSMA will not upsize linked tables.
ASKER
Thank you for your responses.
You mentioned that you have issues with your current setup ...that is crashing....issues...issue
The answer to your issues is definetely NOT upsizing to SQL server...SQL server is a great database engine but is surely not the magic recipe that will resolve your issues in a snap...on the contrary it will add several new ones and unless you had your issues resolved it would probably create a havoc out of the situation you are facing..not to mention that is straight forward migration it will be dead slow on big tables.....it just needs refactorin to pretty much everything from table design to queries...to forms/reports.
Its no "accident" that there books written explicitly on this subject like this : Microsoft Access Developer's Guide to SQL Server
Yes there are plenty of migrators out there like SSMA,Access to MSSQL and a lot others...but the truth is that in most cases they will take your tables and just do a datatype matching and upload your data...they won't for example swing the magic wand and resolve normalization issues,wrong relations and so on...
So ...better start working on your current setup...just progress it to a point that is perfect ...it works as it should..it doesn't crash...its performant...etc.. and when everything is up to this level ...then start looking on utilizing the SQL server as BE in order to get the extra performance/security/remot