Automate Linked Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ITperson - 27 Mar 2008 16:52 GMT

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

Reply to this Message

Alex Dybenko - 27 Mar 2008 17:16 GMT

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

Signature

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

> Hello
>
[quoted text clipped - 34 lines]
>
> Terry

Reply to this Message

Stefan Hoffmann - 27 Mar 2008 17:18 GMT

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 <--

Reply to this Message

ITperson - 27 Mar 2008 22:56 GMT

> hi,
>
[quoted text clipped - 67 lines]
> --> stefan <--
> Here is the final outcome and it works:

[Code]

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

[/Code]

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

Reply to this Message