Results 1 to 3 of 3
  1. #1
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014

    Lightbulb Migrating Access to SQL Server using an ODBC

    So.... You've been using your split end database for quite some time and maybe you're approaching your 2GB limit on Access, or maybe performance is beginning to choke, and you’re ready to scale up. You may have heard that something called SQL Server Express can host your Back End(BE) while connected to an Access Front End(FE). I won't go into all the benefits of migrating your BE to SQL Server Express, however I will give you a nice laid out documentation.

    This tutorial will explain how to connect your Access FE to a SQL Server Express instance using ODBC.

    This is bare minimum coverage of the topic. I do not go into great lengths of the info.
    Products used: Microsoft Access 2013, SQL Server Express 2014

    Things I don’t cover:
    Adding users with passwords
    Adding Schemas
    Adding Filegroups

    Getting started....

    1)Create an Instance on SQL Server Express
    I will not go into detail about this, there are numerous videos explaining how to do this.

    2)Open SQL Server Management Studio(SSMS) and log on to your new SQL Server Instance.

    3)Once you are logged on, create a database by right clicking “Databases”.
    Tips: When creating your database, things to pay attention to is your "autogrowth" and “Initial size.” If you’re Access BE is close to 2GB, you will want to make your initial size of the SQL Database about 2GB.

    4)Congratulations, you now have a database on SQL Server.

    5)We will now create a DSN. The DSN is what allows Access to “talk” back and forth with the SQL Server. The talking is done through what’s called an ODBC Driver. Your computer may have several different ODBC Drivers installed. However the one we will be using today is “SQL Server Native Client 11.0.” So you may ask, Cbende2, where do I create a DSN? Well follow me down the path… Start>Control Panel>Administrative tools>Data Sources(ODBC). Open this guy up. There are three different DSN’s we can create, a “User DSN,” a “System DSN,” and a “File DSN.” For the purpose of this tutorial, I will be creating a “File DSN.” (You can see what each DSN is used for by clicking on each tab, and at the bottom of the window is a description of what each DSN is used for). Click on to the “File DSN” tab and click Add… You are now in the “Create New Data Source” window. Scroll down to “SQL Server Native Client 11.0” and select it then click next. Give your connection a name, whatever your heart desires. Follow next, and click finish. Now enter a short description. For the “Server:” we are now going to open up SSMS and right click on our Instance(It’s the long name at the top above “Databases”) and click properties. Copy the “Name.” We will now paste that into “Server:” over in the “Create New Data Source” window. Next. Next. Change default database to your new database you created. Next. Finish. Test Data Source. Ok. Ok.
    Perfect, you now have your File DSN.

    6) Lets go into our Access Back End. From here we will export the tables to the recently created File DSN. Right click on a table>Export>ODBC Database. Locate the area you saved your File DSN(I think it defaults to My Documents). Select your File DSN, and click ok. Your table will now be located on the SQL Server Database! Repeat this process for each table.

    7) Open Access Front End. Go ahead and delete your tables in the front end(We’re about to connect the front end to your SQL Server Database).

    8) In your Access FE, go to External Data>Import & Link>Link to the data source by creating a linked table>Your File DSN>Select the tables you exported. Be careful not to choose select all.

    9) Rename your tables removing the “dbo.” From their name. This will allow all your forms and queries to work correctly.

    10) Compact and repair Access FE.

    11) Open SSMS, We now have to set the primary keys and foreign keys in the database on SQL Server. An easy way to do this is through creating a new “Database Diagram.” Picture this as your relationship window in Access. Here you can also set your primary keys.

    12) One thing you must do if you want your data to be “editable,” we must add “timestamp” fields to each table. Simply open each of your tables in design view and add a field name of your choice, with data type “timestamp.” You can google why this is, just know it’s an important step allowing SQL to track when you edit a record and when you leave that record.

    Congratulations, you now have an Access Front end connected to SQL Server Express back end.


  2. #2
    ssanfu is online now Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    9)Rename your tables removing the “dbo.” From their name. This will allow all your forms and queries to work correctly.
    Here are two sites that provide code to rename the linked tables, removing the "dbo_". (see the first code example)

    BTW, thanks. I do this so rarely I always forget a step and get frustrated. I have printed this out and it is in my important info folder.
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Great! Glad it's going to be used!

    Thanks for the code by the way!

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

Similar Threads

  1. SQL Server, ODBC, DSN, Access
    By cbende2 in forum Access
    Replies: 10
    Last Post: 06-23-2015, 01:12 PM
  2. Query not working after migrating to SQL Server 2014
    By Esmatullaharifi in forum Queries
    Replies: 7
    Last Post: 04-18-2015, 05:21 AM
  3. Replies: 6
    Last Post: 08-18-2013, 12:47 AM
  4. Access security ODBC to SQL Server
    By Mark@CHP in forum Security
    Replies: 3
    Last Post: 03-27-2012, 08:31 AM
  5. Replies: 0
    Last Post: 12-01-2011, 10:23 AM

Tags for this Thread

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 - Senior Forums