Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Configure Microsoft Access Linked Tables with a SQL Server Database

MSSQLTips author Tim Ford By:   |   Read Comments (29)   |   Related Tips: More > 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:

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.

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 Integration and 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 ConfigurationOnce you have either created or identified the proper SQL Server DSN, highlight it and click the 'OK' button.

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.

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. 

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.

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.

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.


Last Update: 4/18/2008


About the author
MSSQLTips author Tim Ford
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Friday, April 18, 2008 - 10:36:14 AM - BridgetElise Read The Tip

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.


Friday, April 18, 2008 - 11:19:04 AM - jrea8830 Read The Tip

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 - 11:33:02 AM - BridgetElise Read The Tip

ADP started in Access 2000.


Friday, April 18, 2008 - 5:42:00 PM - timmer26 Read The Tip

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 25, 2008 - 8:10:54 PM - timmer26 Read The Tip

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


Wednesday, May 14, 2008 - 9:05:53 AM - pgrealy Read The Tip

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


Wednesday, May 14, 2008 - 9:25:21 AM - timmer26 Read The Tip

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.


Monday, August 04, 2008 - 11:15:24 AM - codexpert71 Read The Tip

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, August 13, 2008 - 9:08:48 AM - Ackim Read The Tip

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

Thanks,

  


Thursday, August 14, 2008 - 9:13:52 AM - pgrealy Read The Tip

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.


Thursday, August 14, 2008 - 9:58:49 AM - codexpert71 Read The Tip

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

 


Tuesday, August 19, 2008 - 2:13:27 PM - angelgirl Read The Tip

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!


Wednesday, September 03, 2008 - 5:57:32 AM - bkuhns Read The Tip

 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, November 18, 2008 - 10:25:36 AM - stingaway Read The Tip

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


Friday, March 26, 2010 - 1:21:48 PM - kdugger Read The Tip

 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?

 


Friday, March 26, 2010 - 1:51:42 PM - BridgetElise Read The Tip

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.


Thursday, April 01, 2010 - 1:36:04 PM - kdugger Read The Tip

 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?


Thursday, April 01, 2010 - 2:05:22 PM - BridgetElise Read The Tip

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 01, 2010 - 5:46:02 PM - kdugger Read The Tip

 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?


Friday, April 02, 2010 - 6:56:03 AM - BridgetElise Read The Tip

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.


Tuesday, April 06, 2010 - 6:07:24 PM - kdugger Read The Tip

 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.


Saturday, June 30, 2012 - 1:09:21 PM - shikher Read The Tip

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.....


Monday, September 03, 2012 - 5:56:27 AM - munafmunan@yahoo.co.in Read The Tip

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

 

 

 


Friday, April 05, 2013 - 8:17:16 AM - sathish naidu Read The Tip

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


Thursday, June 13, 2013 - 11:26:27 AM - Rx Read The Tip

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.

 


Saturday, June 15, 2013 - 8:38:19 AM - Joe Read The Tip

 

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?


Monday, June 17, 2013 - 11:48:16 AM - Rx Read The Tip

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. 


Monday, January 20, 2014 - 9:39:38 AM - Shahbaz Read The Tip

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.


Tuesday, June 03, 2014 - 11:44:55 AM - Jim Read The Tip

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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.