Microsoft Access and SQL Server Integration

By:   |   Comments (19)   |   Related: > Microsoft Access Integration


Problem

We have a few SQL sandbox databases set up in our environment that allow our developers and some key end-users the ability to do some temporary development without expectations of performance or durability. Quite often these individuals will want to use Access as their front-end GUI tool for these databases. Realizing that this solution exposed the Database Administrator to endless phone calls asking for assistance, I developed a document that anyone could follow in order to get an SQL-via-Access implementation up and running on their own. This is the first of four tips focusing on connecting Microsoft Access to a SQL database.

The tips in this series are:

  • Create an ODBC Data Source Name (DSN) using the standard SQL Server client.
  • Create an ODBC Data Source Name (DSN) using the SQL Native Client (SNAC).
  • Linking an Access table to SQL Server
  • Creating a SQL Pass-Through query

This tip focuses exclusively on creating an ODBC Data Source Name (DSN) using the standard SQL Server client. This DSN will then be used to connect Access to SQL Server, allowing the end user the ability to create a form structure for presenting, querying, and reporting against SQL Server data; using the Microsoft Office suite of tools. These tools (Excel, Access, PowerPoint and Word) offer many of the same benefits for analyzing and presenting data that core programming languages and platforms such as C#, VB.net, and ASP do, but without the steep learning curve.

Solution

The initial step in creating any Data Source Name (DSN) is locating the Data Sources (ODBC) Control Panel icon. Navigate to the Administrative Tools via Start\Control Panel.

administrative tools

Selecting Data Sources (ODBC) will present you with the ODBC Data Source Administrator dialog box. There are considerable options for creating DSNs. Depending of the requirements of your environment you may choose to create a User, System, or File DSN. Let's take a closer look at each of the three DSN types in order to allow you to make an informed decision.

User DSN

A User DSN is, you guessed it, user-specific. It is only available to the user who created it.

System DSN

Unlike a User DSN, the System DSN is available to all users of the workstation or server on which it has been created. Both SQL Server and Internet Information Systems (IIS) require a System DSN. Most-likely this will be the only DSN you will ever create.

File DSN

A File DSN is similar to a System DSN, in that it is not user-specific. Unlike User and System DSNs, a File DSN is stored in a .dsn file, not the Windows registry. This file can be emailed, shared, and installed on systems other than the one it was created on. It is important to note however that File DSNs are not ODBC-compliant.

Now that you have a general understanding of why we'll be proceeding on with creating only System DSNs, let's do just that. Navigate to the System DSN tab of the ODBC Data Source Administrator and select the 'Add...' button.

data source

You'll now be presented with the option of selecting the driver you'll be utilizing for this DSN. Since this is MSSQLtips.com, we're focusing on the SQL Server drivers. The next tip in this series will examine the differences in configuring the SQL Native Client (SNAC) that first made it's appearance with SQL Server 2005. For the purpose of this tip, I'll be selecting the SQL Server driver that has been associated with all previous releases of Microsoft SQL Server. Select this driver and then click Finish. This is a poorly-captioned button, since we've only just begun...

create new data source

You'll now be prompted for the basic information about your new SQL Server System DSN. Enter a name and description for the DSN then either type the name of the SQL instance you'll be connecting to or select it from the instances available in the list. Two important things to remember when entering values on this dialog: you will be referring to the DSN name when utilizing Access, Excel, or other application that will be connecting to the database. Be sure that your name is not too cryptic and that it conforms to any naming conventions used in your environment. In regards to the selection of a target Server, remember that this value should be the SQL instance you desire to connect to, not the server itself. If you wish to connect to the SQL1 instance on ServerX, the value you would enter into the Server field on this form would be ServerX\SQL1. Enter the values required for your environment and click Next to continue.

sql server

At this point you are now going to be prompted to configure the security for the DSN. You should be familiar with the two options available: Windows NT authentication or SQL Server authentication. Windows authentication will pass the context of the current Win O/S login to the database, whereas SQL security uses a login/password model where both are stored as objects in the master database of the identified SQL instance. Below you will see both options presented. In either case I recommend selecting the check box authorizing a connection to the SQL instance to collect metadata about the login being passed within the DSN. This will allow you to determine if the user context of the DSN will be compatible with the security set up for this user within the SQL instance. It is my recommendation that you set up security in the SQL instance first, before creating any associated DSNs.

new data source
sql server

You'll note that when working with the Windows method of authentication, you are unable to enter a login and password. This is specifically due to the fact that the login passed to the database for authentication will be dependent upon what login is active on the workstation/server at the time of use. Later in the DSN creation process you'll be able to "test" the DSN. During this test, when using Windows security mode, the test will be performed with your active network login as the active user of the system at the time. Select Next to proceed.

Select the database you want to associate with this DSN. All databases that the user has any level of rights to is listed in the database combo box. If you do not see the database in this list it is because:

  • If using Windows security, you as the DSN creator, do not have rights to the database
  • If using SQL security, either the login\password combination is incorrect or the login does not have database rights.

After selecting the database, determine the connection-level settings for quoted identifiers and ANSI nulls, padding, and warnings the select Next.

data source

On this form I strongly recommend that you leave all defaults in place and continue. There may be reasons to deviate from the defaults but those would most-likely be limited to situations where you're serving data out globally to users that have different language or currency formats, or if you are not utilizing encryption schemes available to the relational database management system (RDBMS). Nine times out of ten, simply select Finish and move on.

sql server

I mentioned earlier that you would have the chance to test your DSN and this is the time. Select the 'Test Data Source...' button on the bottom left. The DSN settings are displayed below prior to proceeding with testing of both a Windows security and a SQL security based DSN.

sql server setup
setup

If, for some reason your DSN test fails I suggest checking the following possible issues:

  • The login\password does not match the login\password combination on the SQL instance you're connecting to in the DSN
  • The login specified does not have rights to the SQL instance or database
  • The SQL instance is not online or is not accepting connections for another reason
  • Networking issues between the workstation or server where you're creating the DSN and the server hosting the SQL instance

Once you work out any possible configuration issues, you should receive results similar to the following screen.

data source test

Clicking OK will then return you to the ODBC Data Source Administrator dialog. If, at a later date you need to edit the DSN you just created, you would do so by selecting the DSN and then clicking the 'Configure...' button on the right side of the interface.

source administrator
Next Steps
  • Stay tuned for subsequent tips on creating SQL Native Client (SNAC) DSNs, linking Access objects to SQL database objects, and creating SQL Pass-Through Queries in Microsoft Access.
  • Learn more about Named Instances at MSSQLtips.com
  • Create your own DSN and configure it with different settings. What happens when you attempt to configure a DSN with incorrect security?


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, May 29, 2017 - 8:48:40 AM - Bill Back To Top (56189)

Thank you for the article.  I am new to using SQL server with MS Access and I am having trouble deploying my runtime version to other computers.  I have the local computer working well with the SQL database and I am able to setup an ODBC DNS connection on other computers on the network but when I try to run the MS Access frontend I get this error:

 

 Error 3151: ODBC - connection to "DATA" failed.

 

I have the System DNS setup on the local computer and the networked computer with the exact same name and settings using SQL server authentication and they both pass test like you show in the article.  When I linked all the tables I used SQL server authentication and selected save password within MS Access.  Is there something more I need to consider when deploying a runtime version and setting up a connection from a networked computer?  Thank you.

 

Friday, April 21, 2017 - 11:16:48 AM - bernie black Back To Top (55082)

 Nice tip.  Cleared up some stuff for me. Thanks

 

Sunday, January 15, 2017 - 11:22:26 AM - Greg Robidoux Back To Top (45333)

Actually, here is more info.  Check this out to see if this helps: http://dba.stackexchange.com/questions/2910/can-a-username-and-password-be-hard-coded-in-a-system-dsn

 

Sunday, January 15, 2017 - 11:21:08 AM - Greg Robidoux Back To Top (45332)

Hi Victor,

did you create a USER DSN or a SYSTEM DSN?  If you are using a USER DSN try a SYSTEM DSN to see if that works.

-Greg

Sunday, January 15, 2017 - 11:14:47 AM - victor Back To Top (45331)

Sir

 

I have this concern i am using the sql server 2008 when i created a dsn and se its userid =sa and password correctly.. when i run my application on visual foxpro it will ask a prompt.. my question is how will i make it to save it .. never prompt anymore since i configured it on DSN

I thought that when i created the DSN ODBC SQL SERVER.. And used on application, then why it when running the program.. it will prompt me a windows to ask for trust login and userid of password

I am very problematic on this.. please help

Thank you

 

 victor

 

Monday, November 23, 2015 - 6:50:11 AM - Rodrigo Back To Top (39122)


Thank you for the above article, however I am still trying to fix an issue related to databases in MS Access.

One of my users ha his PC reinstalled an migrated from an old domain to a new domain. Now he is trying to use the same Access database.

He can load MS Access 2010 and the access file database (tables/linked tables), but when trying to open a linked table he gets the following errors:

- "ODBC--connection to 'servername' failed", clicking in 'help' button it opens a help window with more details about the Error 3151.

- Followed by the message "Microsoft Access can't open the table in Datasheet view"

I have tried many thins so far, because I am not a database expertise and never workedwith MS Access I am struggling to find a possible solution for this.

Thursday, August 14, 2014 - 2:21:48 PM - Sydney Back To Top (34141)

New to Access and SQL Server.  Just created the System DSN per your article.  Tests Successful.  However when I try to link to SQL Server, using Access External Data -> OBDC Database, I get an error message.  "Unable to access file. Please verify that the correct path was given.".

I do not know what is the path for the file.  I thought I had followed all the steps (particularly since the Tests were successful) but I ovviously did not.  Please let me know what I missed.

FYI

I found your article after not getting the expected results with the File DSN that I had created.  I was able to link to SQL Server using the File DSN, but could only see the System files not the AdventureWorks Tables.

Wednesday, July 9, 2014 - 4:04:32 AM - madhu Back To Top (32598)

What about connections to data tables on non-MS SQL servers?

Friday, August 23, 2013 - 2:30:22 PM - Fred Back To Top (26464)

Hi: I just finished with the first article and am ready to set up my SNAC. Can you tell me where the 2, 3, and 4 articles are? This has been great. Very helpful.

Thanks

Fred

Thursday, May 2, 2013 - 4:19:07 AM - Ruel Ebio Back To Top (23679)

Thanks Tim

I been planning to migrate to a domain , i have a test domain network what i do is i log in to local admin of cliet/XP pc then i follow the step you teach using system dsn

is their a way where i can put the connection centrallize,like Active Derictory or script  so i dont have to configure every computer locally then back to domain user?

Thanks in advance

Tuesday, March 5, 2013 - 9:56:21 AM - Fauzan Back To Top (22574)

 

Hi,

I have a link table in access which is i get it from IBM Informix database. The link table in microsoft access and i create a query. How can i link all the link table in microsoft access to sql server ? because i prefer to use sql server then access.

 

Thanks.

Thursday, February 21, 2013 - 3:28:03 PM - Vilas Back To Top (22346)

*** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting

 

The setup works perfect and is visible under SYS DSN. However, when access is attempted thru MS-EXCEL, does not appear in CHOOSE DATA SOURCE. This happens only on LAPTOP. Link is working on desktop.

Saturday, June 16, 2012 - 2:01:34 PM - david Back To Top (18071)

What about connections to data tables on non-MS SQL servers?

Tuesday, October 20, 2009 - 6:38:11 PM - Rx_ Back To Top (4267)

Wondered if there is a new ODBC file or driver update for SQL Server 2008? 

Love the article, have been down this road thousands of times. But, what happens when the Linked Table Manager reports that it has refreshed all the tables, but it really didn't refresh them?  When refreshing all - the Access somehow keeps all the old ODBC link information. When choosing "Always Prompt for New Location" it demands that each and every table go through the process of choosing the System DSN tab and then the ODBC Database Name. When not choosing the "Always Prompt for New Location", it reports "all tables are relinked" but it really kept the same old ODBC server link.

If I go through the process for an individual table (Always prompt for New Location), it does re-link the table.

Have a great Access 2003 mde application that uses a couple of hundred tables linked to SQL Server 2005 using linked tables (Pass Through, update queries and the like).

Recently on a Windows 2003 server, set up an instance of SQL Server 2008 to test the SQL Server update process.

My MS Access DB links to 4 different SQL Server databases. Set up a new workstation to document the process. So, I know the odbc information is in the Access 2003 table.

Monday, October 6, 2008 - 7:32:56 AM - tbpowers Back To Top (1921)

Did you ever find an answer to your question? We are running into the same issue. Even though we chose SQL Server authentication during setup, it defaults to the trusted connection and we have to uncheck and re-enter the user name and password.

Tuesday, May 13, 2008 - 6:57:34 PM - timmer26 Back To Top (983)

Very interesting behavior going on in your environment.  Can you confirm that you're using a System DSN and that it resides on the Citrix server where the Access .mdb resides?

- Tim

Monday, May 12, 2008 - 6:30:56 AM - KSS Back To Top (973)

Thank you for such a nice tip. I would rather say an article. Now the issue: We are running an SQL Server in a large organization in a small section. One User account (Lets Call is USER9999) was created on the server and the front-end application developed in MS Access 2003 is deployed on a Citrix Server. Users can log on to the Citrix Server and access this application through their Citrix Server account managed by a domain controller. The application residing on the Citrix Server connects to the SQL Serevr using the above mentioned account USER9999 which we created on the SQL server and is NOT a trusted account. Time and again users complain of not being able to execute the ODBC of this front-end application in MS Access 2003 since the machine on which this single application is installed tries to access the SQL Server using "Trusted  User Account". When this happens, we have to connect to the application and refresh the ODBC and Tables with "Linked Table Manager". In all these instances the culprit was the "bad" ODBC which was trying to access the SQL Server through Trusted Domain User Account NOT the SQL Server Account (as this option was checked all the time). My question is: Is there any way that we can create an ODBC and "engrave" in it to use SQL Server Account and NOT the Trusted account? The SQL Sever is the member of the domain and running a small database with few users.

I will appreciate any comment on this issue,

 Thanks

Friday, April 4, 2008 - 4:59:19 PM - admin Back To Top (833)

jrea8830,

Thank you for the insight and feedback.

You are correct, this is another good tip from Tim Ford.

Thank you,
The MSSQLTips.com Team

Friday, April 4, 2008 - 12:03:57 PM - jrea8830 Back To Top (832)

Great article; I've been up and down this road many times in the past year and can vouch that you are hitting the connection using DSN right on the head!

It'd be worth while to note that if for some reason the reader can't find "Data Sources (ODBC)" in their Administrative Tools; to also check in your Control Panel (This is very typical in Pre-XP operating systems).

Also, if you still can't find it don't fret, you can usually go into your Windows Components (accessed through the 'Add/Remove Programs' component), and choose to add it in to your Control Panel or Administrative Tools. The option to select it might be a few levels in to the components, so click on 'Details' if the option is there. Have your Windows OS disc ready; sometimes needed to get the files installed. 

Still, can't find it? Well, unfortunately you can't just download the ODBC control by itself, you will have to download Microsoft Data Access Control (MDAC). Be careful to choose the correct version, some versions are not compatible with older Access versions...such as Access 97 doesn't work reliably with MDAC 2.7, and is suggested to use MDAC 2.6 instead. You can get good troubleshooting through Microsoft's Support Knowledge Base (KB)...Do a search on MDAC or ODBC...here's a link to an informative KB for MDAC 2.7 (Article ID # 313008). It's a great starting point, that includes link for setup troubleshooting and getting to older MDAC versions. http://support.microsoft.com/kb/313008/en-us

Keep up the great series of tips for this somewhat confusing area!














get free sql tips
agree to terms