Data migration from MSAccess to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.

Thanks

Mahesh

Hello,

I am not replying here with any solution as such.

I would like to do same thing.

I have built complete application using MS Access 2003. Some of the highlights of this application are:

Customized login for each user without using User Level Workgroup Security features.

Each user is assigned 1 of 10 different roles. One of the roles is Admin role

Only Admin role has access to database window and all objects like tables, queries, forms, macros, modules etc.

Shift key is disabled so no one can access database window.

Admin can enabled shift key and get temporary access to database window. Shift key gets disabled on exit again.

Application has data capture front-end forms, one-click reports, quick query tool using front-end forms without query grid etc.

Only certain role can add new data, only certain role can edit data, data gets locked after certain time or status of data etc. Only ceratin role can upload/downlaod data etc.

Application is also password protected. Regular user can open application without knowing password as password is integrated in vba code. This password is essential as no one can export data from other database.

Currently all tables are stored in seperate database and linked in main application.

I would like to move all tables to SQL Server Express. I am assuming that by doing this I will be able to secure all tables better and it will also help me increasing size of application beyond 2 GB.

Please let me know step by step process to move Access Tables to SQL server express.

Thanks

|||

Hi Mahesh,

refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1 which is answered.

Welcome on a board Adukio,

using SSMA you may migrate your Access DB to SQL 2005 Refer the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1

I would suggest to refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

|||

Hemant,

Thanks for the reply. In fact i had downloaded the SSMA and trying a few things. I don't see a option where in some kind of a column mapping can be done in this tool. What i mean is that :

Table1 (Access Tale) Table2 (SQL Express Table)

Column1 Column1

Column2 Column2

Column3

If we assume a scenerio like the one above where i need to migrate table from a access table to SQL table , if the number of columns do not match (this is very much possible as my target schema has been completely redesigned) , SSMA fails to migrate the data. So i need to know is there a provision for handling a scenerio like this in SSMA?

Thanks

Mahesh

|||

Hi Mahesha,

SSMA does not handle data transformation, which is what you're wanting to do. (The Access Upsizing Wizard doesn't do this either.) The only SQL tool I know of that can do this is SSIS, wich is not included in SQL Express. If you have another version of SQL Server 2005 available, say SQL Dev, you can use SSIS to create a data transformation.

If you don't have another edition of SQL available, you will need to do this manually. I would suggest migrating the data from Access to a new database on your SQL Server, and then use queries to transform the data into your new tables. If this is a process you have to do regularly you can probably work out a process of pulling data into temporary tables and then appending them to your new schema all using Stored Procedures.

Mike

|||

Thanks Mike, I think that answers my query. The data migration is a one time activity here , so i think i don't really need to use temp tables here. May be i'll go with queries and stored procedures.

Thanks!

Mahesh