Configure Microsoft Access Linked Tables with a SQL Server Database

By:   |   Updated: 2008-04-18   |   Comments (41)   |   Related: > Microsoft Access Integration


Problem

Thus far in this tips series on Access and SQL Server, we have created an ODBC Data Source Name (DSN) using the OLEDB driver for connecting to SQL Server 2000, as well as a System DSN to connect to a SQL Server 2005 instance using the new SNAC (SQL Native Client) driver. This was in preparation for using Microsoft Access as a front-end tool for a SQL Server 2005 database. Though Access can be considered a sub-par application by the relational database elitists, it can be a very capable alternative for querying data from SQL Server without the learning curve associated with Visual Studio. In some respects, it is the perfect tool for the casual business user who wants to drill into and share their data. So what's next?

Solution

In order to connect to Microsoft SQL Server from Microsoft Access you need to set up at least one Linked Table. This table is a pointer to a table in a SQL Server database that is associated with a pre-defined System ODBC Data Source Name (referred to as a DSN from this point forward). In truth, you have many options for setting up Linked Tables from Access; many RDBMSs (Relational Database Management Systems) and Microsoft Office applications for example - even SharePoint and Outlook are options for linking tables back to Microsoft Access. For the purpose of this tip however, we're going to focus exclusively on Microsoft SQL Server. The process from this point forward is compatible with all releases of SQL Server since version 7.0.

The first step in setting up a linked table is to navigate to the Linked Tables dialog in Access. From the menu bar, select File | Get External Data | Linked Tables as shown below:

1

By default, Microsoft Access will want to link to another Access database for it's Linked Table source. Browsing the 'Files of Type' pull-down menu on the bottom left of the interface will give you a general idea of just how broad of a scope is available for Linked Table sources. Since we are focusing on SQL Server as a data source, navigate down the list of 'Files of Type' options and select the 'ODBC Databases()' option. Then click the 'Link' button on the right of the interface.

2
3

After pressing the 'Link' button, you will be greeted with a dialog for selecting the DSN for linking to Access. If you have been following along on the previous tips in this series (Microsoft Access and SQL Server Integrationand SQL Server SNAC Creation and Configuration) you will see a DSN that has been set up for a SQL Server 2005 database of your choosing. If not, you can create a new one now by clicking the 'New' button on the bottom right of the interface and following the procedures in SQL Server SNAC Creation and Configuration. Once you have either created or identified the proper SQL Server DSN, highlight it and click the 'OK' button.

4

When creating the DSN for this process, you configured it to use either SQL Server or Windows (Trusted) security. You will be prompted again at this point when attempting to make use of the DSN. Select the appropriate security measures to authenticate to the data source referenced in the DSN and click the 'OK' button.

5

If you have rights to the database associated with the DSN you will be presented with a listing of database objects (note that you are not limited to linking just to tables, but rather views and stored procedures are available options also). We are going to focus strictly on linking to SQL Server tables for this exercise. I am using Northwind as a database source in this tip, so you will see below that I have selected all tables except for the date-specific table 'Category Sales for 1997' in the tables listing on the interface.

6

Once selecting the 'OK' button on the previous screen you will be prompted to select a 'Unique Record Identifier' for each table. In a nutshell, you are being asked for the primary key for the table. If you are privy to this information please select the correct field because performance depends on it. If performance is not an issue, you may simply press the Cancel button throughout this process. The table linking will still complete successfully whether you set a Primary Key or not.

7

Once you have made your way through the previous step you will see a table listing similar to what is below. Make note that Microsoft Access' naming convention process will concatenate the RDBMS schema owner and RDBMS object name in the format of <schema>_<object_name>. You can rename the table in Access after linking and the link will not be broken. However, there will be some minor ramifications in doing so when it comes to using the Microsoft Linked Table Manager which will be highlighted in a future tip.

8

At this point you may utilize the linked tables as though they are native Access objects. You can create queries against them, report off of them, use them as the record source for Access forms and run Visual Basic for Applications (VBA) code against them.

Next Steps
  • Review the other tips in this series on SQL Server interaction with Microsoft Access.
  • Stay tuned for subsequent tips on creating SQL Pass-Through Queries in Microsoft Access and advanced usage of SQL record sources with Microsoft Access.
  • Link one of your existing SQL databases to Access using the process outlined in this tip. Run some queries against it. What does performance look like? Be prepared to answer that question again once we build a pass-through query in our next tip.


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


Article Last Updated: 2008-04-18

Comments For This Article




Tuesday, February 23, 2021 - 1:14:26 PM - Greg Robidoux Back To Top (88279)
Hi Engr. Matthew Ikechukw,

Change DATE() to GETDATE()

Tuesday, February 23, 2021 - 1:09:09 PM - Engr. Matthew Ikechukw Back To Top (88278)
strQuery = "SELECT tblAppointments.AppID, tblAppointments.AppointDate, tblAppointments.AppointTime, Left([tblSchedule].[DoctorsName],5) AS Doctor, tblSchedule.DoctorsName" _
& "FROM tblSchedule INNER JOIN tblAppointments ON tblSchedule.DoctorsID = tblAppointments.DoctorsID " _
& "WHERE (((tblAppointments.AppointDate)>=Date()));"

This SQL String Listed above works perfect on Access 2016. If use it to strip 5 letters of a doctor and the time the doctor has an appointment with a patient. The information is displayed on a calendar box of the date.
Please, can you have me take a look at the code and tell me where I missed it.

Thanks

Now, the string works very but after I link the table to SQL 2014 Express, the code did not work

Wednesday, October 4, 2017 - 5:53:27 PM - Dibs Back To Top (66907)

 

Hi Mr Tim,

 

Please we have a situation where some linked ODBC tables in Ms access where created from a different DB. Now we have a need to reconfigure those Linked tables to connect to a SQL Server. The tables are similar but the Linked tables in Access has the schema name preceding it which isn't the same schema name on SQL server. Please whats the best way to do this change? Also all the queries

Monday, September 11, 2017 - 8:55:53 AM - Hetal Back To Top (66161)

 I want a soltion for connection establish in module of Microsoft Access 2010 for MS SQL

provide code 

 

Tuesday, November 17, 2015 - 10:43:38 AM - Dik van der Zwan Back To Top (39086)

Situation:
SQL server express 2014 as backend for a ms Access 2010 frontend.
Both SQL Server and Access installed on the same machine.
Tables are linked (ODBC, SQL Server Native Client 11.0)
The biggest table (till now): 205 rows, 44 columns.

Problem:
Dubble click on tablelink gives a first response after 32 sec.

Using a Pass Through-query gives similar results.
Examening above table with SQL Server Management Studio: < 1 sec.

Any suggestios on how to solve this problem please??

Wednesday, May 27, 2015 - 12:49:07 PM - Robert Hunter Back To Top (37291)

Followed steps to link to SQL12 database, selected tables are visible in access13 but all fields are filled with #Deleted.  Data is present in the table when viewed in SSMS.  Suggestions?

Tuesday, April 28, 2015 - 2:53:33 PM - saurabh Back To Top (37054)

I wanna change APP=Microsoft Office 2010 to 2007, is this doable ?

 

 

Thursday, April 16, 2015 - 5:40:04 PM - Mike Keast Back To Top (36955)

I am using the linked tables, however if I add a new table in SQL and link it to the access application, everytime I try to access that new table access asks for the login info for SQL again.  How do I get it to use the same info the rest of the application is using and stop asking for login info?

 

thanks for any help

Thursday, March 19, 2015 - 3:19:01 PM - Don Morgan Back To Top (36593)

Mr Ford, thank you in advance for assistance. I have reviewed an exercised 'created' the suggesction on your site. 'Pass Through Queries' I'm starting with the access frontend and accessing the backend 'data' in SQL Server 2008 R2. The data in SQL dbo.Client and when looking at the table while processing it's dbo_Client. When the pass thru is created and based on the instructions 'changing from dbo_ to dbo.' the pass thru query cannot find the dbo.Client when trying to preview client info. I guess its not looking at the SQL Server table?? I must have missed a step. I have two queries to create coming from a form thats part of the query preview and the second preview uses two tables also coming from a form. Thanks, Don

Thursday, January 8, 2015 - 11:37:15 AM - Greg Robidoux Back To Top (35870)

Hi Matthew, does the SQL Server table have a primary key?

Thursday, January 8, 2015 - 11:30:36 AM - Matthew Ikechukwu Back To Top (35869)

I am having a problem update a record on a table after moving the data to SQL Server. Of course, some of the codes will not be compactable with the back-end database. I used the following listed below to change the values of the two field

Me.CustomerClaim_Subform.Form.Controls("Status") = "Package Collected"
Me.CustomerClaim_Subform.Form.Controls("DateofCollection") = Date

Now, when click the command to to change the vaslue of these two fields I got a message saying this record is not updatable!

Me.CustomerClaim_Subform.Form.Controls("Status") = "Package Collected"

I have try to work around the problem, unfortunately couldn't get the right here is the code I try to build

'------------------------

 

Private Sub cmddelivered_Click()
Dim IntResponse As Integer
Dim strConnection As String
Dim strLinkedTableName As String
Dim sql As String
Dim rs As DAO.Recordset

If IsNull([ReceiverID]) Then
MsgBox "Please Type in the Customer Tracking Code and try again", vbCritical, "Package Management System"

Exit Sub

ElseIf Me.CustomerClaim_Subform.Form.Controls("Status") = "Package Collected" Then
MsgBox "This Package is already Collected by the Owner", vbInformation, "Package Management system"
Exit Sub

Else
IntResponse = MsgBox("Are you sure you want to deliver this package to the Owner?", vbYesNo, "Package Management System")
If IntResponse = vbYes Then

strLinkedTableName = "MainPackages"

sql = "Select * From MainPackages where Status = 'Arrival'"

Set rs = CurrentDb.OpenRecordset(sql)

With rs
    If Not .BOF And Not .EOF Then
   
    .MoveLast
    .MoveFirst
If .Updatable Then
    .Edit

.Fields("Status") = "Package Collected"
.Fields("DateofCollection") = Date

.Update
End If
    End If
.Close
End With

Set rs = Nothing


Me.CustomerClaim_Subform.Requery

'Me.CustomerClaim_Subform.Form.Controls("DateofCollection") = Date


End If
End If
'End If
End Sub
'--------------------------------------

Please, I need a help to solve this problem thank you

Matthew

 

Tuesday, September 30, 2014 - 2:35:25 PM - Victoria Stewart Back To Top (34779)

Hi Tim, 

 

I am developing an access tool for my clients, but I been having a problem trying to find an offline solution. Due to my client location, internet is on and off all the time, they need to enter data everyday. It seems that there is no many solutions out there. I had already an access app ready for them and tried to link it to an ms access desktop but odbc does not work off ine. So my question is: Is there any way for this info to be stored in the computer and updated to the server once the internet is back? like sharepoint list..?

 

Thank you for your advice!!

 

Tuesday, June 3, 2014 - 11:44:55 AM - Jim Back To Top (32060)

Tim,

If I have an existing table that has FK relationships to other tables and used in forms/reports/queries all over the place.  Is there a way to 'replace' the MS Access table with a Linked table using the same name?

Thanks,

Jim

Monday, January 20, 2014 - 9:39:38 AM - Shahbaz Back To Top (28144)

Hi,

Can we access and perform the SQL statements like insert and update on the linked table (dbo_tablename) present in MS Access through the Oracle SQL Developer. If yes then How? it require any extra effort. Please help.

I know we can access the normal MS Access table and can perform the sql statements through SQL Developer.

Please let me know if there are any questions.

 

Thanks in advance.

Monday, June 17, 2013 - 11:48:16 AM - Rx Back To Top (25456)

SQL Server Migration Wizard for MS Access (a free MS tool)  see post above is a great way to get started.

http://www.access-programmers.co.uk/forums  This is a frequent subject on this forum. 

This MSSQLtips  site is also fantastic to get you started . It is on my Favorites

Would personally suggest using the SQL Server Native Client driver with scripts for a DSN-Less linked tables. 

Needless to say, you will want to look at a Split Database (front-end linked to back-end DB).

MS Access is a fantastic way to upload data. You are on the right track. 

Saturday, June 15, 2013 - 8:38:19 AM - Joe Back To Top (25442)

 

I am working in a simple MS Access 2010 software recently developed for a specific purpose. We are a small group of 10 people. We have a Share Drive "Q". I pasted MS Access Software in this Q Drive for multi user access. The command buttons, in my Access 2010 Forms, show different shapes when this software is accessed by 2 or 3 users.  No error messages. Our group want to use my SearchForm in my Access 2010 software, for search operations. My job to upload data.

I never used MS SQLserver. Is it easy to work  with it, if I have experience MS Access?

Can we use MS SQLserver instead & download this Forms & Tables from my MS Access 2010 software, for multiuser setup?  will it work?

 

 

Should I explain more?

Thursday, June 13, 2013 - 11:26:27 AM - Rx Back To Top (25420)

Great articles. Placing them in my internet favorites.  My preference is to use MS SQL Server Native Client 11.0 with DSN-Less table connection.

A local access table hold a column with the SQL Server table name (or SQL Server View name) to link and a checkbox column on if to indicate if it should be linked or not. Using code, all the  linked tables can be destroyed, then those that are checked are re-established. This way, a application can quickly be connected to a Production SQL Server DB, a Test SQL Server DB and other DB.

Using the SQL Server Migration Wizard for MS Access (a free MS tool) the data type conversions have worked very well.
The Front-End database is hosted on a Citrix Server. As each user logs in, a copy of the Front-end is created in the user's folder on the Citrix server. This allows users nationally to enjoy Cloud style rich front-end applications on extremelly low bandwidth. It also adds another level of security. End users with almost any platform (including Mac) can use the Access application. If there is a Citrix client for the end-user's computer, the application can be used.

SQL Server Native Client 10.0 / 11.0 translate all Access 2010 queries extremely efficiently. For about 80%, there is no need to develope Pass-Through Queries. It should be noted that the Access Query should not use any wild cards or Access specific functions (e.g. Iff statement or Conversion)

For reports, Access Reports don't work well with Citrix. Using Microsoft Excel Object Model code allows the user to run a custom report and save it on the network. Each Excel report is saved in the user's network folder. Users often prefer Excel as an output.

Thanks to excellent articles like yours, many Access programmers are moving to SQL Server. It is a great match with many rewards. Please continue publishing these types of articles.

 

Friday, April 5, 2013 - 8:17:16 AM - sathish naidu Back To Top (23182)

The linking with sql server and MS Access is working good. But when i want to open the linked table in MS Access it asking every time user id and password. so i can't use the Linked tables in my program. 

please any one help

 

Thanks in Advance

Monday, September 3, 2012 - 5:56:27 AM - [email protected] Back To Top (19371)

Sir  if I am inserting data in a table then it should be fell or post the data in others tables means I am having 

three tables   a,  b,  c,   so  I am inserting data in   a  table  but same data should be fell in other b & c table.

so what it createing table system is here  or what should i do for to the felling data in other table 

please send to my munnan@mail address answers sir thanking you

Waiting for you answer

 

 

 

Saturday, June 30, 2012 - 1:09:21 PM - shikher Back To Top (18275)

I liked the content of linking the tables . currently im facing 1 problem , actually I want to automate the process of refreshinh the linked tables but everytime i connect to my link table using vb code i get a password window and i have to enter it manually. so i want that this work should be done by vb code so that i can fully automate this process.

please guide me on this.....

Tuesday, April 6, 2010 - 6:07:24 PM - kdugger Back To Top (5193)

 Okay.  Thanks for the information.  Unfortunately, this db is part of a software package that I won't  be able to change.  Was just hoping there were additional drivers or something to get around it.


Friday, April 2, 2010 - 6:56:03 AM - BridgetElise Back To Top (5166)

The only way you can get this translate properly is change the data type on SQL Server from bigint to integer.  You probably should never have to use the bigint data type because integer goes up to 2,147,483,647.

Thursday, April 1, 2010 - 5:46:02 PM - kdugger Back To Top (5162)

 Okay.  I can see bigint on one of the fields in SQL Server Management Studio.  Is there a way to get this to transalte properly in Access?


Thursday, April 1, 2010 - 2:05:22 PM - BridgetElise Back To Top (5161)

You would have to see the data on SQL Server 2005.  You need to have SQL Server Management Studio install and have access to look at the database and see the tables field descriptions.  You can not see it in Microsoft Access.

Thursday, April 1, 2010 - 1:36:04 PM - kdugger Back To Top (5160)

 ok.  I'm not a sql expert.  How can I see what data type is being used and is there a way to make it work, with or without changing the data type?  Any driver updates or anything like that?


Friday, March 26, 2010 - 1:51:42 PM - BridgetElise Back To Top (5124)

You are probably using big int as data type in SQL Server or so data type that does not translates properly between SQL Server and MS Access.

Friday, March 26, 2010 - 1:21:48 PM - kdugger Back To Top (5123)

 Can someone tell me why I get #Deleted on some linked tables when I try to view it in Access, using the linked tables option connecting to SQL server 2005 via ODBC?

 

Tuesday, November 18, 2008 - 10:25:36 AM - stingaway Back To Top (2221)

Angelgirl -  You can share the database - but doing so will require a DSN on each machine.  


Wednesday, September 3, 2008 - 5:57:32 AM - bkuhns Back To Top (1733)

 I'm curious how this may work programmatically? Is it possible to implement this linking technique and access a .mdb file using ADO in C++ and all the SQL Server linking happens transparently? I'm just guessing that this is probably something handled on the frontend in Access and wouldn't be transparent on a programming level when communicating directly with the MDB file. Thanks for any insight!


Tuesday, August 19, 2008 - 2:13:27 PM - angelgirl Back To Top (1658)

I am wanting to link an SQL Server Database query view as a table in an Access database.  My question is, will it act like a regular Access table that can be shared or like an Excel linked table, which I've had problems sharing when multiple users are doing reports that are based on queries using the same tables.

My hope is that by linking it directly to the SQL database I can solve that problem.  Do you happen to know if that is the case?

PS - as you can tell, I'm new at the SQL thing, we've just been using the CSV files output by the database and I'm on the user side, not using direct queries on the database.  Thanks for any insight.

The articles and tips have been helpful, I'm going to keep searching.  Thanks!

Thursday, August 14, 2008 - 9:58:49 AM - codexpert71 Back To Top (1636)

I was able to obtain the owner by using the ODBC Driver {SQL Native Client}

 

Thursday, August 14, 2008 - 9:13:52 AM - pgrealy Back To Top (1635)

No, I never found a solution. The work-around that I use is to stay in the “External Data” tab of the Access 2007 ribbon if possible, rather than returning to the “Home” tab. – Pat G.

Wednesday, August 13, 2008 - 9:08:48 AM - Ackim Back To Top (1630)

Pat, i am running into a similar situation, were you able to find any acceptable work arounds to this issue.

Thanks,

  

Monday, August 4, 2008 - 11:15:24 AM - codexpert71 Back To Top (1553)

I am just curious...

I am using ADOX to create Linked Tables from a SQL 2005 database.  By default the ADOX Table object does not contain the owner (in your example dbo_).  I have a need to INCLUDE the owner in some cases.  Do you know of a way to make this happen using ADOX?

 

Wednesday, May 14, 2008 - 9:25:21 AM - timmer26 Back To Top (988)

I suspect that this is because Access will return all the values of the table into memory and needs to do conversion for each row in the table to get it from the SQL datetime format to the default date format for Access.  When it does this you'll get moved back to the first record.  Once you switch fields, it reconverts once again, pulling all the data into Access yet again (another delay.)

This is purely conjecture though, but makes sense with the behavior you're experiencing.

Wednesday, May 14, 2008 - 9:05:53 AM - pgrealy Back To Top (986)

Hi,

Our shop has used this extensively for a number of years, i.e., linking to SQL tables from MS-Access. I have recently noticed a problem and I am not sure how long it existed and/or if there is a fix.  Our environment is Windows XP(SP2), Office 2007/MS-Access 2007, SQL 2005 and SQL 2000. The error occurs when linked to either SQL 2005 or 2000.

 We have many large tables, 40,000+ records, and most have datetime fields, some with multiple datetime fields and these are the problem.When I create a new linked table I choose the External Data tab in the MS-Access "ribbon" at the top, I choose "More" under Import and go through the steps to link a table. After completing the link, if I open the table, still within the "External Data" ribbon tab, the table opens and I can focus on any cell, use arrows or tab to move quickly from field to field NO PROBLEM. However, if I select the "Home" tab from the top ribbon, the linked table remains open and I can focus into any cell and move using arrow keys or tabs, BUT, if I move into a datetime field, there is a long delay(30 secs - 5 minutes) before control returns. When it does, the total record count now appears in the bottom status bar("1 of 40,000" for example). If I move down the same datetime column there is no more delay. However, if I move into another datetime column, the delay recurs. It seems like the first time each datetime column receives focus it loops through each record doing something and it takes way longer than simply clicking the "Last Record" arrow in the bottom status bar.

 If I close the table and open again the same problem occurs where the initial focus to a datetime column results in long delays. This only occurs when the current ribbon tab is "Home". Pretty weird, huh?

I would appreciate it greatly if you can solve this problem but, whether you can or not, would you recommend another good forum for this type of SQL/Access question. Mostly I deal with ASP.Net stuff.

Thanks,

Pat Grealy

Friday, April 25, 2008 - 8:10:54 PM - timmer26 Back To Top (915)

James, the new tip addressing your question has been finished and should go out in the next week.

Friday, April 18, 2008 - 5:42:00 PM - timmer26 Back To Top (894)

James, you touch upon something I'll be covering in a tip at the end of this month, but there is a Linked Table Manager plugin within Microsoft Access you can use to update the linked tables without destroying them.  Since you asked, I'll make a point of using your specific situation in the tip as an example.

 Thanks for the feedback James.

Friday, April 18, 2008 - 11:33:02 AM - BridgetElise Back To Top (889)

ADP started in Access 2000.

Friday, April 18, 2008 - 11:19:04 AM - jrea8830 Back To Top (888)

Another great and accurate article. A question I have is in your article you stated to select the 'primary key' for the tables or 'cancel' if you don't know them; if you don't know them and later find them out how can you update the table to use the 'primary key'? Would you have to recreate the linked table or is there a method that can be used to just update the linked table?

 Thanks for the article and information; this is spot on especially in cases of using Access 97 on the front-end (where ADP isn't an option).

 Thanks,
James R.

Friday, April 18, 2008 - 10:36:14 AM - BridgetElise Back To Top (887)

I peferred to use the ole db thru Microsoft access adp (project) because you do not have to go each PC to created that ODBC connection or changed the server.  I thinked that ODBC is older way of doing thing. The adp of Microsoft Access is cleaner, faster, and do not have go to each PC.














get free sql tips
agree to terms