Results 1 to 6 of 6
  1. #1
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94

    Migrating MS Access Database to SQL Server Database.

    Dear All,



    I'm working on MS Access DB Table , but i want to migrate MS Access DB to Sql Server.
    I'm new in sql server So please assist me to how to migrate.

    Thanks & Regards,
    Rgds,
    aligahk06

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    there is a wizard that assist you in the migration, but I would not advise it.
    First on creation of your database, you have to decide the size of your database files: in SQL server you have a database file and a log file. You need to make sure that there is as less autogrowth as possible. If you install the database using the full recovery model ( = standard) be sure that you set up a log backup every hour or every 15 minutes or so, otherwise the size of your log file might explode. If you're content to take a daily back-up I would set the recovery model to simple.
    Than I would recreate the tables manually as the SQL datatypes differ from the Access datatypes and I would not rely on the automatic translation made by the wizzard.
    Also take a moment to look at the use of schema's. Standard all tables are created in the dbo schema. But if you have different groups of users with different levels of security it is best to use different schema's per group as you can set the security per schema. In any case take the time to set up the security in a proper way.
    For the Access queries that you often use as a base for reports, forms you can create views on SQL server.
    And, as with all new software, take your time to explore the possibilities of the new software before starting the project.

  3. #3
    aligahk06 is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Apr 2010
    Posts
    94
    Sir,

    Thanks for guidance..

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    No problem, but it is mrs, not sir

  5. #5
    wvmitchell is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    24
    If you're new to SQL Server, I would recommend using the Migration Wizard. That will give you a good introduction to what works & doesn't when moving to sql.
    The wzard lets you create a Visual Studio project that you can modify & run multiple times until everything works out. My last conversion took a dozen runs due to inappropriate naming for a few items, but it's in production 10 years now, even after moving from SQL 2008 / Access 2008 to SQL 2014 / Access 2016.

    I would suggest migrating only the tables. The converted queries / views may or may not work, depending on whether you have form parameters.

    Two more important points: First: inappropriate naming - make sure to inspect all the table and column names to ensure that you don't have spaces, symbols, or other stuff that you sometimes find in Access databases. You can use a utility like Find & Replace from http://www.rickworld.com/ and that also allows you to run multiple tests as well.

    Second, make sure that you have a primary key on every table that you intend to update from the Access app, and that you have the proper relationships established before you start any conversion.

    HTH

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 5 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-06-2017, 10:22 AM
  2. Error when migrating Access database to SQL Server
    By AnneForumer in forum Import/Export Data
    Replies: 1
    Last Post: 11-18-2016, 10:24 PM
  3. Replies: 2
    Last Post: 04-27-2016, 06:20 AM
  4. Replies: 4
    Last Post: 03-05-2015, 08:50 PM
  5. Replies: 5
    Last Post: 09-12-2014, 10:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums