If you’ve ever asked Access to do a join between a local Access table and SQL Server table (which is called a heterogeneous join) you may have experienced first hand how slow it can be to process results. The situation could be vastly superior if you can afford to download the SQL table as a temporary table to your Access front-end and then process the join. This post will provide you with an easy solution to download SQL Server data into Access using a subroutine you can call from code.
Pass Through Query + Make Table Query Combo (No Linked Table Approach)
There is no direct way to download SQL Server data into Access using code, you can’t use a ADODB recordset or command to download the data, since they only “see” the SQL Server side and don’t have exposure to your Access data. The technique I’m providing here will use a temporary pass-through query, then using it with a make table query to create the local Access table copy of your SQL data.
'Insert this constant in a public module:
Public Const conConnectionQry As String = _
"ODBC;DRIVER={SQL Server};SERVER=YourServerIPAddressGoesHere;" & _
"UID=YourUserIDGoesHere;PWD=DittoPassWord;" & _
"DATABASE=NameOfYourSQLServerDatabase"
Public Sub CreateLocalSQLTable( _
strTable As String, _
strSQL As String, _
Optional bolExportExcel As Boolean _
)
Dim qdf As DAO.QueryDef
Dim strQuery As String
10 On Error GoTo ErrorHandler
20 strQuery = "qryTemp"
30 DoCmd.Close acTable, strTable
40 DoCmd.DeleteObject acQuery, strQuery
50 DoCmd.DeleteObject acTable, strTable
60 Set qdf = CurrentDb.CreateQueryDef(strQuery)
70 With qdf
80 .Connect = conConnectionQry
90 .SQL = strSQL
100 .Close
110 End With
120 strSQL = "Select * INTO " & strTable & " FROM " & strQuery
130 CurrentDb.Execute strSQL
140 DoCmd.DeleteObject acQuery, strQuery
150 If bolExportExcel Then
Dim strFile As String
160 strFile = CurrentProject.Path & "" & strTable & _
Month(Date) & Day(Date) & Year(Date)
170 If Dir(strFile) <> "" Then
180 Kill strFile
190 End If
200 MsgBox "Table " & strTable & " is ready for export to Excel"
202 DoCmd.TransferSpreadsheet acExport, , strTable, strFile, True
204 FollowHyperlink strFile & ".xlsx"
210 End If
ExitProcedure:
220 Set qdf = Nothing
230 Exit Sub
ErrorHandler:
240 Select Case Err.Number
Case 3376, 3010, 7874, 2059
250 Resume Next
260 Case Else
270 MsgBox Err.Description, vbInformation, "Error"
280 GoTo ExitProcedure
290 End Select
End Sub
The procedure takes three arguments:
- strTable: Name you wish to use for your local Access table
- strSQL: SQL statement used to retrieve data from SQL server. You can therefore extract multi-table joins into a local Access table.
- bolExportExcel: Boolean variable you pass-along if you wish to also extract the data into Excel
Example 1: Extract one table from SQL Server called tblCustomers
CreateLocalSQLTable "tblCustomers_SQL", "Select * from tblCustomers"
Notice how I appended the _SQL to my local Access table name. I will later delete all _SQL tables on program exit.
Example 2: Multi-table join extract
CreateLocalSQLTable "tblCustomers_SQL", "Select CustomerID, CustomerName, CustomerStatus from tblCustomers Inner Join tblCustomerStatus On tblCustomers.CustomerStatusID = tblCustomerStatus.CustomerStatusID", True
In the second example the code will also launch Excel with the extracted data, allowing my customer easy Access to data from SQL Server without the use of ODBC in Excel.
Add indexes and primary keys if needed
Once you’ve downloaded the data you may have a need to add indexes or primary keys to your local copy of the table, click here for a post that will walk you through that using Alter SQL commands in Access.
Another Approach using Linked Tables
If you have the SQL Server table already linked to your Access front-end you can extract the data using this procedure:
Public Sub CopyTableLocally(strTable As String)
Dim strSQL As String
10 On Error GoTo CopyTableLocally_Error
20 Application.Echo True, "Working on table " & strTable
30 DoCmd.DeleteObject acTable, strTable & "_SQL"
40 strSQL = "Select * Into " & strTable & "_SQL From " & strTable
50 CurrentDb.Execute strSQL
ResumeExit:
60 On Error GoTo 0
70 Exit Sub
CopyTableLocally_Error:
80 Select Case Err.Number
Case 3376, 3010, 7874, 2059 'Trying to delete an object that does not exist, continue
90 Resume Next
100 Case Else
110 MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure CopyTableLocally of Module mdlGeneral"
120 Resume ResumeExit
130 End Select
140 Resume ResumeXit
End Sub
Some Caveats
- Don’t download too much data. There’s a good reason your data is on SQL Server. Pulling large amount of data only eats up network traffic and may cause contention issues or even deadlocks. I usually use this technique for small reference tables that I later use in a all local joins. If your data is small and the data rarely changes in the table you’re downloading then this code is for you.
- Beware of file bloating: Ideally you should keep your temp tables in a separate Access file, otherwise your front-end may start getting pretty large and require frequent compacting to slim it back down.
Hi! good day Juan, I would like to ask about N-Tier Architecture. A SQLserver in a server accessed by a client pc with ms access or vb frontend is a client server model or 2-tier. What if both SQLserver and client frontend was installed in the same server but a terminal server and accessed by the client using remoteApp or remote Desktop? still a 2-tier or 1-tier model? is it possible to implement a 3-Tier model in ms access a.k.a MVC? . I’m also an access die hard but instead of using sqlserver as a backend, I find mySQL and Firebird more compatible with access (that is my own observation- migration and conversion is very straight forward and I dont have to worry even with the boolean Y/N field in access). Thanks and more power to you.
Hi Erwin,
Great questions! Yes, it’s still a two tier model regardless if it’s via RDC, WAN or Local network. I’m not sure what MVC is, perhaps you can elaborate?
In regards to a three tier model, here’s one to consider:
Main database on SQL Server
Access database on client PC
SharePoint Database holding temporary or transient data
Access Web Services running on IPad or other web device.
Hope that helps!
Juan
Thanks again Juan,
below is the definition from MS
The Model-View-Controller (MVC) pattern separates the modeling of the domain, the presentation, and the actions based on user input into three separate classes [Burbeck92]:
Model. The model manages the behavior and data of the application domain, responds to requests for information about its state (usually from the view), and responds to instructions to change state (usually from the controller).
View. The view manages the display of information.
Controller. The controller interprets the mouse and keyboard inputs from the user, informing the model and/or the view to change as appropriate.
Controller. The controller interprets the mouse and keyboard inputs from the user, informing the model and/or the view to change as appropriate.
Hi Juan
Interesting read – and it just goes to show that as always, there are multiple ways to skin a cat…
I have a question. In this sort of scenario – one where I wanted a temporary table, but one which may have relationships to other tables – I’ve always been likely to do the work in two steps. Firstly a DDL SQL statement to create the table , along with its contraints, indexes and foreign keys. Then secondly, a straightforward DML statement to append the data into that table.
I’m certain that in fact this is simply down to “the way I’ve always done it” – but I suppose I’ve also instinctively felt that creating the indexes etc on an empty table and then appending data would be faster than using an ALTER TABLE command once the data already resides there.
Do you have any thoughts on whether one way or other is likely to yield a performance benefit?
Thanks for a thought-provoking read!
Andrew
Hi Andrew,
Thanks for sharing your technique! I can’t tell you for sure if doing the indexes first and then filling the table with data is faster but your approach does require more discipline.
Sometimes a temp table is the derivative of a multi-join query, so I commend you in taking the time to create the table first, I for one use the INTO statement and then ALTER but each of us has our preferences!
Sincerely,
Juan
Hi Juan good day to you! is it possible to create a 3 tier model or application in pure or native ms access app with a server db backend. It says in 3 tier architecture that the business logic or rules is put into separate server hardware..im puzzled if its possible and really applicable in ms access. Although im very satisfied with 2 tier implementation with access, i just want to explore some of the possibilities (no sharepoint). Hoping for your prompt response and tnx so much. erWIN
Erwin, good question.
N-tiered architecture in general depends on having a middleware… this could be a web server, a DLL or something similar sitting between the client and the database server. As such, you wouldn’t use ODBC, OLEDB or similar technologies when you build a middleware but rather implement the functionality in forms of web services using protocols like SOAP or REST or a properitiary interface which the client must support in order to connect to the middleware. N-tiered architecture is a wonderful when you have a need to support multiple numbers of architectures (think of FaceBook or Twitter – they need/want clients running on not just Windows, but also Mac OS X, iOS, Windows phone, Android… list goes on).
Hopefully, it may become clear why in general Access doesn’t really lend itself as a n-tiered client. We typically are more likely to use it in a corporate environment where there everyone is supposed to use same OSes, is constrained in how they can access data (e.g. may not get to it from home unsecured). In that context, n-tiered architecture would possibly be overkill. That doesn’t mean we have to use 2-tiered architecture exclusively. Juan already cited SharePoint as an example. You want another example not using SharePoint? Write procedures to consume a SOAP web service would effectively make Access a client in a n-tiered architecture. Even better, because it’s going over port 80, it may mean it now can use data from sources even when firewall setting is excessively restrictive and there’s no chance of getting the network guy to change it. Microsoft has an example of calling SOAP web service from VBA:
http://msdn.microsoft.com/en-us/library/aa140260(v=office.10).aspx
I hope that help. If you need assistance building a solution that would enable Access to participate in a n-tiered architecture, feel free to make use of our consulting service and call us at 773.809.5456.
Best of luck!
Pingback: DAO or ADODB? Which one should you use and when?accessexperts.com | accessexperts.com