Changing links with code (1 Viewer)

[MikeAngelastro]

Hi,

I'm maintaining a frontend access db that can be attached to different backends depending on what company it is for. Each company frontend has tables that are linked to the same backend because they are common to all companies and other tables that are linked to their own backend. The tables that are linked to their own backend are specified in a table called "tblMyLinkedTables" containing the connect string of the of their specialized backend. This table is in the common backend db. I want to be able to change the connect string to the appropriate one for any particular company based on this table. I am using the following code the make the change, but the change does not actually occur. No error is raised either. It's as if the properties of tabledefs are read-only.

Public Function CorrectMyLinkedTables()
'Purpose: To update the table links of the db's linked tables so that they agree with tblMyLinkedTables.

Dim td As TableDef
Dim MyLinkedTables As DAO.Recordset

Set MyLinkedTables = CurrentDb.OpenRecordset("qryMyLinkedTables", dbOpenSnapshot)
With MyLinkedTables
.MoveFirst
Do Until .EOF
If Len(CurrentDb.TableDefs(!Name).Connect) > 0 Then
CurrentDb.TableDefs(!Name).Connect = !ConnectString
CurrentDb.TableDefs(!Name).RefreshLink
End If
.MoveNext
Loop
End With

End Function

Does anyone know why the above code does not work?

Thanks,

Mike

 

[ChrisO]

G’day Mike.

Well I can’t help with that method because I’ve never used it.

But here is a demo that uses the TransferDatabase method to achieve the same outcome.

The company is selected from a list box and is also saved so that it is the company that is displayed the next time the db is opened.

BTW, this is derived from the first database I ever wrote, about 8 years ago, and now has 237 backends. Still works fine…

Maybe it is an alternative you can use.

Regards,
Chris.

 

[MikeAngelastro]

Thanks ChrisO,

I have downloaded your database and plan to take a look at it. But I wanted to show folks the solution I came up with.

The first part of the solution is to create a table, called tblMyLInkedTables, in a common backend. Give it CompanyCode, Name, SourceTableName, and ConnectString for fields. Create a link to that table in all the frontends that will use it. The company code is what identifies the frontend in the table and in my approach I use the database filename to determine that code. I then run the function "RecordMyLinkedTables". This records the links and stores the required info in the table. When a table is added or removed from the linked tables list in the frontend, this function is run again to update the table so that it reflects the new make up.

The second part is to run the function "CorrectMyLinkedTables" whenever the frontend is loaded. Because it uses its filename to determine what company it represents, it generates the proper links for that company. This way I need only one frontend for customization. This frontend can then be copied to different frontends with different filenames.

The difficulty I was having was fixed by using "Set db = DBEngine.Workspaces(0).Databases(0)" instead of "Set db = CurrentDb". Does anyone know why the first one worked and the second one didn't?

I will repost after looking at ChrisO's database.

Public Function RecordMyLinkedTables()
'Purpose: To update a table with the specs of the db's linked tables.

Dim td As TableDef
Dim TableName As String
Dim SourceTableName As String
Dim ConnectString As String
Dim MyLinkedTables As DAO.Recordset

'Clear out tblMyLinkedTables
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryClear_tblMyLinkedTables"

Set MyLinkedTables = CurrentDb.OpenRecordset("tblMyLinkedTables", dbOpenDynaset)

For Each td In CurrentDb.TableDefs
If Left(td.Name, 1) <> "~" Then
TableName = td.Name
SourceTableName = td.SourceTableName
ConnectString = td.Connect
If Len(ConnectString) > 0 Then
With MyLinkedTables
.AddNew
!CompanyCode = Left(CurrentProject.Name, 3)
!Name = TableName
!SourceTableName = SourceTableName
!ConnectString = ConnectString
.Update
End With
End If
End If
Next

Set MyLinkedTables = Nothing
DoCmd.SetWarnings True

End Function


Public Function CorrectMyLinkedTables()
'Purpose: To update a table with the specs of the db's linked tables.

On Error GoTo HandleError

Dim td As New DAO.TableDef
Dim MyLinkedTables As DAO.Recordset
Dim db As DAO.Database
Dim SQL As String

SQL = "SELECT * FROM tblMyLinkedTables WHERE CompanyCode = " & SQLstring(Forms![xMain Menu]!CompanyCode)
Set db = DBEngine.Workspaces(0).Databases(0)
Set MyLinkedTables = db.OpenRecordset(SQL, dbOpenSnapshot)
With MyLinkedTables
.MoveFirst
Do Until .EOF
Set td = db.TableDefs(!Name)
If Len(td.Connect) > 0 Then
td.Connect = !ConnectString
td.RefreshLink
End If
Set td = Nothing
.MoveNext
Loop
End With

ExitFunction:
Set MyLinkedTables = Nothing
Exit Function

HandleError:
RecordError Err.Number, Err.Description, "Running function CorrectMyLinkedTables"
MsgBox "Error: " & Err.Number & " - " & Err.Description & " Running function CorrectMyLinkedTables."
Set td = Nothing
If Err.Number = 3011 Then
'The table is not in the linked-to database.
Resume Next
Else
Resume ExitFunction
End If
End Function

 

[Oldsoftboss]

This is what I adapted from the Northwind Db

http://www.access-programmers.co.uk/forums/showthread.php?t=63235&highlight=link+back+end

Dave