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?
DoCmd.OpenForm "frmCustomers", WhereCondition="[CustomerID] = " & lngCustomerID
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
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.
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.
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.
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.
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.
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.
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!
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
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
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!