Connecting Access
By Richard Rost

9 days ago

Lesson 10: Connect to SQL Server from Access

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

In this lesson, we connect Microsoft Access to SQL Server by setting up linked tables rather than importing, so your Access front end works with live server data. I show you the step-by-step connection process, discuss choosing the right ODBC driver, explain the difference between file and machine data sources, and clarify how to handle the DateTime2 data type. You will also learn about managing DSNs, common pitfalls in setting up connections, and some best practices for naming conventions and maintaining compatibility between Access and SQL Server.

Navigation

Prev: Lesson 09
Next: Lesson 11

Keywords

SQL Server for Access, connect Access to SQL Server, linked tables, ODBC Driver 17, file DSN, machine data source, DSN-less connection, Windows authentication, DateTime2 support, DateTime Extended, linked table manager, instance name, connection string, S

Comments for Connecting Access

Age	Subject	From
8 days	Remember to Refresh	James Nardone
8 days	Connecting SQL Server Table to Access Front End	Paul Whiteway

Subscribe to Connecting Access
Get notifications when this page is updated

Intro

Transcript

In Lesson 10, we are going to connect Microsoft Access to SQL Server finally. Up to now, we have been building everything on the server side, so this lesson will bridge the gap and bring those tables into Access as linked tables. I will walk you through the connection process step by step so you can see your SQL Server data live inside your Access front end.

Up till now, we have been working mostly inside SQL Server, but Access developers live in Access. So now we are going to connect the two.

The key idea to remember at this point is SQL Server remains the system of record. The data lives on the server. Access is just a front end that reads and edits that data.

Today we are going to be linking the tables, not importing them. Linking keeps the connection live. This is the same concept as a split Access database. If you are not familiar with this concept, go watch this video. It explains how splitting an Access database works. The only difference is here we are going to have Access front ends, but SQL Server is going to be on that back end.

Linked tables in the front end are just pointers to tables that live somewhere else in your SQL Server. When you open or edit a linked table, you are working with live data on the server.

Importing is different. Importing creates a static copy inside your Access file. That copy will not stay in sync with what is on the server. Importing is fine for reporting snapshots or archives, and we will do a lot of that. Sometimes, if you are going to be running a complicated process, it makes sense to pull down the data and then work on it locally. But generally, 9 times out of 10, we are going to link and work with the linked data.

So I am back in my TechHelp free template. We have seen this database before. This is what I am going to be using for most of the class. I will put another link to it down below on my website. If you are watching on YouTube, you will have to come to my website to get it.

So we are going to create a link to a table in our server. We are going to go to External Data. I am going to double-click to keep that open. There we go. Data - New Data Source - From Database - From SQL Server. There it is, right there. Then open up this window. Same thing we have done before. We are going to go to "Link to the data source by creating a linked table", hit OK. This screen shows data sources. There are two kinds of data sources: file data sources and machine data sources. I will talk more about these in a second.

DSN stands for Data Source Name. This is simply the name of the connection profile. It is a friendly label, a friendly name that you assign so you can recognize the connection later. This does not create a database or store data. It just stores the connection settings for you to connect Access to SQL Server.

These connections use something called ODBC, which stands for Open Database Connectivity. That is basically a communications layer that allows Access to talk to SQL Server and a bunch of other programs. But for today, all we care about is Access and SQL Server. It basically acts like a translator between two systems so they can exchange data properly. It is like using Google Translate when you go to Greece and you have to talk to someone who only speaks Greek. I did go to Greece a couple of years ago for my wife's birthday, and I was surprised. Everybody there spoke English. I did not have to use the translator at all, so that was really cool. Love Greece, by the way.

Now up here you have File Data Source and Machine Data Source. What is that all about? A machine data source is stored in the Windows configuration on this computer. It works fine, but it has to be created on every workstation that runs your Access front end.

A file DSN is stored as a DSN file, the .dsn file. Because it is a file, you can copy it, move it, email it, put it on a network share, and other machines can reuse it without rebuilding the connection manually on every machine.

Back when I used to set up small and mid-size company networks, I almost always used file DSNs. They were easy to copy from machine to machine. I would just put it on a shared folder on the server, which makes it really easy to get to. That is perfectly fine for small deployments - small, mid-size company, 3, 4, 10, a dozen users, no big deal.

In larger or more automated environments, the kinds with the dedicated IT guy, developers often move to machine data sources because they manage the profile and the security and all that. They can configure the Windows settings remotely, and they sometimes will use a machine data source.

The more advanced developer method is to use something called a DSN-less connection, which does not use a DSN at all. The connection information is stored entirely inside the Access front end, which reduces external dependencies and actually simplifies distribution. But it is a lot more advanced to set up, and we will be covering that in the developer level courses. For now, to keep things simple, we are going to use a file data source.

Since we do not have one yet, if you did, you would see it listed right here. We are going to hit New.

On this screen, you are going to select the driver that Access will use to communicate with SQL Server. There are going to be a couple of gotcha moments in this video, and the first one is right here. Most people just pick this SQL Server driver and move on. That one will work, but it is technically a legacy driver that ships with Windows. Even if you look over here to the right, the date is newer, but it does not support modern security and encryption standards as well as these other drivers: Driver 17 and Driver 18. These are Microsoft's current fully supported drivers. They include all the updated security, encryption, and compatibility features that work best with modern versions of SQL Server.

Here is another thing: both 17 and 18 will work, but Driver 18 is the newest and has the most current security defaults. However, it also enforces encryption more strictly, which can sometimes trigger certificate or trust prompts in beginner setups. For that reason, in our learning environment for a simple internal network, I usually recommend Driver 17. It tends to connect more smoothly while still providing modern compatibility.

You can pick any of these three that you want, but I suggest Driver 17. This is what I have been using for a while now. If you want to pick the other ones and you have issues, go back up and pick 17.

Let's hit Next. We are creating a new data source, so it wants the name of the file data source you want to save this connection to. Just type something in that will let you remember this later. Let's go with Kirk SQL Express. Hit Next. Verify all that is correct. That's the name of the file. How do you want to describe the data source? This is just plain English like Kirk SQL Express. That is fine.

Now, which server do you want to connect to? It is very similar to the Browse feature that we had in SSMS. This almost never works. You can click on it and sit here and wait for a minute, and it is probably not going to find your server. It never finds mine, and I know mine is set up correctly. See? It never finds it. So what we are going to do here is we are going to type in the name of the server you want to connect to, just the name of the server. So it is Kirk in my case, or you could type in the IP address if you would rather do that.

We have done this before. Hit Next. We are going to use Windows authentication. We have had this discussion before in the previous video. If you are still using SQL Server authentication, you want to use that, pick this one. I am going to pick Windows authentication. Hit Next.

Now this is another one of those gotcha moments, but I am going to leave it in the video because I used to make this mistake all the time myself, and I want this to be a learning moment for you. Everything on this screen looks okay. Everything looks fine. Not quite sure what all this stuff means, but most people just say, okay, looks great, let's hit Next. Beautiful. All this stuff looks fine. Sure. Great. Let's hit Finish. It says here are all your settings. Let's test the data source. This should pop up. Oh, the test completed successfully. Wonderful. I should be in there. Hit OK. Hit OK. There is my Kirk SQL Express DSN. Everything is set up fine. Hit OK.

Now, the link tables window comes up and you should be familiar with this. You should have seen this before when linking tables just to an Access backend. You are going to see a whole ton of stuff in here, way more than you were ever seeing in an Access database. If you scroll down this list, I am looking for my table. There is all this "sys" stuff. Do not mess with any of this "sys" stuff, by the way. I am seeing a whole bunch of stuff in here, but none of it is my table. Now my table is up here. DBO, that looks familiar. If I bring up my SSMS, I should be finding DBO CustomerT. Where is the DBO CustomerT? That is not in there. What is going on?

Well, this is the teaching moment. I wanted to leave this in the video because back when I first started using SQL Server with Access, I used to make this mistake all the time. We forgot to set the default database to our database, and instead, it is basically connecting to just the master system database, which is not what we want. We do not want that master system database. We want our database.

So how do we fix it? Yes, you can edit your DSNs, but for now, just to keep things simple, we are going to just run through the process again and create a new one. Then I will show you toward the end of the video how you can go in and get rid of that other one. So let's do it again. The repetition cannot hurt.

New Data Source - From Database - SQL Server. Link. Hit OK. This window comes up. For other DSN, we are going to create another new one. Pick Driver 17 again. Hit Next. Give it another name this time. Let's just call it Kirk 2. Our second attempt. That is fine. Next. Finish.

Now, describe the data source: Kirk SQL Express Take Two or whatever. We can rename these later, by the way. Which server do you want to connect to? Again, Kirk or your IP address. Next. Windows authentication. Next.

Now, this is the step that we missed before. Change the default database to drop this down. Look at that, there is our database and the other system databases that we do not want. So click that. Hit Next. This stuff is all fine. I will go over this in a future lesson. You do not need to change most of this. Then hit Finish.

Once again, test your data source. The test should complete successfully. If not, that is a whole different video. Hit OK. Hit OK again. There is our Kirk 2 DSN. Hit OK. Oh, look at that. There is our DBO CustomerT because now we are in the right database.

Now all your objects show up in here. DBO CustomerT. Hit OK. Now you are going to get this warning message: "The table you are linking to contains the DateTime2 data type." Yes, we set that up. "Would you like to enable linking to the DateTime2 data type? If yes, the database version will be permanently changed and it will not be compatible with Access 2013 or earlier versions. If no, Access will still treat this type as a regular DateTime type and precision will be lost."

What does all this mean? It means we are using SQL Server's newer DateTime2 data type, which we wanted to. DateTime2 is the modern replacement for the older DateTime type and supports higher precision, the fractional seconds. So Access is asking whether you want to enable full support for that data type. There is DateTime2 in Access as well.

If you click No, it will still work, but Access will treat those fields like regular DateTime values and it will chop off those fractional seconds. Unless you are using Access 2013 or earlier versions, you do not have to worry about this; say Yes. If you are still working with 2013, it is time to upgrade. That is 13 years old already. It is 2026. Get a newer version of Access. If you have to support older users, that is fine. Just say No here.

Now, it is a bit of a catch-22. Earlier, when we built our SQL Server tables, we set our date fields to DateTime2 with a precision of three. We did that intentionally so SQL Server can store milliseconds. That lines up better with how Access actually works behind the scenes. In Access, the Now function does store fractional seconds in a DateTime field, even though Access does not display them and you normally cannot work with them directly, but they are still there. We had this conversation before.

If you try to push that data into a lower precision SQL Server DateTime field, SQL Server will truncate those fractional seconds and give you grief. Now, when Access links to a table that uses DateTime2 on the server, it asks whether it should enable full support for that data type. So, in order for Access to interact properly with those higher precision fields, it has to upgrade its internal compatibility level. It feels a little circular: we configured SQL Server to support Access' hidden fractional seconds, and now Access needs to enable support to fully work with that SQL Server data type. It is a little confusing, I understand.

In modern environments, if you are working with the newer versions of Access, just say Yes and move on. The only time this matters is if you are working with Access 2013 or earlier. In that case, you would click No to preserve backward compatibility. Otherwise, enabling DateTime2 support is the correct choice.

You are still going to get another error message: "One or more newly created objects contain a data type that is not compatible with earlier versions of Access, for example." In other words, earlier versions, 2013 and earlier, but if you hit OK, you should still get your table.

If you take a look, here is the table. If you open it up, you will see there is your data. We will talk about this more in a second. If you right-click and go to Design View, now you cannot change design here, but you can look at the design. You will see CustomerSince is this new DateTime Extended. I have not even covered DateTime Extended in any of my TechHelp videos or even my Access course, because the only time you really need it is if you are dealing with SQL Server. We will talk a lot more about this in future lessons.

So there, we are all hooked up now. We have our table from SQL Server, and if you hold your mouse over, you can see there is the connection string right there.

One thing about the name: when I first started working with SQL Server and I was getting all these tables out of here and putting them in SQL Server, I used to rename these so this was just CustomerT. But now I actually kind of like the DBO there because it tells me that this is a connected table to SQL Server, and you do not forget about it when you are doing your coding, you are in your VBA. You have to remember to put the DBO in there for the name of the table, and that reminds you, oh, now I need to do these other things like DBC changes and things we will talk about in the developer class when you are working with recordsets and things of that nature. It is the same reason why I use "T"s and "Q"s and "F"s in all my object names so I know what they are when I am dealing with them.

This here is another one of those indicators. Access puts that on there when you connect to an SQL Server table. I like that. I did not like it at first, but I do now. Trust me, not a big fan of the underscore, but I have grown used to it. In fact, for a while, I was renaming all these with a dot, like DBO dot, so it matches this DBO dot, but that just gets old, and when you connect a whole bunch of tables and you reconnect stuff, it becomes a nightmare to go through and change them all the time. So I just go with what they give you now.

Let's do a little test. Let's open this up. Make sure we can see in here. Earlier I was messing around, I put some exclamation points after my name. Let's get rid of those. Let's put some pluses in there. When you move off it and the record is no longer dirty, close that. Go back over to SSMS and take a look at the records in here. Let me slide this back over here. Right-click and select 1000 rows. There you go; you can see right there, there is the +++.

So now we know it is live. Now we know we are connected. Now we know that Access is actually talking to the data in the server. We can get rid of that now. This is all live data.

So now we are all set. We are plumbed in.

Earlier, I said I would show you how to manage those ODBC connections. Easiest way: go to Start and type in ODBC. There you go; you are going to see two of them, 64-bit and 32-bit. If you are working with the older versions, the 32-bit versions, I am assuming everybody is - it is 2026 now, folks - if you are not using 64-bit Access, you need to. So I am going to open this up.

Here are your User DSNs, your System DSNs, your File DSNs. These are the ones we are going to be working with. Here are both of them.

Here is something interesting: these, once you are done with them, you do not need them anymore, so you can remove them. I could even remove this one, too. I am going to leave it here, but you can remove this because once that table is connected, it stays here. You can delete the DSN file now because if you go into the Linked File Manager - which you should be familiar with, the Linked Table Manager - there is the connection string. It is all in here. Access now has everything it needs to connect to this table in the future. The only reason you want that DSN file to stick around is if you want to add more tables, which we are going to be doing in the future.

For those of you who are curious where this stuff lives, it is actually in your Documents folder, your personal Documents folder on this machine. You can change that here. You can go up here and see there are your DSN apps. I am under my OneDrive folder, the default Documents location. In fact, if you open up File Explorer, you can actually come in here and just rename this file. Get rid of that, too. Come back here. Refresh this. I had to restart the ODBC connection, but there it is. You can click on it. Go to Configure. You can rename this, get rid of that, take two. Hit Next. Hit Next. There is your database. Next. All this is good. Hit Finish. Test your data source. It is popping up on my other window. That is why I have to do that. Hit OK. Hit OK. Now it is still good. You can add more. You can copy that to someone else's system if you want to. That is the basics of setting up a DSN.

So there you go. Now we have got our table inside our Microsoft Access database, and now we can make forms and queries in here that point to that table, just like it is a regular linked table to an Access backend. Only now it is under SQL Server.

Before we finish up, a couple of clarifications. I know sometimes I say things in the middle of a lesson that could be worded a little better. I always go back and review my videos and usually remove things like that when I go "blah, blah, blah, blah." There are a few spots where I just want to clarify, so nobody walks away with the wrong idea or has to post in the forums later and is like, "Wait a minute, but you said this."

First of all, when we talked about DateTime2 and DateTime Extended, SQL Server uses DateTime2, which we all know. In Access, what you actually see is called DateTime Extended. I showed you that on the screen. They work together, but they are not technically the same thing. Access does not have a native DateTime2 type. Access uses DateTime Extended to support SQL Server's higher precision date values. That might not have been clear earlier, but I thought it was.

A quick note about precision: Access does store the fractional time component internally, but it is not true millisecond precision. Historically, a DateTime value has a resolution of about one three-hundredth of a second. So you can see fractions, but they do not line up perfectly with exact milliseconds. The reason we use DateTime2 with a precision of three on SQL Server is that it does give us modern, high-precision date time data. It does not force us into the older SQL Server DateTime limitations. So even if we are not actively using fractions of a second in our app, DateTime2 keeps things cleaner and avoids weird rounding or truncation issues when dates move between Access and SQL Server.

Third, when I said you could pick any of those three drivers, technically yes, they may all connect, but for modern setups, you really should be using ODBC Driver 17 or 18. The one simply called SQL Server is the legacy driver, which I mentioned. It still works, but it is not recommended for new development. For beginners, Driver 17 is usually the smoothest choice with the least headaches.

Fourth, I really did not emphasize instance names enough. If you are connecting using SQL Server Express or a named instance, you might need to specify the server name and the instance name. For me, just "Kirk" worked fine, but depending on your environment, you might have to type in the instance name too, like "Kirk\\SQLExpress". We did that when we connected using SSMS. Access usually does not require that, but it might, depending on your environment. If the connection fails using just the machine name, try using the machine name backslash instance, just like we did with SSMS.

Just wanted to clear those things up so everybody is technically accurate and you are not confused by anything moving forward.

So today, we learned how to connect your Access front end to SQL Server and work with the live linked tables instead of static data. That connection lets Access stay your interface, your front end, while SQL Server handles the storage.

Post a comment down below. Let me know how you liked today's video and what you would like to see covered in future lessons.

That is going to do it for Lesson 10. Hope you learned something. Live long and prosper my friends. I will see you soon for Lesson 11.

Quiz

Q1. What is the key difference between linking and importing SQL Server tables into Access?
A. Importing creates a live connection while linking creates a static copy
B. Linking creates a live connection while importing creates a static copy
C. Both create static copies
D. Both create live connections

Q2. Why is linking tables from SQL Server into Access preferred over importing, for most use cases?
A. Linking creates a permanent backup
B. Linking keeps the data live and synchronized with the server
C. Importing is more secure
D. Importing preserves advanced data types

Q3. What does DSN stand for?
A. Data Source Network
B. Database Source Name
C. Data Source Name
D. Direct Server Name

Q4. What is ODBC used for in the context of Access and SQL Server?
A. For encrypting database files
B. For enabling Access to communicate with SQL Server
C. For managing user permissions
D. For creating backups of SQL Server

Q5. What is the main advantage of a file DSN over a machine data source?
A. Supports more advanced security features
B. Can be copied and reused on multiple computers easily
C. Runs faster
D. Is automatically created by Access

Q6. Why does the instructor recommend using "ODBC Driver 17 for SQL Server" for most Access-to-SQL Server connections?
A. It is the newest driver and always supports the latest features
B. It tends to connect smoothly and offers modern compatibility without frequent certificate prompts
C. It is the default in Windows
D. It's the only driver available

Q7. What issue does the instructor experience if the default database is not set in the connection setup?
A. The connection fails completely
B. Only system tables appear and not the intended user tables
C. Access refuses to open the linked table
D. Only partial data is available

Q8. What recent SQL Server data type does the instructor discuss supporting in Access linked tables?
A. SmallDateTime
B. DateTime2
C. TimeStamp
D. VarChar

Q9. What is the limitation when enabling DateTime2 support in Access?
A. You cannot use forms and queries
B. Access can no longer connect to SQL Server
C. It will not be compatible with Access 2013 or earlier
D. All date values become text

Q10. If you need to support Access 2013 or older when linking DateTime2 fields, you should:
A. Say Yes to enabling DateTime2 support
B. Say No, so Access treats DateTime2 fields as regular DateTime
C. Change all DateTime2 fields to text in SQL Server
D. Upgrade to SQL Server 2022

Q11. What does DBO in the table name signify when Access links to a SQL Server table?
A. It is a backup copy
B. It is connected to SQL Server and shows the schema owner
C. The table is hidden from Access users
D. The table is only read-only

Q12. Where are file DSNs typically stored by default?
A. System32 folder
B. Program Files
C. User's Documents folder
D. SQL Server root directory

Q13. After a successful link, what happens if you delete the .dsn file?
A. The linked table in Access disappears immediately
B. The linked table continues to work because Access copied the connection info
C. Access will not be able to open the table
D. The data is deleted from SQL Server

Q14. What is the more advanced method of connecting Access to SQL Server mentioned, and where is connection info stored with it?
A. File DSN, stored in user's registry
B. DSN-less connection, stored in Access front end
C. DSN-based connection, stored on SQL Server
D. File DSN, stored in Program Files

Q15. What should you try if connecting with just the machine name does not work, especially with SQL Server Express or a named instance?
A. Restart Access
B. Use the machine name and instance name separated by a backslash
C. Use "localhost" as the server name
D. Run Access as administrator

Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B; 11-B; 12-C; 13-B; 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 explores how to connect Microsoft Access to SQL Server and bring your tables into Access as linked tables. Up to this point, we have been working within SQL Server, creating tables and configuring everything on the server side. With this lesson, I am going to walk you through the process of linking your Access front end to SQL Server, so you can see and manipulate your server data live inside Access.

As Access developers, we typically spend most of our time in Access, but now it is time to link things up so Access serves as the front end and SQL Server remains the system of record. All of your data continues to live securely on the server, while Access simply reads from and writes to those tables.

It is crucial to understand the difference between linking and importing tables at this stage. Linking tables in Access establishes a live connection to SQL Server, just like working with a split Access database. If you are not familiar with split databases, I recommend checking out my lesson on that topic. In this case, instead of linking to an Access backend, we are using a SQL Server backend. Importing, on the other hand, creates a static copy of the data inside your Access file. That static data does not stay synchronized with SQL Server. Importing is useful for reporting snapshots or for working on complicated processes where it is helpful to have all of the data locally, but most of the time, linking is what we want because it keeps Access and SQL Server in sync.

For this demonstration, I am using my TechHelp free template, which you have seen in previous lessons. I will post a link to download it on my website - you will need to visit there since the template is not available directly from YouTube.

Let me explain how to link Access to a SQL Server table. Start by going to the External Data tab, followed by choosing to add a New Data Source from a Database, and then From SQL Server. Here you will want to select "Link to the data source by creating a linked table." On the next screen, you will see options involving data sources, specifically file data sources and machine data sources. Let me clarify the difference.

A DSN, which stands for Data Source Name, is simply a saved connection profile. It is not a database itself, but rather a file or configuration that stores the settings necessary for Access to connect to SQL Server. ODBC, meaning Open Database Connectivity, serves as the communication bridge between Access and SQL Server (and many other types of databases), allowing both programs to exchange data even though they speak different "languages." You can think of it like using a translation program to converse with someone who speaks another language.

Now, what is the difference between file and machine data sources? A machine DSN is stored on that specific computer and must be created on every machine that runs your Access front end. By contrast, a file DSN is just a file (with a .dsn extension) that you can copy, move, email, or put on a network share for reuse on different computers, making it very convenient for small to mid-size businesses.

For most small and mid-size deployments, I recommend using file DSNs - they are straightforward to copy across multiple machines, and you can keep them easily accessible in a shared folder. In larger organizations with IT staff, developers sometimes use machine data sources since they can centrally manage settings and security. There is also a more advanced method, known as a DSN-less connection, which stores all the connection info directly inside the Access database. This reduces dependencies and makes the Access database easier to distribute, but it is more difficult to set up. We will cover DSN-less connections in a future, more advanced course. For our purposes here, we are sticking with file DSNs.

If you already had a file DSN, it would appear on this list, but since we do not, we are going to create a new one. At this point, you need to select the driver Access will use to communicate with SQL Server. This is one of the common points of confusion. You may see an option labeled just "SQL Server" but that is an old, legacy driver. Even though it still ships with Windows, Microsoft now recommends using "ODBC Driver 17 for SQL Server" or "ODBC Driver 18 for SQL Server" for modern environments. These newer drivers support updated security, encryption, and are compatible with the latest versions of SQL Server. Driver 18 is the newest and offers stricter security defaults, which can sometimes trigger additional prompts when setting up. For learning and internal use, Driver 17 is usually the smoothest and is what I recommend.

Now, give your file DSN a name that is clear and memorable, like "Kirk SQL Express," and continue through the setup. You will be prompted to describe the data source; you can enter a simple description or just use the name again. The next step is to enter the server you want to connect to. While there is a browsing option, it rarely finds your server, so it is quicker just to type in your server name or IP address directly.

Choose Windows authentication for the login method, unless you have set up SQL Server authentication separately. After that, you will see another configuration window. It is tempting to just keep clicking Next, but here is an important point: the default database. If you do not set the default database to your specific application database (rather than the general 'master' database), you might not see your tables in Access later. This is a common stumbling block; I have made this mistake myself and wanted you to see how easily it happens.

If you miss this step and complete the setup, you will find that Access only shows you system tables, not your actual app tables. Simply go back and repeat the process, but this time, when you get to the screen where you select the database, make sure to set it to the one you've been working with (not 'master' or any other system database). Choose the appropriate driver again, give the DSN a name, select your server, use Windows authentication, and crucially, set the default database. Complete the rest of the steps, and now when you view the table list, your actual tables should appear.

When linking, Access may warn you about the DateTime2 data type, which is a newer SQL Server data type that supports more precise timestamps. Access asks if you want to enable full support for these higher precision dates. If you click Yes, your Access database will support this data type, but will no longer be compatible with Access 2013 or earlier. If you are in a modern environment or not supporting very old versions, say Yes. If you must support old versions, choose No, but be aware precision will be lost. This may sound circular, but Access has to enable special support so your date data avoids truncation or errors. Usually, it is best to say Yes and move forward.

Once everything is linked, you can view the SQL Server table inside Access, and you will see the live data. The table name will include the schema, such as DBO_CustomerT. I used to remove the DBO prefix, but now I like leaving it, as it indicates these tables are connected directly to SQL Server. It also serves as a reminder in your VBA code that you are working with SQL Server tables, which matters for technical details like connection strings and recordsets.

If you open and change data in Access, you can verify instantly on the SQL Server side (for example, using SQL Server Management Studio) that any edits you make are reflected immediately. This demonstrates that you are working on live data, not a copy.

Managing DSNs after the fact is also simple. From the Windows Start menu, search for ODBC, and you can open the ODBC Data Source Administrator. There, you will find all your user, system, and file DSNs. Once your tables are linked, Access stores the needed connection settings internally, so you do not even need to keep the DSN file unless you plan to add more tables in the future or share that connection with other users. DSN files are usually saved to your Documents folder, but you can rename, relocate, or remove them as needed. Access will continue to work with the table as long as the internal link string is valid.

Now that the SQL Server table is linked in Access, you can build queries and forms just as you would with regular Access backend tables; only now, the data resides securely in SQL Server.

Before wrapping up, I want to clarify a few technical details. Access shows the SQL Server DateTime2 type as DateTime Extended. While they are closely related, Access does not have a true native DateTime2 type; it uses DateTime Extended to handle SQL Server's extra precision. Access represents fractions of a second internally, but not with true millisecond precision. That is why we use SQL Server DateTime2 with a precision of three digits, which aligns better with how Access stores fractional times and avoids issues with data moving between Access and SQL Server.

Although technically you can choose any available driver, for new projects, stick with ODBC Driver 17 or 18. The older "SQL Server" driver is legacy and not recommended.

One more note about server names: When connecting to a named instance of SQL Server, especially Express editions, you might need to specify the instance as "ServerName\\InstanceName." For example, "Kirk\\SQLExpress." If just the machine name does not work, try including the instance as you would in SQL Server Management Studio.

In summary, today we covered how to link Microsoft Access to SQL Server, so you can work with live, synchronized tables while taking advantage of SQL Server's storage and security. All of your familiar Access tools continue to work, but under the hood, your data is managed properly on the server.

Let me know in the comments what you thought of today's lesson and what topics you would like to see explained in future sessions.

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

Connecting Access to SQL Server as a live linked table
Differences between linking and importing tables in Access
Understanding the role of Access as front end and SQL Server as backend
Step-by-step walkthrough of the External Data, New Data Source process
Creating a file DSN (Data Source Name) for SQL Server connections
File DSN vs Machine DSN and use cases for each
Selecting the appropriate ODBC driver for SQL Server
Gotchas with SQL Server "legacy" ODBC driver versus Driver 17 and 18
Naming and describing your DSN for clarity
Manually entering SQL Server name or IP address during DSN setup
Choosing Windows authentication for SQL Server connection
Fixing the issue with default database selection during DSN creation
Setting the default database to ensure correct table visibility
Linking Access tables to SQL Server with DateTime2 data type
Handling the DateTime2 and DateTime Extended compatibility prompt
Understanding the impact on backward compatibility with older Access versions
Testing linked table functionality in Access and verifying live data
Managing and removing DSN files after linking
Exploring where DSN files are stored and how to rename or delete them
Access Linked Table Manager for managing linked SQL Server tables
Clarifying differences between DateTime2 in SQL Server and DateTime Extended in Access
Dealing with ODBC connection strings after DSN setup

Article

In this lesson, we are going to learn how to connect Microsoft Access to SQL Server by linking tables so you can work with your SQL Server data directly inside your Access front end. Up to now, everything has existed only on the SQL Server side, but with this process, Access will serve as your interface while SQL Server remains your system of record, storing and managing the data. The main idea is that your data will live on the server, and Access is simply working as a smart front end, providing you with live connections instead of static snapshots.

When you link tables in Access instead of importing them, you create a connection that allows Access to read and write data live on SQL Server. Importing, by contrast, just takes a copy of the server data and brings it into your Access file as a stand-alone table. This makes sense for reports or working with a fixed set of data, but most of the time, you want Access to stay in sync with SQL Server, so linking is the way to go.

Let's walk through the process using Access. Assume you have the Access database you want to use already open. Go to External Data and choose New Data Source, then From Database, and then From SQL Server. Access will prompt you to indicate if you want to link the tables or import them; make sure you select "Link to the data source by creating a linked table" and hit OK.

The next screen asks you to choose or create a Data Source Name, or DSN, which is a saved set of connection settings. DSNs come in two main varieties: File DSN, which saves the settings in a portable .dsn file that you can copy or move around as needed; and Machine DSN, which saves the settings in your Windows configuration, making it specific to one computer. For small team setups, File DSNs are usually more convenient because they can easily be distributed across different workstations.

If you have not created a DSN yet, click New. At this screen, you need to choose an ODBC driver. While you might see an option called simply "SQL Server," that is an older driver and is not recommended for modern secure environments. You should choose "ODBC Driver 17 for SQL Server" or "ODBC Driver 18 for SQL Server." Driver 18 is the newest and most secure, but it also enforces encryption more strictly, which might require extra configuration for certificates. For most internal test environments and learning labs, Driver 17 is a reliable and compatible choice.

After you select the driver, click Next. Now you must give your new DSN file a name, such as "Kirk SQL Express," and specify a description if you like. The next step is to indicate which SQL Server to connect to. The Browse function here is unreliable; it's best to type the name of your server directly. In many cases, this is your computer name (for example, "Kirk"), or you can specify an IP address. If you're working with SQL Server Express or a named instance, you may need to use the format "ComputerName\\SQLExpress".

Next, choose the authentication method. Select "Windows NT authentication" if you want to connect using your Windows credentials, or "SQL Server authentication" if you have a separate SQL username and password configured on your server. After picking the appropriate option, click Next to continue.

On the next screen, be sure to set the default database. This is a step people commonly overlook. If you leave the default as it is, Access will connect to the SQL Server "master" database, not your application database. Use the dropdown list to select the database you created for your application, then click Next until you can Finish.

Before you finish, you will see a summary screen. Use the "Test Data Source" button to verify that your settings work. If the test succeeds, click OK through to complete the setup. Your DSN will now show in the list. Select it and move forward.

Access then presents a list of tables and views it can see on the SQL Server. You will notice many system tables beginning with "sys" - do not link to these. Find your application tables, which are usually prefixed with "dbo," such as "dbo_CustomerT." Select the tables you want to link.

If your SQL Server tables use the new DateTime2 data type, Access may prompt you to enable linking with DateTime2 fields. DateTime2 is SQL Server's modern high-precision date and time type, and Access supports it through something called DateTime Extended. If you want to retain full compatibility and precision for milliseconds, click Yes when prompted unless you need to maintain compatibility with Access 2013 or earlier. If older Access support is needed, click No, but be aware that fractional seconds will be lost.

After this, your linked tables will appear in your Access database with special icons indicating their linked status. Their names will typically start with "dbo_" followed by the table name. You might be tempted to rename these, but it is helpful to leave them as is. The "dbo" prefix serves as a reminder these point to SQL Server and keeps you aware when writing VBA or designing queries. It is possible to hide or rename table names for clarity, but that is up to your workflow and future maintenance needs.

Now you can test the live connection. Open the linked table in Access and edit a record. Move off the record to save it, then switch to SQL Server Management Studio (SSMS) to view the same table - you should see your changes in real time. This confirms that Access is now talking directly to SQL Server and that your setup works as intended.

A few additions and clarifications: Access displays SQL Server's DateTime2 fields as DateTime Extended, which is not exactly the same but provides compatibility for high-precision times. Regular Access DateTime fields store some extra fractions of a second internally, though not at true millisecond accuracy. By using DateTime2 with a precision of three in SQL Server, you avoid potential truncation or rounding issues when Access and SQL Server exchange data.

If you ever need to manage or remove your DSNs, you can open the ODBC Data Source Administrator tool in Windows ("ODBC Data Sources" in your Start menu). File DSNs are typically saved in your Documents folder by default. Once you have completed linking your tables in Access, the DSN information is baked into the Access file, so you can actually delete the DSN file unless you need to connect again from another workstation.

Now that your SQL Server tables are linked, you can build Queries, Forms, and Reports in Access just as you would with any Access backend. Under the hood, though, you are working with live data on SQL Server, which means your users gain the benefits of SQL Server's performance, security, and data integrity.

Finally, if you ever run into connection issues, double-check your server and instance name, the driver version, and your authentication method. For named instances such as SQL Express, use the "MachineName\\InstanceName" syntax if "MachineName" alone does not work.

In summary, you have learned how to link Microsoft Access to SQL Server so your Access front end works directly with up-to-date SQL data. This approach gives you a powerful hybrid setup: Access for rapid interface building and SQL Server for robust data management. Experiment with your forms and queries and see how it works. If you have questions about DSN, linking, or compatibility, review the ODBC settings or leave a comment for further explanation.

Navigation

Links

Keywords