Automate Linked Tables

[ITperson]

Hello

I have 261 table that are linked.
When I do maintenance on my db, I use a local copy. In the linked table
manager, I see all the linked tables.

When I implement my changes to the server, I copy my local copy of the db to
the server. But: the database paths for the linked tables still reflect my
local system.

I have 1 of 3 dbs that the tables are linked to (temp.mdb, hist.mdb or
data.mdb).

My idea is this (although if you have a better solution, Im all ears).
Create 3 arrays where each array will contain the names of the tables for
each db. Then take each array and write the tablenames to the linked table
manager.

However, "How do you do that?"

I have 1 array that would hold 155 tablenames, but I cannot figure out how
to get the names into the array. I have written some code in excel that
concatenates the tablenames to "Arr_temp(var)=tablename" (is this correct?

But I also need to delete all the linked tables in the linked table manager
before creating these new ones.

The reason I need to do this is because it takes me at least 40Minutes to do
this and I have to do it several times a week.

Thank you for your help and input.

Terry

 

[Alex Dybenko]

Hi,
you can make a local where with tables names and then adjust this proc for
your needs:
http://www.mvps.org/access/tables/tbl0009.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Stefan Hoffmann]

hi,

My idea is this (although if you have a better solution, Im all ears).
Create 3 arrays where each array will contain the names of the tables for
each db. Then take each array and write the tablenames to the linked table

First of all, store this information in a table, e.g. ID, SourceTable,
PathToMDB.

Use a function like this to link the tables:

Public Sub TableLinkMDB(ASourceName As String, _
APathToMdb As String, _
Optional ADestinationName As String = "")

On Local Error GoTo LocalError

Dim count As Integer

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
Else
ADestinationName = UCase(ADestinationName)
End If

CurrentDbC.TableDefs.Refresh
For count = 0 To CurrentDbC.TableDefs.count - 1
If CurrentDbC.TableDefs(count).NAME = ADestinationName Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete (ADestinationName)
Exit For
End If
Next count

Debug.Print "+"; ASourceName; "="; ADestinationName
DoCmd.TransferDatabase acLink, "Microsoft Access", _
APathToMdb, acTable, _
ASourceName, ADestinationName ', , True
End If

Exit Sub

LocalError:
'Error handling

End Sub

You may use a loop like this

For TableCount = CurrentDbC.TableDefs.count - 1 To 0 Step -1
If (CurrentDbC.TableDefs(TableCount).Attributes _
And dbAttachedTable) Or _
(CurrentDbC.TableDefs(TableCount).Attributes _
And dbAttachedODBC) Then

With CurrentDbC.TableDefs(TableCount)
MsgBox .NAME & "-" & .SourceTable
End With

End If
Next TableCount

to collect the information for your table.

Also you may take a look at

http://www.mvps.org/access/tables/tbl0009.htm

mfG
--> stefan <--

 

[ITperson]

hi,

First of all, store this information in a table, e.g. ID, SourceTable,
PathToMDB.

Use a function like this to link the tables:

Public Sub TableLinkMDB(ASourceName As String, _
APathToMdb As String, _
Optional ADestinationName As String = "")

On Local Error GoTo LocalError

Dim count As Integer

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
Else
ADestinationName = UCase(ADestinationName)
End If

CurrentDbC.TableDefs.Refresh
For count = 0 To CurrentDbC.TableDefs.count - 1
If CurrentDbC.TableDefs(count).NAME = ADestinationName Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete (ADestinationName)
Exit For
End If
Next count

Debug.Print "+"; ASourceName; "="; ADestinationName
DoCmd.TransferDatabase acLink, "Microsoft Access", _
APathToMdb, acTable, _
ASourceName, ADestinationName ', , True
End If

Exit Sub

LocalError:
'Error handling

End Sub

You may use a loop like this

For TableCount = CurrentDbC.TableDefs.count - 1 To 0 Step -1
If (CurrentDbC.TableDefs(TableCount).Attributes _
And dbAttachedTable) Or _
(CurrentDbC.TableDefs(TableCount).Attributes _
And dbAttachedODBC) Then

With CurrentDbC.TableDefs(TableCount)
MsgBox .NAME & "-" & .SourceTable
End With

End If
Next TableCount

to collect the information for your table.

Also you may take a look at

http://www.mvps.org/access/tables/tbl0009.htm

mfG
--> stefan <--
Here is the final outcome and it works:

Sub relinktables()
Dim rstRecordset As Recordset
Set rstRecordset = CurrentDb.OpenRecordset("SELECT * FROM linkedtablepaths")
With rstRecordset
Dim tblname As String
Dim dbname As String
ehp ' delete all linked tables first > see sub below

Do While Not rstRecordset.EOF
dbname = rstRecordset.Fields("pathtomdb").Value
tblname = rstRecordset.Fields("sourcetable")
Call LinksCreateToSource(dbname, tblname)
.MoveNext
Loop
End With
End Sub

Sub LinksDelete(Optional strConnectString As String = "")
'This function removes links to tables with specified connections
'If strConnectString is omitted all links will be removed
Dim tdf As TableDef

For Each tdf In CurrentDb.TableDefs
If Not tdf.Name Like "MSys*" Then
If tdf.Connect <> "" Then
If InStr(1, tdf.Connect, strConnectString, vbTextCompare) > 0 Then
DoCmd.DeleteObject acTable, tdf.Name
End If
End If
End If
Next tdf
End Sub




Sub LinksCreateToSource(strLinkSourceDB As String, tdf As Variant)
On Error GoTo Error3011

Dim dbs As Database

Set dbs = DBEngine.Workspaces(0).OpenDatabase(strLinkSourceDB)

If Left(tdf, 4) <> "MSys" Then 'Do not create links to the System
tables
'Links create

DoCmd.TransferDatabase acLink, _
"Microsoft Access", strLinkSourceDB, acTable, tdf, tdf
End If
dbs.Close
Set dbs = Nothing

Error3011:
If Err.Number = 3011 Then 'If it cannot find the table in the db it is
linking to
Exit Sub
Else: Resume Next
End If

End Sub

It was a tough go of it, but it works well (it does take a while to run
because there are 261 tables to link; 1db is local, 2 are on our server.

Thanks for all your help

Terry