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 using 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 its 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:
DoCmd.OpenForm "frmCustomers", WhereCondition="[CustomerID] = " & lngCustomerID
Where lngCustomerID is a variable holding the CustomerID you wish to see.
Do design your Microsoft 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
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?
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.
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 ).
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
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.
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.
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!
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?
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
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!
Jesse,
Load the form with no data, ask the user to select the parameter and then reload the form’s recordsource again.
Regards
Juan
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!
Welcome to the blog Jesse!
Yes, you can use a stored procedure as a recordsource, but only as read only. There are two methods:
— Use a pass-through query
— Create a temp table and use the table as a recordsource.
TO learn more, please check out this post:
http://accessexperts.net/blog/2011/07/29/sql-server-stored-procedure-guide-for-microsoft-access-part-1/
and:
http://accessexperts.net/blog/2012/01/10/create-temp-tables-in-access-from-sql-server-tables/
Hope that helps!
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.
Thanks for the suggestion, Rene! We’ll look into getting an article on the subject out.
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.
XP is a charm, Vista not so much. Basically XP and 7 so far. I try and use Access 2007 as much as possible.
Juan has not seen the Access 2013 under the bonnet, he will find a bag of worms…lol
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
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.
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.
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.
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
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.
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”