How to copy content from Access to SQL Server 2005 database

Posted on 2008-11-03
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:Delphiwizard
    LVL 18

    Expert Comment


    Do you have MS Enterprise Manager?

    Must this be in Delphi Code?

    LVL 36

    Assisted Solution

    by:Geert Gruwez
    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

    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


    Author Comment

    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 36

    Assisted Solution

    by:Geert Gruwez
    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

    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 36

    Assisted Solution

    by:Geert Gruwez
    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

    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

    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


    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

    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:

    Author Closing Comment

    Thank you all for your answers.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
    Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now