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
Permissions
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.
Cbende2