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

data migration

Dear All,
I'm having to manage data migration from a proprietary db system to an in-house designed sql server db.
Is there anyone out there who dealt with this and used software or services of companies specialising in this?
All ideas are welcome, thanks in advance!I would export the data as csv or bunch of insert statements and push them into the new db via bulk insert. thats the most db system independent way, I know and it only needs a dozen lines of code.|||The challenge is mapping data elements and logic between the two systems. And you will need to do custom coding for that. Probably a LOT of custom coding.
My advice would be to load the legacy data into staging table in your new database, and then draft sql procedures or scripts to transform it to your new schema. This way the process is repeatable if logical errors occur.
And my advice would be to get a competent DBA to help you with this and expect it to take a couple weeks to get it right.|||I've done something similar to this before.

Basically, what was needed then was to migrate all data from a proprietory db to MSSQL. Luckily, we were able to connect to that database through ODBC. We added that to MSSQL and from there wrote scripts to do the migration.

I agree with blindman, you may need a lot of coding to do this. We spent few weeks to do it cause almost everything (table structures, fields, etc) is different.

It's not difficult but you may need time to do it.

Data Loss in SQL Server 2000

I set up a server for a client with Small Business Server
2000. Everything is working well except for SQl server.
The database was designed by a third party. Said third
party imported the database and everthing looked ok. The
database uses Access XP as its front end. I have the
latest patches on all parts of the server including SQL
2000 and the lastest ODBC drivers on all desktops (around
5-7, all running XP prof or 2000). Now onto the problem.
Users have been experiencing data ie records they update
sometimes do not show the changes when check them later
(anywhere from a couple of hours to next day). I have
been called out to look for network problem or server
overload etc. Everything I check out is fine. Third party
has not really looked into problems with logs etc and
keeps bringing up things for me to do. How can I check
for problems with the database and what tools should I
use? I do not have much experience with SQL server as
this is my only client that uses it. I am very
experienced with server and exchange. Any help would be
greatly appreciated."Tim" <tim@.buckeyemicro.com> wrote in message
news:133e01c37316$c70b57f0$a301280a@.phx.gbl...
> I set up a server for a client with Small Business Server
> 2000. Everything is working well except for SQl server.
> The database was designed by a third party. Said third
> party imported the database and everthing looked ok. The
> database uses Access XP as its front end. I have the
> latest patches on all parts of the server including SQL
> 2000 and the lastest ODBC drivers on all desktops (around
> 5-7, all running XP prof or 2000). Now onto the problem.
> Users have been experiencing data ie records they update
> sometimes do not show the changes when check them later
> (anywhere from a couple of hours to next day). I have
> been called out to look for network problem or server
> overload etc. Everything I check out is fine. Third party
> has not really looked into problems with logs etc and
> keeps bringing up things for me to do. How can I check
> for problems with the database and what tools should I
> use? I do not have much experience with SQL server as
> this is my only client that uses it. I am very
> experienced with server and exchange. Any help would be
> greatly appreciated.
>
First, SqlServer doesn't loose updates. Period. It just doesn't happen.
So 99% this is an application problem, either an outright bug, or a
concurrency problem like users overwriting each others changes.
You could use a tool like Lumigent's log explorer
(http://www.lumigent.com/products/le_sql/le_sql.htm) to see what happened to
the changes. Or you could start a SQLProfiler trace and just let it run to
a file on the server, and dig through it later for evidence of the missing
updates. Your best hope is to get the users to reproduce the error:
concentrate on having multiple users making changes that might interfere
with each other. If you can describe exactly how to reproduce the problem
the software vendor should be able to fix it.
So in short, this isn't your problem, but you may have to play dective to
get the ball rolling. But you've got to fight when the third party puts the
ball back in your court. This is a common tactic that software people use
to deflect blame or stall for time (at least so I've heard ;)). Be helpful,
but don't be a pushover. SQLServer doesn't just loose updates. Only
applications can do that. If there were any kind of network problem or
serious server problem the users would be getting errors, not lost updates.
David