SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Relinking Tables and Views Between Access and SQL Server
Microsoft MVP Andy Couch About the Author

Andrew is a founding member of the UK Access user group and has worked as magazine editor, guest speaker, trainer and run the web sites for the Access and UK Office user groups.

For the past 15 years he has worked with a variety of databases and client-server developments but his current specialty is in migrating large Access databases from Access back-ends to SQL Server. Over the past ten years he and others have been using their own migration tool, which they have now launched as a commercial product at Upsizing.co.uk.

Andrew is a Microsoft MVP and you can follow him on Twitter @Upsizing.
Send to a Friend Printer Friendly Version
Most of the time re-linking tables is not a problem, it is simply a question of having an automated procedure for doing this. Re-linking views is however more of a problem in respect of maintaining updateable views.

In practise you will often want to re-point your Access front-end at a test or development SQL back-end, and then again back at the live system, and having an automated procedure for this will save you hours of work. You also need to re-link Views and Tables when the underlying Table structure or View definitions change, for example when a new column is added, removed or changed in a Table. This article shows examples of the code you need to do this, and to maintain view updateability.

Problem Relinking Views

When you link a view Access automatically prompts you for a combination of one or more fields to make the view data unique and this enables the view to be updateable. However, if you use program code to re-point the view, the index gets destroyed, and the view is no longer updateable.

Solution

Modify your program code to identify and then re-create any indexes over linked views. To make this work, execute a DDL command to create a new index on the view.

Background

A linked table has three key properties. The table name (this is the apparent name for the table, as Access allows the true name to be changed). The source table name, this is the true name for the table. The Connect string, which contains the details which point the Access link at a particular data source; in this case the SQL server database. The main method used here is RefreshLink, which causes Access to repoint the link when the Connect property is changed.

Example

Consider the example of linking an Access application to the SQL Server database for the Northwind database. When the link is created, it will most likely be given the name dbo_Customers, or if you used a software utility to do this it may be called simply Customer, but shown with a globe icon to indicate this is a linked table.

Globe icon to indicate linked table

Pressing Ctrl-G and writing a little code in the immediate window reveals much about this linked table.

The Immediate Window

Relinking Tables

By this we mean code which uses the Data Access Objects. This code needs to search all the Tabledefs where the connection string contains “ODBC”, and set the strings to point at a different database, then the link needs to be refreshed. It is a good idea to create a table which holds different connection strings, so that you can easily relink all your tables.

Example data
Example data

Connection String

Sub RelinkSQLTables()
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim constr As Variant
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
tdef.Connect = constr
tdef.RefreshLink
End If
Next
MsgBox "Re link completed"
End Sub


Relinking Queries

Similar code is also required for re-linking any pass through queries.

Sub CreateLinkInCode()
'This example create a link using program code
' Assuming connect string is for the northwind database
Dim constr As Variant
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
Dim db As Database
Dim tdef As TableDef
Set db = CurrentDb
Set tdef = New TableDef
tdef.Name = "OrderDetails"
tdef.Connect = constr
tdef.SourceTableName = "dbo.Order Details"
db.TableDefs.Append tdef
End Sub


Relinking Tables And Views

Consider creating the following view in the Northwind database in SQL Server.

CREATE VIEW ExampleOrderDetails AS SELECT * FROM [Order Details]

Then when you link this view selecting the following two key fields as unique.

Select unique record identifer



The following code could be used to relink tables, and maintain view updateability.

Sub RelinkSQLTablesAndViews()
' This example re-links any tables and views
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim indexSQL As String
Dim fld As Field
Dim constr As Variant
Dim HasIndex As Boolean
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
For Each tdef In db.TableDefs
If InStr(tdef.Connect, "ODBC") Then
HasIndex = False
If tdef.Indexes.Count = 1 Then
' only interested in objects with 1 index
indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
' convert field list from (+fld1;+fld2) to (fld1,fld2)
indexSQL = Replace(indexSQL, "+", "")
indexSQL = Replace(indexSQL, ";", ",")
HasIndex = True
End If

tdef.Connect = constr
tdef.RefreshLink
If HasIndex And tdef.Indexes.Count = 0 Then
' if index now removed then re-create it
CurrentDb.Execute indexSQL
End If

End If
Next
MsgBox "Re link completed"
End Sub


Alternative Code For Bloat Problem

WARNING : The code below can make bloating worse, and should ONLY be used if you have the problem described below. Regard this as an alternative way of doing things worthy of investigation that has in our experience solved some specific problems.

Bloat is when a database becomes significantly bigger for no apparent reason. The code here is provided as an alternative method for re-generating links, which could be used to get around a bloat caused when re-linking to a different SQL Server, or this can be viewed as an alternative to the code method outlined above.

There are several points to highlight in the code, firstly it deletes the tabledefs and then re-creates them, this means that you must take care when using the tabledefs collection because the contents are changing so we drop the FOR loop and open a recordset which utilises the system table MSysObjects inside a query to identify the linked tables. We also need to use a recordcount to make sure that steps do not get repeated as the contents of the MSysObjects will change during the processing.

The two techniques described in this article (using the DAO - TableDefs or the system objects table - MSysObject) can be merged together to solve different specific problems.

Firstly we need a query to locate the SQL Server linked tables, an example is shown below.

SQL Server linked tables
Then the alternative code is shown below.

Sub RelinkSQLTablesAndViewsAlternative()
' This example re-links any tables and views
Dim db As Database
Set db = CurrentDb
Dim tdef As TableDef
Dim indexSQL As String
Dim fld As Field
Dim constr As Variant
Dim HasIndex As Boolean
Dim rst As Recordset
Dim Tablename As String
Dim SourceTableName As String
Dim tablecounter As Integer
Dim i As Integer

constr = DLookup("ConnectionString", "tblConnections", "Active = True")
' find all the tables to be relinked
Set rst = db.OpenRecordset("qryTablesUsingMSysObjects", dbOpenDynaset)
If rst.EOF Then
Exit Sub
End If
rst.MoveLast
' get a count of how many records to process
tablecounter = rst.RecordCount
rst.MoveFirst

For i = 1 To tablecounter
Set tdef = db.TableDefs(rst!Name)
HasIndex = False
If tdef.Indexes.Count = 1 Then
' only interested in objects with 1 index
indexSQL = "CREATE INDEX " & tdef.Indexes(0).Name & " ON [" & tdef.Name & "](" & tdef.Indexes(0).Fields & ")"
' convert field list from (+fld1;+fld2) to (fld1,fld2)
indexSQL = Replace(indexSQL, "+", "")
indexSQL = Replace(indexSQL, ";", ",")
HasIndex = True
End If

Tablename = tdef.Name
SourceTableName = tdef.SourceTableName
Set tdef = Nothing
db.TableDefs.Delete Tablename
Set tdef = New TableDef
tdef.Name = Tablename
tdef.SourceTableName = SourceTableName
tdef.Connect = constr
db.TableDefs.Append tdef

If HasIndex And tdef.Indexes.Count = 0 Then
' if index now removed then re-create it
CurrentDb.Execute indexSQL
End If

rst.MoveNext
Next

MsgBox "Re link completed"
End Sub


Download Code

The sample code can be downloaded from here Access 2000 Database.
MUST+SQL automatically translates your Access queries into Views and Stored Procedures, converting complex calculations to Nested Queries.

Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form