Code for linking files

[Paul Watts]

Hi.
I have created a database with a single foreground file (used by several people) and several background files each containing similar data from different divisions of our business. For various reasons, we often have to redo the links between the foreground file and tables in one or other background file. I'd like to make this a simpler and more foolproof process.
Does anyone have any code for setting up links. The code would have to cancel existing links and allow the user to establish new links with a specified file. I know there's a wizard that does this but I want to prevent users from accessing the navigation bar and dabbling with objects.

Is this clear??

Regards

Paul

 

[bob fitz]

A search in google found:
http://www.devhut.net/2011/04/07/ms-access-vb-relink-back-end-tables/
http://access.mvps.org/access/tables/tbl0009.htm
http://www.jstreettech.com/files/JStreetAccessRelinker.zip
http://dfenton.com/DFA/download/Access/Reconnect.html
http://www.utteraccess.com/forum/Linking-Tables-t128312.html
http://support.microsoft.com/kb/209862

...a single foreground file (used by several people)...

Not at the same time I hope. This would likely lead to corruption.

 

[boblarson]

Hi.
I have created a database with a single foreground file

Each person should have their own copy of the frontend they are opening. Read more about why here:
http://www.kallal.ca/Articles/split/index.htm

 

[Paul Watts]

No, only one user at a time.

Paul

 

[ahaigh]

The easiest way is via ADOX:

Public Function LinkTables(ByVal sFrontEnd As String, ByVal sDatabase As String) As Boolean
On Error GoTo ErrorHandler
Dim catDB1 As adox.Catalog
Dim catDB2 As adox.Catalog
Dim tblLink As adox.Table
Dim attrib As String
Dim sLinkedTo As String
Dim cnn1 As Connection
Dim cnn2 As Connection

Set cnn1 = New Connection
Set cnn2 = New Connection

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFrontEnd
cnn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabase

Set catDB1 = New adox.Catalog
Set catDB2 = New adox.Catalog

' Open a Catalog on the database in which to verify the links.
catDB1.ActiveConnection = cnn1
catDB2.ActiveConnection = cnn2

For Each tblLink In catDB1.Tables
If tblLink.Type = "LINK" Then
'This is a linked table
sLinkedTo = tblLink.Properties("Jet OLEDB:Link Datasource").Value

If InStr(LCase$(tblLink.Name), "customerspecific") = 0 Then
If TableExists(catDB2, tblLink.Name) Then
tblLink.Properties("Jet OLEDB:Link Datasource").Value = sDatabase
Else
Debug.Assert False
End If
End If
End If
Next

Set catDB1 = Nothing
Set catDB2 = Nothing
LinkTables = True

cnn1.Close
Set cnn1 = Nothing

cnn2.Close
Set cnn2 = Nothing
Exit Function
ErrorHandler:
'LogError Err.Description, "LinkTables", MODULE_NAME, False, Erl
End Function

 

[Paul Watts]

Wow ahaigh.
This is far too complex for me to understand.
Can I copy and paste this and expect it to work ok?

Paul

 

[speakers_86]

I like the jstreet link from post 2. You can copy and paste it. The only thing you will need to do is call the relink routine from your AutoExec.

 

[ahaigh]

Paul - probably not - you'd need to adapt bits to your own needs.

1) Add a reference to Microsoft ADO Ext. 2.7 for DDL and Security (msadox.dll)
2) I'm using Access 2003, for which the provider is Microsoft.Jet.OLEDB.4.0 yours may be different

Sadly the whitespace has been stripped out of the pot, making the code VERY hard to read! Once properly indented its easy to follow...

The routine expects 2 filenames to be passed to it - the first is the database that you want to update, the second is the database containing the tables you want to link to.

I don't update any tables that contain 'customerspecific' in their names - you can obviously modify this logic to suit your needs

 

[Paul Watts]

Hi ahaigh
Sorry I've taken so long to respond but I have been trying to understand your code. However, being a complete VBA novice, I have failed miserably. I can't even understand it's structure. :banghead:

I have, however, been wondering whether to adopt a different approach. If I had code which deleted all current links and then automatically started the existing links wizard ............

Would this be something you could help me with?

Paul