Are linked tables the best way to use SQL Server? Yes, no, maybe…

I’ve been seeing a lot of talk lately about linked SQL Server tables…ever since I’ve started this blog I’ve address it several times, but there have been some changes since my last diatribe that warrant revisiting the issue again.

ADVERTISING
SQL Server Hosting

Linked SQL Server Tables – Should you use them?

YES! Linked tables are a great way to leverage SQL Server in your app. In the old days some developers would use unbound forms and elaborate code to read/write to SQL Server, and for good reasons:

  • Networks were not as fast back then, remember token rings?
  • The ODBC stack was horrendous in the early days, a charm to work with now
  • Finally, Access was not as good in handling linked tables as it is now

Linked tables will allow you to bind your forms and make it easier to design and use them, run queries in Access and use lookup tables.

So what’s the catch? 

There are several issues you need to be aware of when using linked tables. The first one is NEVER use a combination of linked tables with local tables in your queries. Instead download the server side tables as temp tables in your app or upsize your local tables to SQL Server and do the processing on the server in it’s entirety.

Don’t do massive update queries using Access

If you ever need to update a lot of records at once, say, increase pricing on all items by 10%, you’re much better off doing the update via a pass-thru query or on the server directly.

Don’t load more data than you need to

I’ve bounded forms to tables with several million records with no problem, how? By using the where clause of the DoCmd.OpenForm command. Limiting data to just a fraction of records at any given time. Here’s how: Say you have a table of customers called tblCustomers and that the primary key is CustomerID, to load just one customer in your frmCustomers form you would use the following syntax:

Where lngCustomerID is a variable holding the CustomerID you wish to see.

Do design your SQL Server tables for optimum  performance with Access

Make sure you follow my guidelines for designing SQL Server tables with Access:

  • All tables need to have a TimeStamp or RowVersion field.
  • All dates should be in the Date/Time format
  • All bit fields need to have a default value of Zero
  • All text fields should be VarChar unless you need international characters, then use nVarChar
Beware the security issues
If you store your password with your linked tables you’re just asking for it if it falls into the wrong hands. Instead use Ben Clothier’s method when linking tables, or recreate and delete them when your app starts and quits. We recommend using Active Directory and trusted links as your best scenario.
2015-07-03T19:51:34+00:00

About the Author:

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

25 Comments

  1. John E Simpson September 19, 2018 at 10:29 am - Reply

    I’d be curious to know if this list is pretty much unchanged with, say, Access 2016 and newer versions of SQL Server (2016-17). Any experience with the newer versions to share?

  2. Argy Bargy September 28, 2017 at 4:01 pm - Reply

    Why not use a combination of linked tables with local tables in your queries?

    I see it in database solutions all of the time, and would really like to understand the downside to doing so.

    • oscar November 23, 2017 at 3:59 pm - Reply

      HI Argy,

      you may be working with a few rows, but for databases containing above >300k rows, the processing time takes almost one hour. and if you do everything in the server side it takes around 2 mins max.

      Reason: Access will download into memory the whole database ( on the sql server side ), and if it contains millions of rows… well….. after downloading the table. it will execute the query and return your rowset. hopefully was only a SELECT statement, because updates and inserts will follow the same logic above… it is terrible i can tell by experience, my boss decided to migrate some of the tables to the sql server, but not all…. now i am struggling every week waiting one hour to update the information ( i havent had the chance to improve the coding… im the only programmer here and i have also managment activities ).

  3. Ernesto December 28, 2015 at 10:28 pm - Reply

    Hello, I’m having problems with some update queries in access 2013, I used those queries in access 2010 and they used to run very fast, but now they never finish. Same queries, same database, same place, not in network. the only change is the access version.
    Any ideas?
    thanks in advance

  4. christ November 6, 2015 at 5:29 pm - Reply

    I’m having a problem with a linked SQL view in an access 2013 application.

    This view is used as the datasource for a form. It holds about 25.000 records.

    When I open the form sql server starts an async_network_io wait and closes this the moment I close the form.
    It also starts this async-network-io when I open the linked table in access, but closes this when I go to the last record.

    I would like to resolve this issue, and after searching high and low for a couple of days I still haven’t found a solution.

  5. dbennett2 June 29, 2013 at 12:59 pm - Reply

    It’s amazing to pay a quick visit this website and reading the views of all mates about this piece of writing, while I am also eager of getting familiarity.

  6. http://umoreinstabile.altervista.org/lo-zodiaco-degli-stronzi-cancro May 23, 2013 at 3:16 am - Reply

    I like the helpful information you provide in your articles.
    I will bookmark your weblog and check again here frequently.
    I’m quite sure I will learn many new stuff right here! Best of luck for the next!

  7. jpc January 11, 2013 at 1:14 am - Reply

    You are specific about the recommendation, “If you ever need to update a lot of records at once, say, increase pricing on all items by 10%, you’re much better off doing the update via a pass-thru query or on the server directly.”

    This is something I need to do on a regular basis working in Access 2010 updating SQL 2008 R2 tables. I find this extremely slow and slower than with Access 2003.

    Could you provide or point to an example of updating SQL table from Access 2010?

    • Juan Soto January 11, 2013 at 2:03 am - Reply

      Jeff,

      You can issue these commands through our EASY ADODB technique, which you can find here:
      http://accessexperts.net/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/

      For example, let’s say you need to update prices by 10% for all products in tblProducts:
      ExecuteMyCommand “Update tblProducts Set Price = Price * 0.10”

      That will execute the statement on the server instead Access doing it for you.

      Hope that helps!
      Juan

  8. Jesse Herrera November 9, 2012 at 2:46 am - Reply

    Thanks for the links! I was able to get the pass through query created but since my stored procedure requires a parameter the form is failing when loading saying the stored procedure requires a parameter. I tried turning off “Fetch Defaults” on the form but that didn’t seem to help. Thanks again for your time!

    • Juan Soto November 9, 2012 at 3:06 am - Reply

      Jesse,

      Load the form with no data, ask the user to select the parameter and then reload the form’s recordsource again.

      Regards
      Juan

  9. Jesse Herrera November 9, 2012 at 2:08 am - Reply

    I’m beginning the process of upgrading a very large Access 2000 ADP application. It works great but it’s probably past time to get the application upgraded to a newer version of Access. Is it still possible to have a RecordSource of a stored procedure? I didn’t see how to go about creating a link to a stored procedure but I’m also a bit lost so I’m not surprised. Thanks for your time!

  10. RENE PRADO October 24, 2012 at 7:58 am - Reply

    Hi Juan, Can you add a topic in your blog about ACCESS and Terminal Server. To my experience this is the best setup of ACCESS in a multi-user, it’s either JET/ACE or Server-Based back end. This is another area to be explore specially on deploying ACCESS Apps to web/internet. ACCESS 2013 web apps is no much on the above setup in terms of cost effectivity. Just a humble suggestion.

    • Ben Clothier October 24, 2012 at 7:55 pm - Reply

      Thanks for the suggestion, Rene! We’ll look into getting an article on the subject out.

  11. Anonymous October 8, 2012 at 11:27 pm - Reply

    Juan said:
    “The ODBC stack was horrendous in the early days, a charm to work with now

    Finally, Access was not as good in handling linked tables as it is now.”

    Which version of Windows would you consider to be “a charm” to work with ODBC? There are lots of XP installation still running but most of the improvements seem to be in 7 and 8.

    I would ask the same question for Access. Does this apply to 2007 or only to 2010 and newer? For me, 2010 still has too many bugs. I’m waiting for SP2.

    BTW, the new site design is excellent. It is much easier to comment now.

    • Juan Soto October 9, 2012 at 4:25 am - Reply

      XP is a charm, Vista not so much. Basically XP and 7 so far. I try and use Access 2007 as much as possible.

    • Kumar Sinha July 13, 2013 at 6:05 pm - Reply

      Juan has not seen the Access 2013 under the bonnet, he will find a bag of worms…lol

  12. Gilad October 7, 2012 at 9:41 pm - Reply

    Can you please also comment on your experience with a combination of linked tables from a remote SQL-Server and a local express SQL-Server version, both in the same query.
    Thanks greatly

    • Ben Clothier October 8, 2012 at 4:29 am - Reply

      If the “same query” is an Access query, then this is still subject to the same problems that Juan discussed with mixing Access tables with SQL Server tables. This is inherent in the fact that a operation involving two disparate data sources cannot have access to the same metadata of the other data source’s data (e.g. indexes, statistics, etc.) and therefore the engine (Access in this case) has no way about it but to retrieve the needed data from both sources and perform the comparison between two local copy to determine the final results. You can minimize the cost by passing filters that can be applied on the individual tables (e.g. restricting to only records added in last 10 days). Access is usually smart enough to pass back those sort of filters but for any filter where we are comparing datasource1.table1 = datasource2.table2, then Access need to work off a local cache by downloading the data.

      Such queries are called “Heterogeneous queries” and I should note that SQL Server also supports similar concept to some extent via Linked Server, OPENROWSET and few others but they still have their costs.

      If you predict that you will be doing this frequently and there is no filters that you can apply to each table individually, then you may need to consider using local tables to insert the data from each source and have Access query the local table only. This may be faster than performing a heterogeneous operation in some cases.

  13. tsmith60 October 4, 2012 at 7:22 pm - Reply

    Your comment about not connecting Local Tables with SQL tables in queries–have you found performance issues with SQL tables from two different databases? A lot of my applications link SQL tables from ERP systems like JD Edwards with SQL tables from my custom apps.

    • Ben Clothier October 8, 2012 at 4:30 am - Reply

      tsmith60, hopefully, my reply to Gilad should also answer your question as well. If you feel this need to be faster, it may be necessary to consider alternatives such as beforementioned downloading into local Access tables or perhaps using SSIS instead for the ETL operations.

  14. Avelino October 2, 2012 at 4:25 pm - Reply

    Juan, very good this contribution, I always wore my programs with ADO recordset to load data into and made a select list to fill the forms, despite not having any linked table thought it was very annoying to have to load the list with more than recordset 3000 data, currently I work with linked tables use a recordset combbox to use as search engine to load the data into a form I schedule with DAO and functions normally now as the protection of the tables I use a code that hides the tables even if someone tries active navigation options remain hidden tables here have a code examples:

    Public Function fncOcultarTabela (status As Boolean)
    ‘used in the form frmZerar
    If status Then
         Application.CurrentDb.TableDefs (“dbo_tblRegistro”). Attributes = 1
          
    else

         Application.CurrentDb.TableDefs (“dbo_tblRegistro”). Attributes = 0
     
    end If
    MsgBox “Restart the application …”, vbInformation, “Warning”
    End Function

    • Ben Clothier October 8, 2012 at 4:43 am - Reply

      Avelino, thank you for sharing!

      Just two things to be aware about:

      1) in older Jet versions (3.5 and below), the Hidden attribute also meant that when objects is compacted & repaired, it would be deleted. This is no longer true (at least since Access 2003; hadn’t tested on earlier systems). This isn’t a problem nowadays now that I think it’s very rare that people are using Jet 3.5 (Access 97 and earlier, IIRC) but something to be aware about, nonetheless.

      2) There are way to get Database Windows / Navigation Pane to show hidden & system objects and the Hidden attribute would still show up.

      As such, it is effective for deterring casual snoopers.

    • Kumar Sinha July 13, 2013 at 5:59 pm - Reply

      I am not sure why Microsoft have removed .adp from MS Access 2013! Link tables are awful to work with, views from SQL server is attached as a linked table and you have go through 100s views to identify unique reference and all you can see is the view name! You could have created views 6 moths ago. Alsothere are some inconsistency … I had a table in SQL Server “tblUser” with UserID (identity field int data type) as the primary key when linked tables the table had another field as the primary key “UserRef” (short text field). Now you can imagine when creating a dynaset trough DAO.

      I posted my comments in another forum as follows:
      “If you thinking of migrating to MS Access 2013 then think again!! It’s not worth the hassle. I have been coming up with so many hurdles you won’t believe it. Here are some:

      1 Access 2013 does not support .adp which is really a sad move for Microsoft

      2 the link table does not support BigInt data type, so if you have SQL Server with fields with “BigInt” then that is converted in the linked table to “Text”, if you are into database then you would know that one is a numeric and the other as Text data type so imagine all the problems you will have when writing queries with those fields.

      3 I have a user table called “tblUser” with an identity field UserID (internal numeric field set as primary key) and another field called “UserRef” (short text, no duplicate) on the SQL Server but when linked to the SQL Server from MS Access 2013 the primary key shows as “UserRef”!! There is no way to change that as it is a linked table!!

      4 All the views are as linked table and when you open them all the data show up as “#Deleted”

      5 ADO has to be referenced as it uses DAO, not too critical but a hassle some of the ADO does not work properly but that could be due to all the other issues

      5 ComCtl32.dll can not be referenced for controls such as treeview, listview on the 64bit OS. You can add the control as an active X control on the form from MSComCtlLib library but then you can not do early binding, so everything is declared as object and no way of referencing methods and properties when writing code. Even during runtime-debug mode you can not access the intellisence. There’s no “NodeClick” event!!

      6 you can not upgrade MS SQL Server database to MS SQL Server 2012 directly!!

      AND the list goes on…

      Microsoft way… Develop without getting “real users” requirements (development team involved first) then make the users involve and then make fixes and delay in new version and if the version do come out they are not meeting user acceptance, incompatible, etc which means more fixes. A classic example is the Visual Studio 2012 project can not be open in Visual Studio 2010 (what is the point of IL!!! There is no conversion wizard either!! It is easier to do it manually by coping the code from the designer into a new form and then the code for the front-end events and routine.

      Apple way… “Real Users” involvement with and without technical background and then requirements derived from the users’ need, and then passed to development team to make the product so the design meet the user acceptance. I am looking at the designs of iPAD (vs Surface), iPhones etc

      At this rate Microsoft will lose business and go down the tube. If they carry on like this I give a decade and people will switch; start using other products the are open source and people who can afford to live with minor issues with them from Microsoft. Microsoft is over-rated”

Leave A Comment

 

Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.