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 Configuration. Once 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.
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
Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.
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
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 08, 2015 - 11:37:15 AM - Greg Robidoux
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!
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..?
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?
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.
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?
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 05, 2013 - 8:17:16 AM - sathish naidu
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 03, 2012 - 5:56:27 AM - email@example.com
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.
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.
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.
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!
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
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?
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.
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.
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
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
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.