Connect Access
By Richard Rost
37 hours ago
Connect Access to SQL Server Online: Step-by-Step Today we will walk through how to connect your Microsoft Access database to SQL Server so you can use it online. I will show you how to install SQL Server Management Studio, set up an ODBC data source file, manually upload your Access tables to SQL Server, and then relink Access to use the online tables instead of local ones. We will also discuss options for setting up SQL Server locally, differences between file and machine data sources, and important points about connecting, security, and performance as you move your Access data online. PrerequisitesLinks
Keywords TechHelp Access, connect Access database to SQL Server online, SQL Server Management Studio, ODBC connection, migrate Access tables to SQL Server, Winhost SQL Server, linked tables, SQL Server setup, SSMS installation, pass-through query, database security
Subscribe to Connect Access
Get notifications when this page is updated
Intro Today we will walk through how to connect your Microsoft Access database to SQL Server so you can use it online. I will show you how to install SQL Server Management Studio, set up an ODBC data source file, manually upload your Access tables to SQL Server, and then relink Access to use the online tables instead of local ones. We will also discuss options for setting up SQL Server locally, differences between file and machine data sources, and important points about connecting, security, and performance as you move your Access data online.Transcript Connecting your Microsoft Access Database to SQL Server so you can use it online from anywhere is actually a lot easier than most people think.
Welcome to another TechHelp video brought to you by Access Learning Zone. I'm your instructor Richard Rost. In the last two videos we talked about why you might want to move your Access Database online and then we set up a hosted SQL Server account on Winhost. If you missed either of those two videos, you'll find links to them in the description down below.
Today we are going to connect your Access Database to SQL Server. I'll show you how to install SQL Server Management Studio, create an ODBC connection, upload your Access tables, and then relink your database so Access starts using the online SQL Server instead of local tables. By the end of this video you'll have your Access Database connected to SQL Server and working online.
This video is lesson three from my complete SQL Server Online Seminar. In the full seminar I show you everything from setting up your server, to migrating your Access Data, to connecting the front end, optimizing performance, securing the database, and even building a simple web app so people can access your data from a browser. If you'd like to follow the entire process from start to finish, there's a link in the description below.
Let's get started. In lesson three we are going to connect our Access Database to SQL Server. We're going to set up the SQL Server Management Studio. We're going to set up an ODBC data source file to connect to SQL Server. Then we're 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've got an empty database shell just sitting there waiting for stuff. We're going to install SQL Server Management Studio so that we can work with the database remotely.
Some ISPs, like even GoDaddy, have tools you can use online to add databases and tables and all that kind of stuff. I have worked with several of them and out of all of them I prefer using Microsoft's SQL Server Management Studio. It's the easiest, it's the best. It's going to be the same for everybody.
One thing 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's free. It's got some limitations, but for most small businesses it's fine. You can even run your actual office on it unless you've got, you know, 500 users. I forget what the limitations are, I can look them up.
I'm going to be putting together a whole separate seminar on setting up SQL Server on premise. 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 like "Oh no, I don't know how to database online." The focus of this seminar is to get you up and running with an online SQL Server. But 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 courses, level one, level two, level three. There is more that I could do with SQL Server than I can count on one hand and two hands and my toes, anyway.
Open up your web browser and search for the SQL Server Management Studio. I'll put a link down below in the description, but for now I don't have that link yet, so let me come in here and type it in, SQL Server Management Studio, there it is, pick the first one.
Make sure it's on Microsoft.com, of course. As you know, with everything on the web, this page may change.
Now, you'll see a link over here that says Download SQL Server. I don't want the full SQL Server at this point, I just want the management studio. It's basically the front end that lets you connect to the server that's already installed and running. Scroll down a bit, it's right here: Download SQL Server Management Studio SSMS.
You'll see it's downloading down here in the bottom. I'm 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 doesn't have all my bookmarks and all that stuff right there.
Let's open the file. Do I allow this app to make changes? Yes, it's from Microsoft, I trust it.
We're going to hit install. We're going to see some fancy stuff go by for a few minutes here. I'm obviously pausing between screens; I'm not going to make you watch the whole thing unless you want to play some Jeopardy music - I did that in one class, I forget which one it was - and then finally after a while you should see setup completed.
Close. Come down to your Start button and find it - I'm just going to type in SQL. There it is, SQL Server Management Studio 18, go ahead and open it up.
Now it's time to log into your server so remember that big block of stuff I had you put on your notepad? Keep it handy because we're going to use a lot from now on. All that information, you can use it several times.
Your server name is the data source. Mine is s25.winhost.com, that's the server that I'm 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 the stuff in and then hit connect.
It should connect to your server and if you click on Databases under here, it looks expanded, you should see your database right there - there is my PC Resell database that I set up.
This is the server and this is my database inside the server. Now, you can usually have multiple databases on the server. The package that I signed up for only gives me one, but that's okay. You can have pretty much as many tables as you want, up to the maximum size of your database.
Open this up and you will see your Table Views, which are basically queries, and a whole bunch of other stuff. Again, I'm not going over all the stuff. This is a quick how-to-get-up-and-running seminar. I will be covering all of this in my full SQL Server seminar that's coming up soon.
If you want it, if you're interested in it, it's 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 wasn't going to do this until June or July but well, it's almost June now.
Now that we've got this set up and we're connected to our database, we're going to put it aside for a minute. We're going to go back over and we're going to use Access for a bit. So just minimize this guy, just set it down there. If you're going to be using it often, which we're going to be for this class, you can just pin it. Right click, pin to taskbar and you will keep finding it on your Start menu.
At this point, I want to take one second to just mention that there is a tool available called the Microsoft SQL Server Migration Assistant. It's 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'm not a huge fan. I'm going to show you how to do it manually because I think it works better. The Migration Assistant, I've had some problems with it, I've had it not do stuff right, so I'm just going to show you how to set it up manually.
If you want to play with this tool, go ahead. The one nice thing about this is that if you've got a lot of tables, it will do them all in one shot, but I've had it where it didn't get all the tables, so it's not 100 percent perfect. So I'm just going to show you how to send your tables up manually. We'll do that first.
The database that I'm going to use is my free TechHelp database. I'm not doing it with a simple super complicated database today. We're going to make a nice simple database. If you've been following my TechHelp videos, I'm sure you've seen this before. If not, let me open it up.
It's a relatively simple database - it's got five tables. It's got customers, contacts, orders, order details, and the version history table, which we can ignore. Customer form, you just scroll through your customers here, it's got orders, order details, contacts. Very simple, and it's 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: 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's why it's called a machine data source) or you can put it in a file and 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 whoever has that file can work with the server.
If you use a machine data source, it's stuck to your machine, stored in the registry. Let me show you how to set up a file data source.
To connect to an external data source, 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 doesn't matter, it's the same thing. I'll just pick that one.
We're going to link to the data source by creating a linked table. We don't want to import. Now, you're probably saying to yourself, I don't have any tables in my server yet so how can I possibly link to them? That's okay, we're just going to set up the DSN file first and then we're going to upload some tables to the server.
For right now we're just setting up the DSN file - the ODBC connection file.
Now you've got 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'll use a machine data source, but again, this is stuck to this machine, you can't use it anywhere else. So I like to use file data sources if I'm 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's just make a file data source first. Come down here and pick New, create new data source. We're going to scroll all the way down to the bottom of this list, pick SQL Server (this plain one), Next. Give the file data source a name - I'm going to type in PC Resale. It's going to get a DSN extension but that's okay, just PC Resale. Name your database whatever you want to call it. Next.
This looks pretty simple so far. Ready? Hit Finish.
Now, give it a description. The description can be anything - PC Resale, that's fine. What SQL Server do you want to connect to? If you are on a local network and you've got SQL Server running, you could drop this list down and pick from a list of servers, but we don't, so 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'm going to paste that in there - s25.winhost.com. Then Next.
How do you want to connect to it? It's going to ask you either Windows NT authentication or SQL Server. You're always going to pick SQL Server authentication and again, use the username and password from your connection string. Remember I told you you're going to use these a lot, that's why I had you put them in Notepad. You're going to use them often, keep that handy the whole time you're developing.
Next.
Create a new data source to the SQL Server, click this on, change the default database to the one that I've 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 "tests completed successfully", which means I'm connected. My Access database now has connection to my SQL Server database. If not, you've got a problem. Go back over the steps, make sure all your passwords are correct, you typed everything in or copied and pasted everything. If not, do it exactly the way I did it. If this doesn't connect, nothing else is going to work.
Hit OK. Hit OK again, and now if you look over here, I got a bunch of stuff. This is my Documents folder by the way. It's right there, PCResale.dsn. That's the file I'm going to use to connect to this server from now on. I don't have to specify most of those options. You'll probably still need your password in a couple places. We will need our connect string again when we get in the code, but now I don't.
I'm not going to continue what I was doing here before because I'm not going to link to any table yet. So at this point, once this file is created, I'm going to hit Cancel.
Now I'm 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's fine. Hit OK. Now, does this guy look familiar? Scroll over, pick PCResale.dsn, that's the database you're sending it to. Hit OK.
You will get the SQL Server logon every time you publish up a table. That's just how this works, so go get your password, keep it handy, keep it in your clipboard if you want to. I'm going to copy and paste it.
Hit OK, give it a second.
Save export steps? No, I'm not going to do this export again so just hit Close.
Now let's go over to the SSMS and verify that table made it up there. So, minimize Access, back to the SQL Server Studio. Open up tables. There it is, dbo.customerT. Don't worry about the dbo, it's a database object, it's fine.
Open it up and you can see there's some stuff in your columns, keys. Tables in SQL Server are a little more complicated than they are in Access, but I'm going to show you what you need to know to get up and running.
Open up columns, for example, and you'll see all of the columns (fields) in the customer T - first name, last name, email address, and so on. There are some differences: short text, long text, for example. Short text in SQL Server is nvarchar. You're going to have to just get used to the differences in what they are called.
This customerID is an int and it can't be null. Text is long text. Date time field, money is currency, yes/no is bit. There are some differences. Again, I'm 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's vitally important, if you're 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 my SQL seminar, that's just the SQL language in Access. Go watch that stuff, it's 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 just type in queries over here and execute them. For example:
select * from customerT
You don't have to type in the DBO.
That's it: select * from customerT, hit Execute and 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' (use single quotes). There are some differences in the SQL syntax, I'll talk about that very shortly. If I run that now, there's 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, interfacing with it here.
You can also save these queries. If you have some favorite queries, you can save them. I'm 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've got our customer table up on the server and it's working. Now, how do I read this information from Access?
Let's go back to Access now. Here I am in Access. I'm going to delete the customer table - I've 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.
Go to External Data, New Data Source, Other Data Sources, ODBC Data Link. Go over to your DSN. Copy and paste your password, hit OK.
Here's the link tables - there's a whole bunch of stuff in here, most of it you're going to ignore, you're never going to touch half of this stuff. It's all available for people who know what they're doing and want to get into the system information.
Find dbo.customerT. The passwords: if you save the password, it is saved in the database. Anyone 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 don't give the front end to people you don't trust with having access to the data on the server.
For the purposes of this class, I'm going to pretend everybody that you're 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 log-ons and stuff in your front end database. Plus you can also set up users, usernames, and log-ons on SQL Server as well and give people different rights on the server. That I'm going to cover in the SQL Server seminar later. Today I'm just going to get everybody up and running in the same database. If you want to learn server security, that'll be in part two.
I'm going to click Save Password. If I don't save the password in this link, every time you open the database and try to connect to that table, it's going to ask you for the password, which you might want, that's 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'm going to save that password in the link. Hit OK.
Your password will not be encrypted before it's saved in the file. That's fine, save it.
Next is select unique record identifier. I don't know why they don't have it so that Access can just tell what the primary key is, but you have to just select the primary key field here.
Hit OK.
There it is, dbo.customerT. If you hold your mouse over, you'll see the connection. Don't worry, the password is not in there. You're going to hide this from your end users anyway. You're going to turn off the navigation pane and you're going to lock it down just like I do in my security seminar so they can't get into here and see this stuff.
Now it's dbo.customerT. I'm going to rename it to just customerT and now it'll basically take the place of customerT in this database. I open up the customer form, and there it is. You can scroll through the records, and everything else should work just fine.
You might notice a slight delay because now this is coming over the Internet, it's going up to your web server. We're going to talk a little bit more about how to optimize sending network traffic over the wire. This isn't super bad - it's pulling down one record at a time. But if you have something like the customer list and this is really long, thousands of items, that could take a while. We're going to talk about optimizing this in a future lesson in this class.
There are a million other optimization tricks I could show you down the line in part two, but today I am going to show you a couple of things like how to use a pass-through query to make things a little faster.
Everything I've 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'm going to show you some tricks and tips, give you some coding, 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've got small tables, a few hundred records here and there, you're good. You could start working right now, you can take your database, you can encrypt it into an ACCDE file, and you're 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.Quiz Q1. What is the primary purpose of connecting a Microsoft Access database to SQL Server as described in the video?
A. To allow the database to be accessed online from anywhere
B. To improve security for local-only databases
C. To automate all data entry tasks in Access
D. To convert Access forms into web pages
Q2. What tool does Richard recommend for managing your SQL Server database remotely?
A. Microsoft Excel
B. SQL Server Management Studio
C. Windows Registry Editor
D. Power BI Desktop
Q3. What is a benefit of using a file data source (DSN) over a machine data source when connecting Access to SQL Server?
A. It encrypts the SQL code automatically
B. It can be moved and used on other computers easily
C. It limits database access to a single user
D. It automatically sets up user permissions
Q4. What should you do before linking a table in Access to SQL Server?
A. Delete the table from Access, then recreate it by hand in SQL Server
B. Export the table from Access to SQL Server, then delete the local table
C. Link directly without uploading the table first
D. Only set up a new form, no need to modify tables
Q5. What method is used in Access to physically send your local tables to SQL Server?
A. Import wizard within SQL Server
B. Exporting the Access table using the ODBC Database option
C. Using a VBA macro to copy data
D. Printing out the table then scanning it into SQL Server
Q6. After creating a DSN file for your SQL Server connection, what is the next recommended step?
A. Immediately link all available tables from SQL Server
B. Cancel the wizard and manually export the desired tables
C. Create user accounts in SQL Server
D. Set up a database password in Access
Q7. What do you need to select when linking a table from SQL Server back into your Access database?
A. The default data type conversion
B. The unique record identifier (primary key)
C. The ODBC driver language
D. The SQL command timeout value
Q8. What is one possible risk of saving the SQL Server password in the linked table connection in Access?
A. The password will be displayed to all users when they open Access
B. Anyone with a copy of the Access front end can access SQL Server with that password
C. The password will expire automatically
D. The password will be automatically posted online
Q9. What is a common sign that your Access database is successfully linked to a table on SQL Server?
A. The linked table appears with a server icon and prefix like dbo.tablename
B. The table automatically renames itself to "SQLtable"
C. The local database size increases significantly
D. Access automatically closes after linking
Q10. According to the video, what should you do if your table export or connection to SQL Server fails?
A. Reboot your computer
B. Double check your connection string details and try again
C. Delete your SQL Server database and start over
D. Switch to a different database software
Q11. What is the main limitation of using the SQL Server Migration Assistant as described by Richard?
A. It cannot transfer any data, only table structures
B. It sometimes misses tables or does not copy all of them correctly
C. It requires you to know SQL fluently
D. It only works with cloud servers
Q12. Why should you learn basic SQL queries even when using SQL Server Management Studio?
A. To speed up your computer
B. To be able to directly interact with, query, and verify your data
C. To bypass all database passwords
D. To automatically generate forms for Access
Q13. What is a recommended security practice regarding Access front ends with linked SQL Server tables?
A. Only give the front end to users you trust with server access
B. Hide all tables and queries from every user
C. Disconnect from the internet before using Access
D. Give everyone full SQL Server rights
Q14. What is an ODBC connection primarily used for in this process?
A. Compressing Access database files
B. Connecting Access to SQL Server remote databases
C. Printing reports directly from SQL Server
D. Synchronizing Access forms with Excel
Q15. What will happen if you do NOT save the password when linking a table in Access to SQL Server?
A. You will never be able to access the table
B. Access will prompt for a password every time the linked table is accessed
C. The table will be deleted from SQL Server
D. It will export the password to a text file
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-A; 10-B; 11-B; 12-B; 13-A; 14-B; 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 will demonstrate how to connect your Microsoft Access database to SQL Server so you can use your database online from anywhere. This is actually much simpler than many people assume, and I'll walk you through all of the steps needed to make this transition.
Previously, we discussed the reasons for moving your Access database online, and we set up a hosted SQL Server account on Winhost. If you haven't seen those earlier videos, I recommend checking them out; the links are available in the video description.
In today's lesson, which is the third in my complete SQL Server Online Seminar, we'll cover every aspect of connecting your Access database to SQL Server. I'll guide you through installing SQL Server Management Studio (SSMS), setting up an ODBC connection, uploading your Access tables to SQL Server, and relinking your database so Access begins to use your new online tables instead of the ones stored locally.
By the end of this session, you will have your Access database working online via SQL Server.
If you are interested in the comprehensive process from start to finish, covering topics like optimizing performance, securing your database, and even building a simple web app interface, be sure to check out the full seminar using the link in the description below.
To summarize what we'll be doing in lesson three: we're going to set up SQL Server Management Studio, create an ODBC data source file for the connection, upload the customer table to the server, remove it from the Access database, and then link the table back from SQL Server.
Now, if you have your database set up online, you should see an empty database shell ready for data. To work remotely with your SQL Server, you'll need to install SQL Server Management Studio. While some internet service providers offer online tools for managing SQL Server databases, I recommend the official SSMS tool from Microsoft. In my experience, it's the most reliable and universally consistent solution.
If you would like to experiment with SQL Server locally in your office or create test databases, you can do that using SQL Server Express. This version is free and, while it does come with some restrictions on capacity and users, it is adequate for the needs of most small businesses. Unless you have an unusually large number of users, you will find it suitable for managing your office data. I'll be putting together a separate in-depth seminar on setting up SQL Server on premise, but today's focus is getting you online because that's what so many of you have asked for since more people are working remotely these days.
To get started, open your browser and search for SQL Server Management Studio. It's important to download it directly from Microsoft's website. Look for a link specifically for SQL Server Management Studio - not the full SQL Server download - since SSMS is just the interface that connects to your existing online database. Download and install the program, allowing it to make changes if prompted.
Once the installation is complete, open the program from your Start menu. When you launch SSMS, it will ask for a server name, authentication type, and credentials. These details are found in the connection information you received when setting up your SQL Server account - I advised earlier to keep those details in a Notepad file for easy access. Enter the server name (in my case, for example, s25.winhost.com), and choose SQL Server authentication. Fill in the username and password from your connect string. Consider saving the password if you wish to avoid re-entering it in the future.
Once connected, you should see your server listed with your databases below. For most hosting packages, you'll have a single database, but within that, you can create as many tables as allowable by your plan's database size limits. You can explore your tables, views, and various other database objects from here. While there is a lot more you can do with SSMS, including managing security and writing more complex queries, this lesson is just about getting set up.
With SSMS connected, minimize it for now since we'll work within Access for the next steps. I do want to mention the Microsoft SQL Server Migration Assistant, a tool designed to copy your Access tables to SQL Server. I've tried using it in the past, and it's fine if you have a lot of tables to migrate at once, but I've found it can sometimes miss tables or cause issues. For simplicity and greater control, I prefer doing the migration manually, and that's what I'll demonstrate here.
My sample database for this tutorial is my free TechHelp database - it's quite straightforward with five tables: customers, contacts, orders, order details, and a version history table we won't need. This is a perfect candidate for demonstrating the SQL Server connection process.
The recommended method for connecting Access to SQL Server is using an ODBC data connection. Access 2019 and Office 365 allow you to choose between a machine data source and a file data source. Machine data sources are registered to a single computer and are stored in the Windows registry, while file data sources allow you to save the connection settings in a file. I prefer file data sources because they're portable: you can move the file between computers, and anyone with the file can access the server.
To set up a file data source in Access, go to External Data and create a new data source. Choose ODBC database, then set up a linked table rather than importing. While it's true your tables may not yet exist on the server, this step is just to create the connection file. Once you go through the process, create a new data source and select SQL Server, give it a name (such as PCResale), and save it. Then specify your server name from your connection details (for example, s25.winhost.com), select SQL Server authentication, and enter your user ID and password from your connect string. Make sure to choose the correct database on the server, then proceed with the default options and test the connection. If the test is successful, you now have a working DSN file in your Documents folder - this is the file you (and others) can use for connections moving forward.
At this stage, you can cancel the linking process since there are no tables up on SQL Server yet. Now, let's get the customer table uploaded. To export a table to SQL Server, right click on the Customers table in Access, choose Export then ODBC Database. Select your DSN file, enter the password again, and proceed with the export. You may be prompted to save the export steps, but you can ignore this unless you plan to repeat the process often.
Once that's done, switch back to SSMS and refresh your tables list. You should see your table there, prefixed with dbo. to indicate the schema.
Now, in SSMS, you can explore the table structure and even run queries to view your data. For example, running a simple SELECT statement lets you confirm the data is present and correctly formatted. You will notice some differences in field types between Access and SQL Server - for instance, Access "Short Text" becomes "nvarchar" in SQL Server, and "Yes/No" fields become "bit" fields. These differences are important to recognize if you plan to do more advanced development, and I will cover them in greater detail in future lessons.
Interacting with data in SSMS is most effective by creating and executing SQL queries. Learning the basics of the SQL language, especially SELECT statements, is crucial if you plan on working with SQL Server. I have tutorials on SQL basics if you need to brush up your knowledge.
Now that the table is on your server, it's time to link it back to Access. Delete the local copy of the customer table from your Access database, as we do not want to maintain separate copies. Then go to External Data, create a new ODBC linked table using your DSN file, and supply the password when prompted. Choose the dbo.customerT table and make sure to save the password in the link if you want Access to open the table without prompting each time. Note that if you do save this password, anyone with your Access front end will be able to connect to the SQL Server using those credentials, so share it only with trusted users. You may want to consider setting up further security measures, both in Access and on SQL Server, to control who can access and modify your data.
When prompted, select the primary key as the unique identifier, then complete the linking process. Now you will see your SQL Server table linked in Access. You can rename it if you want to match the original table name.
Opening your forms or queries in Access should show the data just as before, except now it is being read from SQL Server over the internet rather than from a local file. There may be slight delays compared to local tables, especially if you're dealing with large record sets, but there are ways to optimize performance, which I'll discuss in future lessons.
Everything we've discussed here should get your Access database running online, with SQL Server handling the back end and Access as your familiar front end interface. You can export additional tables and repeat the same linking process as needed.
In the upcoming lessons, we'll explore optimization strategies, coding techniques, and tips to make your solution even more efficient and secure. For now, you have all the knowledge you need to get started putting your Access data online and working with it remotely.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Installing SQL Server Management Studio
Setting up a hosted SQL Server account
Creating an ODBC data source file
Difference between machine and file data sources
Uploading Access tables to SQL Server
Exporting Access tables using ODBC
Linking Access to SQL Server tables
Relinking forms to SQL Server tables
Testing ODBC data source connection
Selecting primary key during table link
Running SQL queries in SQL Server Management Studio
Viewing and verifying SQL Server table data
Renaming linked tables in Access
Understanding table field type differences
Saving connection passwords in Access
Basic troubleshooting for ODBC connectionsArticle Connecting your Microsoft Access database to an online SQL Server is a great way to make your data accessible from anywhere, and the process is more straightforward than you might think. Here, I will walk you through the steps needed to get your Access database working with SQL Server in the cloud, using a hosted server so your data becomes available online.
To start, you need a SQL Server database hosted online. Many companies, such as Winhost and GoDaddy, offer these services. Once you have your account and a new database set up, you will want to work with it remotely, and the best way is to use Microsoft SQL Server Management Studio. This program is the main tool for managing your SQL Server databases, giving you control over tables, queries, and every aspect of your online data.
If you want to experiment first or use SQL Server within your office, you can also install SQL Server Express locally. It is free and works well for most small business needs, though it has some user and size limitations.
To set up SQL Server Management Studio, search online for the download from Microsoft. Be careful to download directly from the official Microsoft website. Once downloaded, run the installer, approve any security prompts, and follow the simple installation steps. Once installed, you can find it in your Start menu under SQL Server Management Studio.
When you open SQL Server Management Studio, you will be prompted to connect to your server. Pull out the connection details you received when you set up your hosted database. The server name is your data source, and usually looks something like s25.winhost.com or similar. Choose SQL Server Authentication, then enter your username and password from the setup process. Save your password if you do not want to type it every time. After connecting, you will see your server and your database listed under the Databases section.
SQL Server Management Studio gives you a detailed view of your database. You can explore your tables, queries (called views), and manage other components. This tool is very powerful, so it is useful to get familiar with its layout as you continue.
Before bringing your Access data online, you will need a way for Access to talk to the SQL Server. Access communicates with external databases using ODBC data connections. There are two main types you might use: machine data sources and file data sources. A machine data source is set up for a specific computer. In contrast, a file data source is stored as a file, and any computer with access to that file can use it to connect to the server. For most people, especially if you move between computers or want others to have access, a file data source is the more flexible choice.
To create a file data source in Access, go to External Data, select New Data Source, then Other Data Sources, then choose ODBC Database. You want to create a linked table, rather than importing data. When prompted, select to create a new file data source. Name your data source file something relevant, like your company or project name. In the dialog prompts, enter your SQL Server information. For the server, enter your online SQL Server's name (for example, s25.winhost.com). Choose to use SQL Server Authentication, and provide your username and password. Finally, select your correct database from the list. Leave the other settings on their defaults and complete the setup, testing the connection at the end. If the connection test passes, you know you are ready to move data.
At this stage, you have your data source file, usually with a .dsn extension. This file is stored in your Documents folder by default, and forms the bridge between Access and your online SQL Server. You will use this file when you want to link data or upload tables.
To bring your Access tables online, you use Access's export feature. For example, to move your Customers table, right click it in Access, choose Export, then select ODBC Database. Name the table for the server, and in the prompts, select your new data source file. Enter your SQL Server password if prompted. Access will upload the table to your SQL Server database. After uploading, you can switch over to SQL Server Management Studio and verify that your table appears on the server. You should see it listed under Tables, and opening it will show your fields and records.
It is helpful to know that Access field types and SQL Server field types use different names. For example, Access short text becomes nvarchar in SQL Server, Yes/No becomes bit, and currency becomes money. These differences are mostly under the hood and do not usually affect your day-to-day usage, but it is good to be aware of them in case you are troubleshooting.
Using SQL Server Management Studio, you can also run queries directly, for example, to view all records in your table or to filter data. A basic SQL query like "select * from customerT" will show all your customer records. More advanced queries let you filter, sort, or join data from multiple tables. Knowing basic SQL language commands is important if you plan to do more with your online database.
Now that your data is in SQL Server, you can link Access back to those tables. In Access, delete the local version of the table you just uploaded (for example, the Customers table), to avoid confusion. Then, go back to External Data, choose New Data Source, Other Data Sources, ODBC Database, and use your data source file to link to SQL Server. When you connect, Access will display a list of tables to link; you usually want to find your main tables, such as Customers, and ignore the system tables.
When linking a table, Access will ask you to select the unique record identifier. This is usually the primary key, like CustomerID. Choose the correct field to ensure Access can properly edit records.
After linking, you will see the new table in Access, usually named with a prefix like dbo_ to indicate that it is from SQL Server. You can safely rename it in Access to match your preferred naming convention. Now, opening your Access forms or queries will display data from the online SQL Server table rather than your old local table. This gives you all the convenience of Access's interface, but your data is stored online and can be accessed from anywhere.
Saving the password in the link will allow users to open the database and access the linked tables without entering credentials each time. However, note that whoever has access to this Access file will be able to connect to the database with these credentials, so use caution in distributing your front-end file. Proper security and user management on the server is recommended for sensitive data and multi-user environments.
At this point, your Access database is using SQL Server as its backend. Everything in Access should work as before, but your data is now stored online. You may notice some slight lag when opening tables or forms, as data is now traveling over the Internet. For most small databases, performance is still very good. With larger tables, you may want to learn about optimizations, such as using pass-through queries to speed up operations and reduce network traffic. That involves writing queries that execute directly on the server, which can be much faster for large data sets.
In summary, connecting Microsoft Access to an online SQL Server involves preparing your server and user credentials, installing SQL Server Management Studio to administer your data, creating a reusable file data source in Access for online connectivity, exporting your tables to the server, and then relinking Access to these online tables. With this setup, you can continue working in Access while your data is safely stored and accessible on the cloud. Further optimizations and security measures are possible, but with these steps, you have all you need to start using Access with an online SQL Server backend. |
