[Last Call] Learn how to a build a cloud-first strategyRegister Now


How to copy content from Access to SQL Server 2005 database

Posted on 2008-11-03
Medium Priority
Last Modified: 2012-05-05

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.).

Question by:Stef Merlijn
  • 5
  • 4
  • 3
LVL 18

Expert Comment

ID: 22869177

Do you have MS Enterprise Manager?

Must this be in Delphi Code?

LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 600 total points
ID: 22870364
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 :)
LVL 18

Assisted Solution

Johnjces earned 1400 total points
ID: 22870558
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

by:Stef Merlijn
ID: 22871133
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?
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 600 total points
ID: 22874174
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 ?

Author Comment

by:Stef Merlijn
ID: 22874728
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.
LVL 38

Assisted Solution

by:Geert Gruwez
Geert Gruwez earned 600 total points
ID: 22874760
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

LVL 18

Expert Comment

ID: 22876358
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.



Author Comment

by:Stef Merlijn
ID: 22877960
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?
LVL 18

Accepted Solution

Johnjces earned 1400 total points
ID: 22878083

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.


Author Comment

by:Stef Merlijn
ID: 22878085
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: http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

Author Closing Comment

by:Stef Merlijn
ID: 31512784
Thank you all for your answers.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month18 days, 3 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question