none
Importing Access tables ino SQL while preserving Primary Keys and Relationships RRS feed

  • Question

  • (HOPE THIS IS THE RIGHT FORUM TO POST MY QUESTION)

    I need to convert a simple Access 2016 database into an SQL database.

    I installed SQL Server 2019 Express on my PC.

    I created a "New Database" and used the Task > Import Data procedure to launch the "SQL Server Import Wizard" which is the preferred route, I'm told, these days, to accomplish this task. I wish I could still use the old "SQL Server Migration Assistant for Access" for, I'm told, that is preserves relationships between the tables, see this post on StackOverflow:

    https://stackoverflow.com/questions/30082858/import-access-database-into-sql-server-preserving-relationships-between-tables

    My problem is that, no matter which option I chose as the Source or Destination, i the Wizard, none of my Tables Primary Keys come accross intact into the resulting SQL Tables. And there is no traces of the original Table Relationships. 

    Is that the situation now? All Keys and Relationships have to be recreated in SQL?

    Sunday, March 15, 2020 5:46 PM

Answers

  • Thank you Drake but I since found that Microsoft *does* offer a tool designed specifically for migrating Access databases to SQL.
    It is called Microsoft SQL Server Migration Assistant for Access.
    As of March 2020, I downloaded it from: 
    https://www.microsoft.com/en-us/download/details.aspx?id=54255.

    It works perfect!


    • Edited by roger.breton Friday, March 27, 2020 12:14 PM Update
    • Marked as answer by roger.breton Friday, March 27, 2020 12:14 PM
    Friday, March 27, 2020 12:13 PM

All replies

  • Found some information with regards to my "problem', here:

    http://williamsportwebdeveloper.com/cgi/wp/?p=153 Here is the post in question:

    [Begin quote]

    One of my biggest problems working with SQL Server databases is preserving my primary keys and identity fields when I try to copy tables from one database to another. The SQL Server Import and Export Wizard likes to remove primary keys and you lose your identity fields. This results in many errors when the web application attempts to insert new records without providing a value for the identity field because it is suppose to auto-increment. Today I figured out how to work around this problem using SQL Server 2005. Needless to say, I did not find much information about this topic on the Internet. The first thing you should do is run the Script Wizard to generate CREATE statements. Make sure the Script Primary Keys option is set to True. The tables in the destination database need to be created by running this script in order to get your primary keys and identity fields. The tables will not contain any rows of data.

    Next you run the SQL Server Import and Export Wizard to copy your data into the tables. The trick here is to click the Edit Mappings button in the SQL Server Import and Export Wizard and check both Delete rows in existing destination tables and Enable identity insert in Transfer Settings.

    SQL Server Import and Export Wizard

    Transfer Settings

    Unfortunately, this won’t help you if you want to import data into an existing table. You can’t just delete all its rows and import using identity insert or you’ll wind up with ID numbers that increment from the last value of the identity field. You need to drop the table and recreate it from a script to preserve your exact identity field ID numbers.

    [End quote]

    Apparently, SQL Server Import 'likes' to remove primary keys and the identity field is lost. Being relatively new to SQL, I'm having a hard time 'decode' his suggestion. From what I gather, I need to create a New Database and run the Script Wizard to generate CREATE statements, with the Script Primary Keys option set to True. I understand, at this point, that the Tables will not contain any data. So I am to create all the Tables I need with all their Columns inside the script, using the right data types -- not for the faint of heart. And THEN, I am to run the SQL Server Import Wizard to extract the data from my Access database while not forgetting to 'click' the Edit mappings button to check both the Delete Rows in existing destination tables and Enable identity insert option in Transfer Settings.

    The poster goes on to write that "This won't help if you want to import data into an existing table" but, fortunately, this is not my case.

    Sunday, March 15, 2020 6:05 PM
  • Hi,

    Thanks for posting here.

    This forum is for discussing about General Windows Desktop Development Issues. But this thread seems to be more related to SQL Server or Access.

    I will redirect this case to the correct forum for the better support, thanks for understanding.

    Best Regards,

    Drake


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, March 27, 2020 5:30 AM
  • I'd try asking for help over here.

    https://social.msdn.microsoft.com/Forums/Lync/en-US/home?forum=accessdev

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-msoffice_custom

     

     



    Regards, Dave Patrick ....
    Microsoft Certified Professional
    Microsoft MVP [Windows Server] Datacenter Management

    Disclaimer: This posting is provided "AS IS" with no warranties or guarantees, and confers no rights.

    Friday, March 27, 2020 11:45 AM
    Moderator
  • Thank you Drake but I since found that Microsoft *does* offer a tool designed specifically for migrating Access databases to SQL.
    It is called Microsoft SQL Server Migration Assistant for Access.
    As of March 2020, I downloaded it from: 
    https://www.microsoft.com/en-us/download/details.aspx?id=54255.

    It works perfect!


    • Edited by roger.breton Friday, March 27, 2020 12:14 PM Update
    • Marked as answer by roger.breton Friday, March 27, 2020 12:14 PM
    Friday, March 27, 2020 12:13 PM