Quickly Link SQL Server Tables in Access

Long gone are the days when I did much application development in MS Access.  However, I still find it a darn good tool for querying, reporting and doing quick edits to data stored in other platforms.

I wrote this code ages ago to allow me to quickly create linked tables in MS Access for SQL Server tables/views. It is much faster than the manual UI approach and doesn’t require setting up a DSN on the local system to make the connection

Usage

Use the immediate window (ctrl-G) to enter these commands:

Link every table in a SQL database(“MYDB”) from SQL Server Instance (“MYSQLSERVER”)
? LinkTable(MYSQLSERVER,MYDB,True)

Link a single table(“MySQLTable”) in a SQL database (“MYSQLDB”)  from SQL Server Instance named “MYSQLSERVER”
? LinkTable(MyTableAliasInAccess, MYSQLSERVER,MYDB,MYSQLTABLE,True)

The Code

Just paste this code into a new module in the Access database and you are ready to go.

Sub LinkAllTables(Server As String, database As String, OverwriteIfExists As Boolean) 'Usage Example (link all tables in database "SQLDB" on SQL Server Instance SQO01, overwriting any existing linked tables. 'linkalltables "SQL01","SQLDB", true 'This will also update the link if the underlying table definition has been modified. Dim rsTableList As New ADODB.Recordset Dim sqlTableList As String sqlTableList = "SELECT [name] as tablename FROM sysObjects WHERE (type = 'U')" rsTableList.Open sqlTableList, BuildSQLConnectionString(Server, database) While Not rsTableList.EOF If LinkTable(rsTableList("tableName"), Server, database, rsTableList("tableName"), OverwriteIfExists) Then Debug.Print "Linked: " & rsTableList("tableName") End If rsTableList.MoveNext Wend rsTableList.Close Debug.Print "Done." End Sub Function LinkTable(LinkedTableAlias As String, Server As String, database As String, SourceTableName As String, OverwriteIfExists As Boolean) 'This method will also update the link if the underlying table definition has been modified. 'The overwrite parameter will cause it to re-map/refresh the link for LinktedTable Alias, but only if it was already a linked table. ' it will not overwrite an existing query or local table with the name specified in LinkedTableAlias. 'Links to a SQL Server table without the need to set up a DSN in the ODBC Console. Dim dbsCurrent As database Dim tdfLinked As TableDef ' Open a database to which a linked table can be appended. Set dbsCurrent = CurrentDb() 'Check for and deal with the scenario ofthe table alias already existing If TableNameInUse(LinkedTableAlias) Then If (Not OverwriteIfExists) Then Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite existing table." Exit Function End If 'delete existing table, but only if it is a linked table If IsLinkedTable(LinkedTableAlias) Then dbsCurrent.TableDefs.Delete LinkedTableAlias dbsCurrent.TableDefs.Refresh Else Debug.Print "Can't use name '" + LinkedTableAlias + "' because it would overwrite an existing query or local table." Exit Function End If End If 'Create a linked table Set tdfLinked = dbsCurrent.CreateTableDef(LinkedTableAlias) tdfLinked.SourceTableName = SourceTableName tdfLinked.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Server & ";DATABASE=" & database & ";TRUSTED_CONNECTION=yes;" On Error Resume Next dbsCurrent.TableDefs.Append tdfLinked If (Err.Number = 3626) Then 'too many indexes on source table for Access Err.Clear On Error GoTo 0 If LinkTable(LinkedTableAlias, Server, database, "vw" & SourceTableName, OverwriteIfExists) Then Debug.Print "Can't link directly to table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Linked to view '" & "vw" & SourceTableName & "' instead." LinkTable = True Else Debug.Print "Can't link table '" + SourceTableName + "' because it contains too many indexes for Access to handle. Create a view named '" & "vw" & SourceTableName & "' that selects all rows/columns from '" & SourceTableName & "' and try again to circumvent this." LinkTable = False End If Exit Function End If On Error GoTo 0 tdfLinked.RefreshLink LinkTable = True End Function Function BuildSQLConnectionString(Server As String, DBName As String) As String BuildSQLConnectionString = "Driver={SQL Server};Server=" & Server & ";Database=" & DBName & ";TRUSTED_CONNECTION=yes;" End Function Function TableNameInUse(TableName As String) As Boolean 'check for local tables, linked tables and queries (they all share the same namespace) TableNameInUse = DCount("*", "MSYSObjects", "(Type = 4 or type=1 or type=5) AND [Name]='" & TableName & "'") > 0 End Function Function IsLinkedTable(TableName As String) As Boolean IsLinkedTable = DCount("*", "MSYSObjects", "(Type = 4) AND [Name]='" & TableName & "'") > 0 End Function
Follow

Get every new post delivered to your Inbox.

Join 128 other followers