How to copy content from Access to SQL Server 2005 database


Currently I use a Access database. This is filled with data. I want to re-create the structure manually in MS SQL Server database (upsizing from within MS Access seems not to be a good way to do this).

How can I copy the content from Access to SQL Server 2005 database without loosing referential integrity?
- when a table in Access has an autoincrement field, when copying this field to MS SQL Server, the record value of the autoincrement field must remain the same.
- different fieldtypes must be copied (memo, integer, text, boolean, etc.).

Stef MerlijnDeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


Do you have MS Enterprise Manager?

Must this be in Delphi Code?

Geert GOracle dbaCommented:
There is a tool that comes with MSSQL database
It's called SQL Server Integration Services

You basically create a project to transform a database from access to mssql
i haven't done it in the new version but the steps are the same:
specify a database source (your access)
specify a destination database (mssql 2005)
start the copy progress of structure and data
that's it

because they are both microsoft products this works fairly well :)
Here is a way I do it, which goes through each record in the Access database then populates the table in MSSQL. I do not put any data or try to put any data in the autoincrment field. I would use some other distinct key since autoincrement will be continuous in the new table while you may have deleted data in the original table.

Anyway... just another, albeit slower way to do it.

while not AccessTable1.eof do //ADO Datasaet
     ADOGTbl.Insert;  // ADO dataset
     ADOGTbl.FieldByName('BullType').asString := KADaoTable1.FieldbyName('BullType').asString;
     ADOGTbl.FieldByName('BullNum').asString := KADaoTable1.FieldbyName('BullNum').asString;
     ADOGTbl.FieldByName('BullDate').asString := KADaoTable1.FieldbyName('BullDate').asString;
     ADOGTbl.FieldByName('LastName').asString := KADaoTable1.FieldbyName('LastName').asString;
     ADOGTbl.FieldByName('FirstName').asString := KADaoTable1.FieldbyName('FirstName').asString;
     ADOGTbl.FieldByName('MidName').asString := KADaoTable1.FieldbyName('MidName').asString;
     ADOGTbl.FieldByName('Name').asString := KADaoTable1.FieldbyName('Name').asString;
     ADOGTbl.FieldByName('LKAddress').asString := KADaoTable1.FieldbyName('LKAddress').asString;
     ADOGTbl.FieldByName('LKCity').asString := KADaoTable1.FieldbyName('LKCity').asString;
     ADOGTbl.FieldByName('LKSt').asString := KADaoTable1.FieldbyName('LKSt').asString;
     ADOGTbl.FieldByName('LKZip').asString := KADaoTable1.FieldbyName('LKZip').asString;
     ADOGTbl.FieldByName('Race').asString := KADaoTable1.FieldbyName('Race').asString;
     ADOGTbl.FieldByName('Sex').asString := KADaoTable1.FieldbyName('Sex').asString;
     ADOGTbl.FieldByName('DOB').asString := KADaoTable1.FieldbyName('DOB').asString;
     ADOGTbl.FieldByName('Hgt').asString := KADaoTable1.FieldbyName('Hgt').asString;
     ADOGTbl.FieldByName('Wgt').asString := KADaoTable1.FieldbyName('Wgt').asString;
     ADOGTbl.FieldByName('HairCol').asString := KADaoTable1.FieldbyName('HairCOl').asString;
     ADOGTbl.FieldByName('EyeCol').asString := KADaoTable1.FieldbyName('EyeCol').asString;
     ADOGTbl.FieldByName('Soc').asString := KADaoTable1.FieldbyName('Soc').asString;
     ADOGTbl.FieldByName('DLNum').asString := KADaoTable1.FieldbyName('DLNum').asString;
     ADOGTbl.FieldByName('DLSt').asString := KADaoTable1.FieldbyName('DLSt').asString;
     ADOGTbl.FieldByName('Glasses').asString := KADaoTable1.FieldbyName('Glasses').asString;
     ADOGTbl.FieldByName('Info').asString := KADaoTable1.FieldbyName('Info').asString;
     ADOGTbl.FieldByName('Photo').asVariant := KADaoTable1.FieldbyName('Photo').asVariant;
     ADOGTbl.FieldByName('Status').asString := KADaoTable1.FieldbyName('Status').asString;
     ADOGTbl.FieldByName('EffectiveDate').asString := KADaoTable1.FieldbyName('EffectiveDate').asString;  

Open in new window

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Stef MerlijnDeveloperAuthor Commented:
Do you have MS Enterprise Manager?
No, I haven't.

I need to be able to do it fully automatically as database at my existing customers must be migrated too.

SQL Server Integration Services
Can this be easily used at the custmers side? How can I obtain this software? Is this also available for MS SQL Server 2005 Express?
Geert GOracle dbaCommented:
easily used ?
it's just point and click

i don't have access to a version here.
but here is the msdn link

and why not the free version of Enterprise Manager ?
Stef MerlijnDeveloperAuthor Commented:
As I mentioned before:
"I need to be able to do it fully automatically as database at my existing customers must be migrated too."

Therefor I think the best approach will be doing it all in code (with Delphi application).
So the suggestion of John is a good start, but it would be nice if it could be expanded with automatic going through all tables and fields and add them to the SQL Server database. Also I think it will take very long to do this record by record as is proposed now.
Geert GOracle dbaCommented:
hmm, have it your way ...

automatically can be done with SSIS too,
the package needs to be saved in SSIS
and run from the command line
on the server with access to the access db

i had the opportunity to do this, but had access to the server at the client
and the enterprise manager.  obviously you don't .  
It took me 15 minutes to convert the db without a single line of code :)

a Delphi app will take some time to create.
and then you need to go Johnjces's way

Well, how many records in your database do you have that you are converting from Access to SQL Server?

It really doesn't take all that long, unless each record has a zillion fields.

If you want to do it in code, the only real answer that I can see in using ADO is to copy each record from old to new as I have pointed out. The one dilemma is your auto increment field so if you use that as some sort of distinctive key, that will change unless you have never deleted any records fro the old dataset.


Stef MerlijnDeveloperAuthor Commented:
There are three MS Access database that need to be converted to ONE MS SQL Server database.

I'm interested in the SSIS approach, but if the SQL Server Integration Services is something you have to pay for, then the customer won't always have it. I don't see that it is part of the Express edition. Can you clearify on that? I also don't see the SSIS function in the free SQL Server Management Studio. Can you please confirm this?

I would:

1). Create a new MS SQL database with all needed tables, fields etc.
2). Ship this empty database with my upgrade product.
3). On site, attach the new MSSQL database to MSSQL Server.
4). Have a database upgrade application that loops through each table, (such as I provided) that will
  A). Copy each record to the correct table.
  B). Check fields that should have data but don't and report that back.

Might take a few minutes, but if you do some db and table checking it might be well worth it.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Stef MerlijnDeveloperAuthor Commented:
This info I found:
"SSMSE cannot manage SQL Server Analysis Services, Integration Services, Notification Services, Reporting Services, SQL Server Agent, or SQL Server 2005 Mobile Edition."

See for details:
Stef MerlijnDeveloperAuthor Commented:
Thank you all for your answers.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.