Go to Top

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.

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.

About Juan Soto

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.

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

  • Ernesto
    December 28, 2015 - 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

  • christ
    November 6, 2015 - 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.

  • dbennett2
    June 29, 2013 - 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.

  • http://umoreinstabile.altervista.org/lo-zodiaco-degli-stronzi-cancro
    May 23, 2013 - 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!

  • jpc
    January 11, 2013 - 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 - 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

  • Jesse Herrera
    November 9, 2012 - 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 - 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

  • Jesse Herrera
    November 9, 2012 - 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!

  • RENE PRADO
    October 24, 2012 - 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 - 7:55 pm Reply

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

  • Anonymous
    October 8, 2012 - 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 - 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 - 6:05 pm Reply

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

  • Gilad
    October 7, 2012 - 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 - 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.

  • tsmith60
    October 4, 2012 - 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 - 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.

  • Avelino
    October 2, 2012 - 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 - 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 - 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 Reply

Your email address will not be published. Required fields are marked *

 

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