Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz
Relinking tables and views between Access and SQL Server

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 you 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 example of the code you need to do this, and to maintain view updateability.

A Problem Relinking Views

When you link a view Access automatically prompts you for a combination of one or more fields to make the views data unique, 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 ths 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 databse. 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.


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


Relinking Tables

By this we mean code with 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


This code will re-link any odbc linked tables, but if you have a view which is updateable, then it will become read-only (see later for improvements to handle this).

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 RelinkSQLQueries()
Dim db As Database
Set db = CurrentDb
Dim qdef As QueryDef
Dim constr As Variant
constr = DLookup("ConnectionString", "tblConnections", "Active = True")
For Each qdef In db.QueryDefs
If InStr(qdef.Connect, "ODBC") Then
qdef.Connect = constr
End If
Next
MsgBox "Re link completed"
End Sub
Creating Linked Tables

It is also useful to have some code for creating linked tables.

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.





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 bigger significantly, 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.

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
Top of Page
Download Code

The sample code can be downloaded from here Access 2000 Database


MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or bringing together multiple Access databases into a single SQL Server database. Click below to improve your development productivity today.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz


Copyright © 2008 ASC Associates, Technical Support support@ascassociates.biz , Sales Enquiries sales@ascassociates.biz