Migrate Access 2007 Database Application to SQL Server 2005 using SSMA - Issues...

Question

I have managed to get SQL Server 2005 Express up and running on my computer Ok in order to do some testing before trying this in the "Real World".

I have a fairly large MS Access 2007 Database application I need to migrate to SQL Server retaining the "Front End" as the user interface. (The app' is already a "split" database with a Front and Back end....)

I have done some initial testing on using SSMA to migrate my Access database To SQL Server Express.

Clearly I don't understand some things and I thought I'd see if anyone has any ideas.

Conceptually I thought that what needed to happen was that the Back End of the database that resides on the server needed to be migrated to SQL server and then the Front End re linked to the (now linked to SQL) tables in the Back End.

When I do this using SSMA I end up with renamed tables in the Back End Access file that look something like "SSMA$myTableNameHere$local". I also get the original table names underneath showing as ODBC linked tables.

So far so good.

BUT.... When I go to re-establish the linked tables from the FRONT END (The user interface) all I can see is the "SSMA$myTableNameHere$local" names NOT the original table names.(Now linked via ODBC) I can link to the "SSMA,,,," tables but it would mean changing the names of every table in every query and on every form and in all code on the Front End! Not something I really want to do.

SO....

I thought I'd try to migrate the FRONT END and see what happens.

What I ended up with is a situation where, basically it works (there are some serious errors and issues that I haven't even looked at yet... like missing data etc.!!!!) and I still get the "SSMA$myTableNameHere$local" tables and the ODBC linked tables with the original names.

I'm trying to understand...... Does this mean that we would do the migration on the Front End and then just copy the same file to each user's computer?

Another subject I'm a little confused about is that I can't link via ODBC to SQL Server Express on the local machine (ie my computer) so I can't test migrating the Back End and then linking to the tables via the Front End as I have in the past in more of a client/server situation.

Answer 1

Forgive my lack of knowledge of Acronym Soup, but I assume SSMA is the SQL Server 2005 "import data wizard" or the wizard in Access to send the data to SQL Server. It appears that you sent the data to SQL Server from Access - something you don't want to do. You want to use the DTS in SQL Server (now called SSIS or something?) to import the data into SQL Server. Then you'll have your tables in SQL Server. Then, simply create your DSN entry for the SQL Server and re-link your tables. All should be well.

Overall, the general rule is to import Access tables using SQL Server instead of using Access to send the data to SQL Server.

Answer 2

Assuming that SSMA replaces the tables in your back end with links to the SQL Server, all you need to do is delete the original table links in your front end and import the newly-created table links from the back end. You can then discard the back end, since it's not used for anything at all any longer.

Answer 3

Hi,

I'd bite the bullet and rename the tables on the SQLServer side back to the friendly names that you had in the original database. You'll probably have less problems. Especially if you have any embedded code the MS Access side.

As far as how you will deploy the MS Access side now, it should be pretty much create the ODBC link on the user's workstation, and copy the MS Access file to their desktop (although you might want to make an MDE (or the 2007 equivalent) to prevent them from accidentally breaking it).

Answer 4

Frankly, now that you have migrated, you need to look at the design of your tables. It is my experience that the wizards for Access migration do a poor job of selecting the correct datatype. For instance if you had a memo field, you might easily get away with a varchar field instead but the last wizard I used (an earlier version) always converted them to text fields. Now would also be the time consider some fixes such as making date fileds datetime instead of character based if you have had that mistake in the past.

I would never consider using a wizard again to do data migration myself having experienced how very badly they can do it.

You will alos find that just converting the data to SQL Server is often not eough to really get any performance benefit. YOu will need to test all the queries and consider if you can convert them to stored procs instead if they are slow. Eliminating the translation from Jet SQL to T-sql can being performance improvements. Plus there are many features of t-sql that can imporve performance that do not have Access equivalents. Access is not big on performance tuning, but to get the benefit of performance tuning with a SQL Server backend, you need to have SQL Server specific queries written. INdexing needs to be considered if the Access tables were not indexed properly.

Answer 5

Hi, Using SSMA is different when you use odbc. If you have an application using fully access (back end and front end). You can manipulate objects easily bounding forms, using DAO, etc.. without problem, then when u need to migrate database to sql server u can use directly odbc (by linking yourself tables to sql server), ssma, ... the main problem how to preserve bounded forms, queries, code in the client-side. If U use directly odbc you must relink by yourself all objects and change code but if u use ssma, you have to do nothing, you will continue to work as u did before. The problem with SSMA is how to deploy the front end to the clients if you developed client side in other place using another sql server?