Connect Access to SQL

Lesson 3: Connect Access to SQL Server ODBC

S M L XL FS | Slo Reg Fast 2x | Bookmark

In Lesson 3, we are going to connect Microsoft Access to SQL Server by setting up SQL Server Management Studio and creating an ODBC data source file. I will show you how to export a table from Access to SQL Server, delete it from Access, and then link the table back from the server. We will discuss the differences between file and machine data sources, review the process of linking tables, and go over some basics of interacting with your new SQL Server tables in both Access and SQL Server Management Studio.

Navigation

Prev: Lesson 02
Next: Lesson 04

Keywords

Access Seminars, connect Access database to SQL Server, SQL Server Management Studio, ODBC data source, export Access tables to SQL Server, link tables, SQL Server Express, upload Access data, DSN file, file data source, server authentication, pass-throug

Subscribe to Connect Access to SQL
Get notifications when this page is updated

Intro

Transcript

Sure! Here is your corrected text with only spelling, grammar, and punctuation corrected, following your instructions.

---

In lesson three, we are going to connect our Access database to SQL Server. We are going to set up the SQL Server Management Studio. We are going to set up an ODBC data source file to connect to SQL Server. Then we are going to export the customer T up to the server, delete it from the Access database, and then link to it up on the server.

Now that we have our database all set up online, we have an empty database shell just sitting there waiting for stuff. We are going to install SQL Server Management Studio so that we can work with the database remotely. Some ISPs, like GoDaddy, have tools you can use online to add databases, add tables, and so on. I have worked with several of them and out of all of them, I prefer using Microsoft's SQL Server Management Studio. It is just the easiest; it is the best. It is going to be the same for everybody.

One thing that I should also mention is if you want to set up a version of SQL Server locally in your office to play with, to set up a test database or do whatever, you can do that too. Get SQL Server Express. It is free. It has some limitations, but for most small businesses, it is fine. You can even run your actual office on it unless you have 500 users. I forget what the limitations are, I can look them up. I am going to be putting together a whole separate seminar on setting up SQL Server on-premises. The focus of this seminar is to get you up and running online because tons of people are asking me, especially with all the people working remotely now with the pandemic and all that. People are saying, oh no, I do not know how to database online. So the focus of this seminar is to get you up and running with an online SQL Server. I may be putting together a full SQL Server seminar that is going to go into a whole lot more detail. Just like my regular developer course is level one, level two, level three.

There is more that I could do with SQL Server than I can count on one hand, on two hands and my toes. Anyways, open up your web browser and you are going to search for SQL Server. For the SQL Server Management Studio, I will put a link down below in the description. But for now, I do not have that link yet, so let me come in here and type it in. SQL Server Management Studio, there it is. Let's pick the first one. Make sure it is on Microsoft.com, of course. As you know, with everything on the web, this page may change.

Now you will see a link over here that says download SQL Server. I do not want the full SQL Server at this point. I just want the Management Studio. It is basically the front end that lets you connect to the server that is already installed and running. Scroll down a bit and it is right here, download SQL Server Management Studio SSMS. You will see it is downloading down here in the bottom. I am using Edge right now, the default Windows 10 browser. I actually personally use Chrome as my normal browser, but for classes, I use this because it does not have all my bookmarks and so on.

Let's open file. Do I allow this app to make changes? Yes, it is from Microsoft. I trust it. Hit install. We are going to see some fancy stuff go by for a few minutes here. Let it do its thing. I am obviously pausing between screens. I am not going to make you sit here and watch the whole thing unless you want to. Should I play some Jeopardy music? I did that in one class, I forgot which one it was. Finally, after 18 hours go by, you should see Setup Completed. Click Close. Come down to your start button and find it. I am just going to type in SQL. It is SQL Server Management Studio 18. Go ahead and open it up.

Now it is time to log into your server. Remember that big block of stuff I had you put on your notepad? Keep it handy because we are going to use it a lot from now on. All that information - you can use it several times.

Your server name. Your server name is the data source. Mine is s25.winhost.com. That is the server that I am on. Change authentication to SQL Server Authentication. Logon and password are provided in your connect string as well. Your logon is your user ID. Your password is whatever password you gave it. Type those in or copy and paste them. Check Remember Password unless you want to constantly keep typing this stuff in. Then hit Connect.

It should connect to your server. If you click on Databases under here, it will expand. You should see your database right there. There is my PC Resell database that I set up. This is the server. This is my database inside the server. You can usually have multiple databases for a server. The package that I signed up for, I only get one. But that is okay. You can have pretty much as many tables as you want in here, up to the maximum size of your database. Open this up. In here you will see your tables, views (which are basically queries), and a whole bunch of other stuff. I am not going over all the stuff. This is a quick, hot to get up and running seminar. I will be covering all of this in my full SQL Server seminar that is coming up soon.

If you want it, if you are interested in it, it is not available yet. Email me, post something in the forum, let me know. The more people that bug me for it, the faster I will make it. Lots and lots of people bug me for this seminar, including some of my Access veterans. Very important ones. So I pushed it forward. I was not going to probably do this until like June, July, but it is almost June now.

Now that we have this set up and we are connected to our database, put it aside for a minute. We are going to go back over to Access for a bit. Minimize this guy, just set it down there. If you want, if you are going to be using it often, which we are going to be for this class, you can just pin it. Right-click, pin to taskbar, if you want to keep finding it in your start menu.

At this point, I want to take one second to mention that there is a tool available called the Microsoft SQL Server Migration Assistant. It is designed to take your Access database and copy the tables and stuff up to SQL Server. I have used it a couple of times. Personally, I am not a huge fan. I am going to show you how to do it manually because I like doing it manually. I think it works better. Migration Assistant, I have had some problems with it. I have had it not do stuff right. I am just going to show you how to set it up right. If you want to play with this tool, go ahead. The one nice thing about this is if you have a lot of tables, it will do them all in one shot, but I have had it where it did not get all the tables, so it is not 100 percent perfect. I am just going to show you how to send your tables up manually. We will do that first.

The database that I am going to use is my free TechHelp database. I am not doing it with the super complicated database today. We are going to make a nice simple database. If you have been following my TechHelp videos, I am sure you have seen this before. If not, let me open it up. It is a relatively simple database. It has five tables. It has customers, contacts, orders, order details, and the version history table, which we can ignore. Customer form, you just scroll through your customers here. It has orders, order details, contacts. Very simple. It is the perfect database for me to show how to get up and running with SQL Server.

The easiest way to connect to your SQL Server is to use something called an ODBC data connection. There are two types of data connections. There is a machine data source and a file data source. In older versions, you might see a system data source. Those are no longer used. Access 2019 and 365 use either a machine data source or a file data source. Basically, a data source file is just a way for you to take the information in that connect string that we got from the server and to set it either in your computer in the registry so that it works for your machine (that is why it is called a machine data source), or you can put it in a file. Then just anybody who has that file can access the server. I personally like using a file data source because I can move the file to different computers and whatever computer that file happens to be on can work with the server. If you use a machine data source, it is stuck to your machine. It is stored in the registry.

Let me show you how to set up a file data source.

To connect to an external data source, we are going to go up to External Data, New Data Source. You can go to From Database and pick SQL Server here, or you can go Other Data Sources and ODBC Database. It does not matter. It is the same thing. I will just pick that one.

We are going to link to the data source by creating a linked table. We do not want to import. You are probably saying to yourself, I do not have any tables in my server yet. So how can I possibly link to them? That is okay. We are just going to set up the DSN file first. Then we are going to upload some tables to the server. For right now, we are just setting up the DSN file, the ODBC connection file.

Now you have your file data sources and your machine data sources. Here are some previous ones I set up in different classes and for myself. Sometimes I will use a machine data source, but again, this is stuck to this machine. You cannot use it anywhere else. I like to use file data sources if I am planning on moving this from one computer to another, or if you have other users you want to be able to get into here. Let us just make a file data source first.

Come down here and pick New, Create New Data Source. Scroll all the way down the bottom of this list. Pick SQL Server, the plain one. Next, we are going to give the file data source a name. I am going to type in PC Resell. It is going to get a DSN extension, but that is okay. Just PC Resell, the name your database, whatever you want to call it. Next. This looks pretty simple so far. Ready to hit Finish. Now give it a description. I am just giving the description PC Resell again. That is fine.

What SQL Server do you want to connect to? If you are on a local network and you have SQL Server running, you can drop this list down and pick from a list of servers. But we do not. Again, go back to your connect string and get the name of your server. Mine is s25.winhost.com, the one I just set up. So I am going to paste that in there. s25.winhost.com. Then hit Next.

How do you want to connect to it? It is going to ask you either Windows NT authentication or SQL Server. You are always going to pick SQL Server Authentication. Again, use the username and password from your connection string. Remember, I told you you are going to use these a lot. That is why I had you put them in Notepad. We are going to use them often. Trust me.

Next. Create a new data source to the SQL Server. Click this on: Change the default database to the database that I have set up. Everything else just leave the way it is. Next. All of this stuff is fine. Leave all the defaults and hit Finish. This window pops up. Hit Test Data Source. You should see Test Completed Successfully. That means I am connected. My Access database now has a connection to my SQL Server database. If not, you have a problem. Go back over the steps and make sure all your passwords are correct. Check that you typed everything in or copied and pasted everything. If not, do it exactly the way I did it. If this does not connect, nothing else is going to work.

Hit OK. Hit OK again. Now if you look over here, I have got a bunch of stuff. This is my Documents folder, by the way. It is right there, PCRESILL.DSN. That is the file I am going to use to connect to this server from now on. I do not have to specify most of those options. You will probably still need your password in a couple of places. We will need our connect string again when we get in the code.

But now I am not going to continue what I was doing here before because I am not going to link to any table. So at this point, once this file is created, I am going to hit Cancel.

Now I am going to show you a couple of different ways to get your data up to SQL Server. Let's start by taking the customer T and publishing it up to the server.

Right-click on the customer T. Go to Export, ODBC Database. Export customer T to what in the ODBC database. Leave it customer T. That is fine. Hit OK.

Now here is this guy. Does this look familiar? Scroll over. Pick PCRESILL.DSN. That is the database you are sending it to. Hit OK. You will get the SQL Server logon every time you publish up a table. Sorry, it is just how this works. So go get your password. Keep it handy. Keep it in your clipboard if you want to. I am going to copy and paste it. Hit OK. Give it a second. Save Export steps? No. I am not going to do this export again, so just hit Close.

Now let's go over to SSMS and verify that that table made it up there. Minimize Access, back to SQL Server Management Studio. Open up tables. Come on. And there it is. dbo.customer T. Do not worry about the dbo. It is a database object. It is fine, do not worry about it.

Open it up. You can see there is some stuff in your Columns and Keys. Tables in SQL Server are a little more complicated than they are in Access. But I am going to show you what you need to know to get up and running. Open up Columns, for example. You will see all of the columns or fields in customer T: First name, last name, email address, and so on.

There are some differences. Short text is nvarchar in SQL Server. You are going to have to just get used to the differences in what they are called. This customer ID is an int and it cannot be null. Ntext is long text. DateTime field. Money is currency. Yes/no is bit. There are some differences. I am going to cover all these kinds of differences in the full SQL Server seminar.

For today, my goal is just to get you up and running and get this stuff working for you. The way that I like to interact with the data if you want to come in here and look at your table data directly is to just create a query for everything. It is vitally important if you are going to be using SQL Server, you have to learn the SQL language. At least the SELECT stuff, how to select records out of a table. Go take my SQL Server, or excuse me, my SQL seminar. That is just the SQL language in Access. Go watch that stuff. It is very important you know how to do all this.

SQL Server as a program is one thing. The SQL language is something totally different. Click on New Query and then a query window opens up over here. Now you can type in queries over here and execute them. For example,

select * from customer T

You do not have to type in the dbo. That is it. select * from customer T, hit Execute. There are your records. You can verify that all this stuff exists in the SQL Server table. You can do all kinds of stuff up here with joins and

where state = 'Florida'

You have to use single quotes. There are some differences in the SQL syntax. I will talk about that very shortly here. If I run that now, there are just my Florida records.

You can do append queries in here, inserts, you can do deletes, you can do all kinds of stuff. I usually work with the data on my website directly, just interfacing with it here. You can also save these queries too. If you have some favorite queries, you can save them. I am not going to do that right now. You can also right-click on the table and say things like Select Top 1000 Rows, Edit Top 200 Rows, whatever. There are a lot of different things you can do. It just generates this query for you and there it is.

We have got our customer table up on the server and it is working. Now, how do I read this information from Access? Let's go back to Access now.

Here I am in Access. I am going to delete the customer table. I have already moved it up to SQL Server, so I no longer need it here. It would be the same thing as if you were making linked tables between two Access databases. You basically put all the tables in one ACCDB file, delete them out of the main one, and then just link them over, or use the wizard to do it.

External Data, New Data Source, Other Data Sources, ODBC Data. Link. Go over to your DSN. Copy and paste your password. Hit OK.

Here is the Linked Tables. There is a whole bunch of stuff in here. Most of it, you are going to ignore. You are never going to touch half of this stuff. I do not. But it is all available for people who know what they are doing in here and they want to get into the system information.

You are going to find, where is it now, let's see, it is going to be, oh, way up at the top here, dbo. It is all alphabetical. dbo.customer T.

Now, here is the thing with the passwords. If you save the password, it is saved in the database. So, anybody you give this database to is going to be able to get into that table on the server, because that password is saved in the link. So do not give the front end to people you do not trust with having access to the data on the server.

For the purposes of this class, I am going to pretend everybody that you are going to give this front end to has access to whatever they need to do in the database. Yes, there are lots and lots of things you can do to secure your front end database. I cover those in my security seminar to prevent people from getting around and setting up user logons and stuff in your front end database. Plus, you can also set up users, user names and logons on SQL Server as well and give people different rights on the server. I am going to cover that in the SQL Server seminar later.

Today, I am just going to get everybody up and running in the same database. If you want to learn server security, that will be in part two.

I am going to click Save Password. If not, if I do not save the password in this link, every time you open the database and try to connect to that table, it is going to ask you for the password. You might want that. That is fine. You might want to set the database password so that for people to use your database, they need to have that password. You can also password protect the Access database file. There are all kinds of places you can put passwords. I am going to save that password in the link. Hit OK. Your password will not be encrypted before it is saved to the file. That is OK. Save it.

Next is Select Unique Record Identifier. I do not know why they do not have it so that Access can just tell what the primary key is, but you have to just select the primary key field here. What is the unique record identifier? Hit OK.

There we go. There is my dbo.customer T. If you hold your mouse over, you will see the connection. Do not worry, the password is not in there. You are going to hide this from your end users anyway. You are going to turn off the navigation pane, and you are going to lock it down just like I do in my security seminar so they cannot get into here and see this stuff.

Now it is dbo.customer T. I am going to rename it to just customer T. Now it will basically take the place of customer T in this database. Watch. I open up the customer form. There it is. You can scroll through the records. Everything else should work just fine.

You might notice a slight delay because now this is coming over the internet. It is going up to your web server. We are going to talk a little bit more about how to optimize sending network traffic over the wire. This is not super bad. It is pulling down one record at a time. But if you have something like the customer list and this is really long, thousands of items in here, that could take a while.

We are going to talk about optimizing this in a future lesson here today. We are going to cover this in this class. There are a million other optimization tricks I can show you down the line in part two, but today I am going to show you a couple things like how to use a pass-through query to make things a little faster.

Everything I have just shown you is really all you need. You now know everything you need to know to set up your SQL Server online, connect to it with SQL Server Management Studio, export your Access tables to the SQL Server and link back to them and pull the information down.

In the next couple of lessons I am going to show you some tricks and tips. We are going to get into some coding and pass-through queries. As of right now you know all you need to know to get your data up online and to work with it. If you have small tables, a few hundred records here and there, you are good. You could start working right now. You can take your database, you can encrypt it into an ACCDE file, and you are good to go.

The next couple of lessons are going to be optimizing this and making this work better and easier and showing you some cool stuff.

Thank you.

Quiz

Q1. What is the primary purpose of connecting an Access database to SQL Server as discussed in the video?
A. To allow collaborative editing of the database online
B. To back up Access data automatically
C. To store data online and allow remote access and management
D. To upgrade Access database forms to web apps

Q2. What tool does the presenter recommend for managing SQL Server databases remotely?
A. phpMyAdmin
B. SQL Server Management Studio
C. Microsoft Excel
D. PostgreSQL Workbench

Q3. Why might someone choose to use SQL Server Express locally?
A. It allows more than 500 users at once
B. It offers unlimited storage
C. It is free and suitable for small businesses
D. It is only available online

Q4. What is the benefit of using a file data source (DSN file) instead of a machine data source for ODBC connections?
A. It is permanently tied to one computer
B. It allows you to move the connection settings between computers easily
C. It encrypts all data sent to SQL Server
D. It requires no password

Q5. What is the first step in exporting a table from Access to SQL Server?
A. Linking the table directly
B. Using the Microsoft SQL Server Migration Assistant only
C. Setting up an ODBC data source file (DSN)
D. Recreating the table manually on SQL Server

Q6. What must you do in Access after moving a table (like customer T) up to SQL Server?
A. Delete the table from SQL Server
B. Delete the table from Access and then link to it on the server
C. Create a new table in Access
D. Restart the SQL Server service

Q7. What is a requirement before most other operations will work, as emphasized when testing the DSN connection?
A. The password must be stored in a separate file
B. The DSN connection must test successfully
C. The Access table must be encrypted
D. The SQL Server version must match Access

Q8. How does Access recognize the primary key when you link a table from SQL Server?
A. It asks you to manually select the unique record identifier
B. It always picks the first field alphabetically
C. It uses the highest value field as the key
D. It does not allow primary keys from SQL Server

Q9. What is a recommended strategy for users who want to interact with SQL Server data directly and efficiently?
A. Edit data only using Access forms
B. Use pass-through queries and learn SQL language basics
C. Avoid pass-through queries to keep it simple
D. Only use macros

Q10. What does the presenter say about saving the password in the DSN link?
A. It will be encrypted for all users
B. It is always required to save it
C. Anyone with the database file can connect using that password
D. You can only save the password for one user at a time

Q11. If you want to restrict which users can access specific tables or functions on SQL Server, what should you do?
A. Hide the tables in Access only
B. Set up user accounts and permissions on SQL Server
C. Use only front-end database protection methods
D. Only distribute reports, not forms

Q12. When moving data from Access to SQL Server, what major type of data needs attention due to differences in naming and type mapping?
A. Hyperlink fields
B. Attachment fields
C. Text and numeric fields (such as nvarchar vs. short text)
D. Action fields

Q13. What is the advantage of learning the SQL language for Access and SQL Server use?
A. It is only needed for advanced reporting
B. It is necessary for writing effective queries and interacting directly with the data
C. It is not helpful for Access users
D. It is only needed for macros

Q14. Why might performance be slower when using linked tables to remote SQL Server databases?
A. The tables are encrypted
B. All data is always downloaded at once
C. Data must travel over the internet, which can add delay especially with large datasets
D. Access cannot connect over the internet

Q15. What is suggested to make working with large remote tables in Access more efficient?
A. Use only forms and macros
B. Use pass-through queries and optimization techniques
C. Export the whole table each time
D. Split the table into many small tables

Answers: 1-C; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-A; 9-B; 10-C; 11-B; 12-C; 13-B; 14-C; 15-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is about connecting your Access database to SQL Server. In this lesson, I walk you through setting up SQL Server Management Studio, configuring an ODBC data source file, and finally moving your Access data up to the SQL Server. We'll cover how to export a table from Access to SQL Server, remove it from the Access backend, and then link to the data on the server, so your front end is drawing directly from SQL Server.

Once your database is ready online, you'll have an empty shell set up on your server that is just waiting for your data. I prefer to use Microsoft's SQL Server Management Studio (SSMS) to manage that database remotely. While some internet hosting companies, like GoDaddy, have their own web-based tools to manage SQL databases, I have found that SSMS is the easiest and most reliable tool for these tasks. It works consistently regardless of your hosting provider.

If you'd like to experiment with SQL Server in your office to test or develop before going live, consider SQL Server Express. This free edition from Microsoft has some restrictions, but for most small businesses or for learning purposes, it's more than enough. You can even run your company's workflow on it unless you are supporting a very large number of users. I will cover full on-premises SQL Server installation in a separate seminar at a later date. For now, our goal is to get your database up and running online, as this is something a lot of people have been asking for, especially with more remote work these days.

To begin, download SQL Server Management Studio. This will serve as the tool to connect to and manage your SQL Server. Be sure to get it directly from Microsoft's website to avoid any confusion. Once you find the correct site, download and run the installer, follow the prompts, and let it finish setting up.

When you start SSMS for the first time, you'll need the connection details from your server. I usually have everyone save this information in Notepad because you'll use it several times as you work through this process. The server name will be your data source, for example, s25.winhost.com. You'll use SQL Server Authentication (not Windows authentication), with your username and password from your connection string. I recommend enabling the "Remember Password" option for convenience. Once you connect, you should see your database under the Databases folder in SSMS. Depending on your hosting plan, you may only have a single database, but you can usually make as many tables as you need until you reach your data size limits.

Within your database, you will see areas for tables, views (which are similar to queries in Access), and other database objects. I won't cover every object in depth today. This is intended as a quick start to get you connected and online. More advanced topics will be covered in a dedicated SQL Server seminar down the road.

Once your database is online and connected through SSMS, you can set it aside temporarily and return to Access. For convenience, I suggest you pin SSMS to your taskbar if you'll be working with it frequently.

There is a tool known as the Microsoft SQL Server Migration Assistant that can help automate moving Access tables to SQL Server. While it can save time, especially when working with many tables, I have found it doesn't always work perfectly. Sometimes it misses tables or doesn't migrate everything correctly. I prefer the manual method, which I'll demonstrate in detail here.

For this tutorial, I'm using my free TechHelp database, which has a simple structure with five tables: customers, contacts, orders, order details, and version history (which we can ignore for this lesson). The customer form allows you to scroll through customers, and the other forms manage orders and contacts. This setup keeps things straightforward so you can see exactly how to get up and running with SQL Server.

To connect Access to SQL Server, you'll use an ODBC data connection. There are two main types: machine data sources (which are saved in your Windows registry and tied to your computer) and file data sources (which are saved as .DSN files and can be used on any PC that has access to the file). For flexibility, I recommend using a file data source.

To set up a file data source in Access, open External Data, choose New Data Source, and select ODBC Database. Choose to link to the data source rather than import, since at this point you may not have any tables on your server yet. Creating the DSN file now will let you use it when moving tables later.

Configure your new data source, naming it whatever you'd like (for example, PC Resell), and enter your SQL Server's name. If you're on a hosted environment (not local network), grab the server name from your connection details and type it in. Use SQL Server Authentication and enter the username and password from your notes. In the wizard, set the default database to the one you set up, and leave the other settings as defaults.

Be sure to test the data source connection. If you see a "Test Completed Successfully" message, your Access database is now able to talk to your SQL Server database. If the test fails, double-check your server name, username, and password. Accuracy is critical here; nothing else will work unless this step is correct.

Once your DSN file is created, there will be a corresponding file (for example, PCRESILL.DSN) in your documents folder. You'll use this file to connect from Access to the SQL Server for various tasks.

The next step is to export your data. For example, to move your customer table up to SQL Server, right-click it in Access and choose to export it via ODBC Database. Use your DSN file, enter your password when prompted, and let Access do its job sending the table up to the server.

Switch over to SSMS and open your database to check that the table has appeared as expected (usually prefixed with 'dbo'). Open the table structure to review columns and data types. You will notice that some Access data types get mapped to different types in SQL Server - for instance, short text becomes nvarchar, Yes/No becomes bit, and so on. I'll cover more on differences between Access and SQL Server data types in the full seminar.

To view or interact with the data in SSMS, you can use queries. I highly recommend learning SQL (the Structured Query Language) to at least the point where you can select records from tables. I have a separate SQL seminar that covers this language in detail. Use the New Query window in SSMS to write and run your SELECT statements, filter records, and confirm that the data matches what you expect.

Once you have verified the data is on the server, return to Access, delete the customer table from the backend, and set up a linked table using your DSN file. This will create a link in Access to the SQL Server version of your data. Choose the corresponding table (often listed with the dbo prefix), select the unique record identifier (primary key), and save the password in the link if you want users to connect automatically without being prompted. Keep in mind that saving the password in the Access front end means anyone with that file can access the linked table directly on the server, so handle it with care if security is a concern.

Now, when you open your form in Access, you'll be reading and writing data directly to SQL Server. There may be a slight delay because the data is coming over the internet, especially if you have large tables or lots of records. Later in the course, we'll cover optimization techniques and how to use pass-through queries for better performance.

Everything I've described up to this point is what you need to get your Access database running with SQL Server online, set up Management Studio, upload your tables, and link your forms to your new online data. In the upcoming lessons, I'll show you additional tips and tricks for optimizing performance and handling larger data sets.

A complete video tutorial with step-by-step instructions on this entire process is available on my website at the link below.

Live long and prosper, my friends.

Topic List

Connecting an Access database to SQL Server
Setting up SQL Server Management Studio
Creating an ODBC data source file
Exporting tables from Access to SQL Server
Deleting tables from Access after export
Linking Access to SQL Server tables
Installing SQL Server Management Studio
Accessing SQL Server databases remotely
Choosing between file and machine data sources
Creating a file data source (DSN)
Configuring ODBC connection settings
Testing ODBC connections
Exporting Access tables via ODBC
Verifying table upload in SQL Server
Understanding SQL Server table structure differences
Writing basic SQL queries in SQL Server
Linking SQL Server tables back into Access
Managing linked tables and their connections
Setting up unique record identifiers in linked tables
Renaming linked SQL Server tables in Access
Optimizing Access forms using linked SQL Server tables
Saving and managing passwords in linked tables

Article

In this article, you will learn how to connect a Microsoft Access database to an SQL Server database. The process involves setting up SQL Server Management Studio, configuring an ODBC data source to connect to your SQL Server, exporting a table from Access to SQL Server, removing the original table from Access, and then linking to the version stored on your server. This walkthrough will guide you through each step in detail, helping you move your Access data to the cloud and leverage the power of SQL Server.

Once you have your SQL Server database set up online, you are ready to begin. The first task is to install SQL Server Management Studio, which allows you to work with your server's database from your own computer. While some hosting providers have web tools for managing SQL Server databases, SQL Server Management Studio is often the most flexible and reliable choice. It is always a good idea to download it directly from Microsoft's website to ensure that you have the latest official version. After downloading, run the installer, follow the prompts, and allow the installation to complete.

After installation, open SQL Server Management Studio and get ready to connect to your database. You will need your connection details, which include the server name, user name, and password. These are usually provided when you set up the server or are available from your hosting provider. When connecting, choose SQL Server authentication and enter the provided credentials. Once connected, expand the Databases list in SQL Server Management Studio to confirm you can see your database.

With the connection to your SQL Server confirmed, the next step is to set up an ODBC data source. This allows Access to connect to your server over the network. There are two types: machine data sources, which store the connection settings in your computer's registry, and file data sources, which store them in a file. A file data source is recommended because you can easily move it or share it with others who need to connect to the same server. In Access, go to External Data, select New Data Source, choose From Database, and pick either SQL Server or ODBC Database. Select the option to link to the data source by creating a linked table. When prompted, create a new file data source and choose SQL Server from the list. Give your data source a name and proceed.

You will then be asked for the server name. Enter the server name from your connection information. Choose to use SQL Server Authentication and provide your user name and password. You may also select to change the default database to the one you have set up. Accept the defaults for the rest of the settings and finish the setup. Test the data source to make sure the connection is successful. The newly created data source file will be saved, usually in your Documents folder. You now have everything you need to connect to your SQL Server from Access.

Now, return to your Access database and select the table you wish to move to SQL Server, such as your customer table. Right-click on the table and choose to export it using ODBC Database. When prompted, select the file data source you just created. Enter your SQL Server password if requested, and allow Access to upload the table to the server. You do not need to save the export steps unless you plan to repeat this process. You can verify the new table in SQL Server Management Studio by refreshing the tables list; the new table should appear.

Once the table is in SQL Server, you can open it in SQL Server Management Studio to examine its structure and data. SQL Server uses different types for its fields compared to Access. For instance, short text becomes nvarchar, long text becomes ntext, dates become datetime, currency becomes money, and yes/no fields become bit. Review the differences so you understand how your data is stored in SQL Server.

When you confirm that the data transferred correctly, go back to Access and delete the original table. You will now link the table stored in SQL Server back to your Access database. In Access, go to External Data, choose to link an ODBC Database, select the same file data source you created before, and enter your password again. When the list of available objects appears, find your table in the list, usually labeled as dbo followed by the table name. You will be prompted to select a unique record identifier. Choose the primary key field, commonly the ID field for your table. This enables Access to work efficiently with the linked data.

When linking, you can choose to save the password in the link. This makes it easier to open the database without being prompted each time, but be aware that anyone who has the Access file will also be able to access your SQL Server if the password is saved. Consider your security requirements and choose accordingly.

Once linked, the table appears in Access as any normal table would. You can rename it in Access for convenience. Opening forms or running queries that use that table should now work just as before. However, you may notice a slight delay if you have a large set of records, since the data comes over your network connection. To optimize performance, consider limiting queries to retrieve only what you need and using efficient filtering techniques. More advanced strategies may include pass-through queries, which send SQL commands directly to the server for faster processing.

At this stage, your Access database is connected to SQL Server, your tables are moved online, and you can continue to use your Access interface to work with your data. You can repeat this export and linking process for other tables as needed. For coding within Access that interacts with your linked SQL Server tables, you would set up your code to work with the linked table objects, handle any potential connection errors, and optimize queries where necessary.

With these steps, you now know how to move your Access tables to SQL Server, link them back to your Access front end, and start using your data online. For smaller datasets, this process will work smoothly and let you take advantage of SQL Server's features while keeping your familiar Access interface. For further optimization and in-depth techniques, you can explore more advanced SQL Server and Access integration topics.