Linked Table Manager Macro

[bwesenberg]

I would like to create a macro or something that I can assign to a button on my form that will refresh the link on the tables. So the users do not have to do this manually.

Can anyone help me with this?
--
Becky
Applications Trainer
XP & 2003 Master Instructor

[Rabbit]

Why would you need to refresh the links? People moving the tables all the time?

[NeoPa]

Are you talking about the RecordSource of the form or some other tables?
What changes are you anticipating that would require the Requery?

[NeoPa]

Having reread the question a couple of times I read the title. This gives some context to the question, but please try to ask the whole question clearly. It saves us all some time.

This (Redoing the link - or Connect property - of a linked table) is something I've tried but failed to do in the past. My best workaround (I only needed to handle two scenarios - test & live data) was to have both sets available but one would be renamed-out. IE. only one set would have the name as used in the project, while the other set was named with a suffix to separate it from the project.

I appreciate that this is not what you were actually after (and I don't know how many different sets you need) but it's the best I've managed so far. If a better answer comes along I will be as interested as you are to learn :)

[Rabbit]

I've done it before. Delete the link and then do a TransferDatabase to relink a table from a different database. I did it because I was afraid people would keep moving the database around.

[NeoPa]

Does that work for ODBC links too?

[Rabbit]

Does that work for ODBC links too?

I'm not sure. I don't deal much with the difference between ODBC and ADODB. The only time I've ever used it is to link between different Access Databases and never specified between ODBC or ADODB.

[Scott Price]

I have a macro in a database I use (not written by me...) It has this format:
Action Function Name
RunCode AttachAllTables()

This references a custom function that looks like this:

'~~~~~------------------------------------------------------------------------
' MODULE:      Attach Table Lib
' AUTHOR:      Curtis Consulting Group
' DATE:        08/02/1993
' PURPOSE:     To Attach and Detach Tables
' DESCRIPTION: Uses Tables: TableDef and TableAttach
'              Uses Queries: qryTablesToAttach, qryDBsToAttach
'              Uses Modules: MailLib (Send mail if corrupt)
'              Uses APP_NAME constant for msgboxes
'
' MODIFICATIONS (MOST RECENT ON TOP)
'DATE    -Function-INI-CHANGE
'04/11/94 DetachTable-djs-rewrote
'03/xx/94 AttachAllTables and AttachTable-kdh-mod for SQL Server
'01/20/94 Verifile-djs-moved from StdLib module
'01/20/94 AttachAllTables-simplified error message
'12/07/93 AttachAllTables-elm-add drive/path/file to error message
'12/02/93 AttachAllTables-djs-prompt user for path if DrivePathFile is blank
'11/02/93 AttachAllTables-djs-test opening each db - requires qryDBsToAttach and new field in TableAttach
'------------------------------------------------------------------------+++++
Option Compare Database   'Use database order for string comparisons
Option Explicit
 
Dim dum
 
'''''-------------------------------------------------------------------------
' TITLE:   Attach All Tables
' AUTHOR:  D.J. Schuba
' DATE:    08/02/1993
' PURPOSE: To attach all tables according to TableDef and TableAttach
' DESCRIPTION:
'
'------------------------------------------------------------------------+++++
Function AttachAllTables() As Integer
  Dim db As Database, dbTest As Database, ds As Recordset
  Dim DrivePathFile$, DrivePath$, TableName$, LastAttach$, Notify$
  Dim retval%, SourceType$
  Dim TblCnt
 
  DoCmd.Hourglass True
  retval = True
 
  ' Test open all databases
  On Error GoTo CannotOpenDB
  Set db = CurrentDb()
  Set ds = db.OpenRecordset("qryDBsToAttach")
  If Not ds.EOF Then
    Do Until ds.EOF
      If IsNull(ds!DrivePathFile) Then
        ' Need to ask user where the data is
        Do
          If Len(DrivePath) = 0 Then
            DrivePath = InputBox$("Enter the drive\path for " & ds!DfltFile & ". Please use the universal naming convention for network drives (e.g., \\serverx\sharey ).", App_Name)
          End If
 
          If Len(DrivePath) = 0 Then
            ' the user did not even try so quit
            DoCmd.Quit
          Else
            If Right(DrivePath, 1) = "\" Then
              DrivePathFile = DrivePath & ds!DfltFile
            Else
              DrivePathFile = DrivePath & "\" & ds!DfltFile
            End If
            Set dbTest = OpenDatabase(DrivePathFile)
            If Len(DrivePath) Then
              ds.Edit
              ds!DrivePathFile = DrivePathFile
              ds.Update
            End If
          End If
        Loop Until Len(DrivePath)
 
      Else
        ' test open for corruption only for MS Access Tables
        If ds!SourceType = "MSACCESS" Then
          Set dbTest = OpenDatabase(ds!DrivePathFile)
        End If
      End If
 
      ds.MoveNext
    Loop
  End If
  ds.Close
 
  ' Now let's see if everything is attached correctly
  On Error GoTo 0
  Set ds = db.OpenRecordset("qryTablesToAttach")
 
  If Not ds.EOF Then
    ds.MoveLast
    TblCnt = ds.RecordCount
    dum = SysCmd(1, "Attaching Tables...", TblCnt)
    TblCnt = 0
    ds.MoveFirst
    Do Until ds.EOF
      TableName = IIf(IsNull(ds!NameOfTable), "", ds!NameOfTable)
      DrivePathFile = IIf(IsNull(ds!DrivePathFile), "", ds!DrivePathFile)
      LastAttach = IIf(IsNull(ds!LastAttach), "", ds!LastAttach)
      SourceType = IIf(IsNull(ds!SourceType), "", ds!SourceType)
 
      If DrivePathFile <> LastAttach Then
        If Not AttachTable(DrivePathFile, SourceType, TableName) Then GoTo Error_AttachAllTables
        ds.Edit
        ds!LastAttach = DrivePathFile
        ds.Update
      End If
 
      TblCnt = TblCnt + 1
      dum = SysCmd(2, TblCnt)
      ds.MoveNext
    Loop
  End If
 
Exit_AttachAllTables:
  On Error Resume Next
  ds.Close
  db.Close
  DoCmd.Hourglass False
  dum = SysCmd(3)  'shutdown status bar
  AttachAllTables = retval
  Exit Function
 
Error_AttachAllTables:
  MsgBox "Failed to attach " & TableName & " in " & DrivePathFile & ".", w_MB_ICONSTOP, "Attach Failed"
  retval = False
  GoTo Exit_AttachAllTables
 
CannotOpenDB:
  Select Case Err
  Case 3049
    ' Corrupt file - fatal error!
    MsgBox App_Name & " needs to be repaired. Please try " & App_Name & " again later.", w_MB_ICONSTOP, "Open Database Failed"
    Notify = CNull(ds!IfCorruptNotify, "")
    'dum = SendMail(ds!DrivePathFile & " is corrupt and needs to be repaired.", Notify, "", "", "", "")
    DoCmd.Quit
  Case Else
    MsgBox Error$, w_MB_ICONINFORMATION, App_Name
    DrivePath = ""
    Resume Next
  End Select
 
  Exit Function
 
End Function
 
'''''-------------------------------------------------------------------------
' TITLE:   Attach Table
' AUTHOR:  Bob Hamilton
' DATE:    mm/dd/1993
' PURPOSE: To Attach a table
' DESCRIPTION:
'
'------------------------------------------------------------------------+++++
Function AttachTable(DBName$, SourceType$, TableName$) As Integer
  Dim retval%
 
  retval = False             ' assume failure
  On Error GoTo Exit_AttachTable
 
  dum = DetachTable(TableName)
  ' don't care if no detach because it may not have been attached
 
  Select Case SourceType
    Case "MSACCESS"
      DoCmd.TransferDatabase A_ATTACH, "Microsoft Access", DBName, A_TABLE, TableName, TableName, False
      retval = True              ' success
    Case "SQLSERVER"
      DoCmd.TransferDatabase A_ATTACH, "<SQL Database>", "ODBC;DSN=;UID=WhoRyou;;APP=Microsoft Access;DATABASE=" & DBName, A_TABLE, "dbo." & TableName, TableName
      retval = True              ' success
    Case Else
      retval = False
  End Select
 
Exit_AttachTable:
  AttachTable = retval
  Exit Function
End Function
 
'''''-------------------------------------------------------------------------
' TITLE:   Detach All Tables
' AUTHOR:  Bob Hamilton
' DATE:    mm/dd/1993
' PURPOSE: Detaches all attached tables
' DESCRIPTION:
'
'------------------------------------------------------------------------+++++
Function DetachAllTables() As Integer
  Dim db As Database
  Dim ss As Snapshot
 
  Set db = CurrentDb()
  Set ss = db.ListTables()
 
  ss.MoveFirst
  Do While Not ss.EOF
    If (ss!TableType = DB_ATTACHEDTABLE Or ss!TableType = DB_ATTACHEDODBC) And Left$(ss!Name, 4) <> "~TMP" Then
      DoCmd.SelectObject A_TABLE, ss!Name, True
      DoCmd.SetWarnings False
      ' domenuitem Database, Edit, Delete
      DoCmd.DoMenuItem 1, 1, 4
      DoCmd.SetWarnings True
    End If
    ss.MoveNext
  Loop
 
  DetachAllTables = True
End Function
 
'''''-------------------------------------------------------------------------
' TITLE:   Detach Table
' AUTHOR:  Dave Schuba
' DATE:    04/11/1994
' PURPOSE: Detach a table
' DESCRIPTION:
'------------------------------------------------------------------------+++++
Function DetachTable(TableName$) As Integer
  Dim retval%
 
  On Error GoTo Exit_DetachTable
 
  retval = True
 
  DoCmd.SelectObject A_TABLE, TableName, True
  DoCmd.SetWarnings False
  DoCmd.DoMenuItem 1, 1, 4                 ' Database, Edit, Delete
  DoCmd.SetWarnings True
 
Exit_DetachTable:
  DetachTable = retval
  Exit Function
End Function
 
'''''-------------------------------------------------------------------------
' TITLE:   Get Database spec
' AUTHOR:  djs
' DATE:    07/29/1993
' PURPOSE: Get spec from TableAttach
' DESCRIPTION: TableAttach should always be local
'------------------------------------------------------------------------+++++
Function GetDataBase(vId) As String
  Dim db As Database
  Dim tb As Table
 
  Set db = CurrentDb()
  Set tb = db.OpenTable("TableAttach")
  tb.Index = "PrimaryKey"
  tb.Seek "=", vId
  If tb.NoMatch Then
    GetDataBase = ""
  Else
    GetDataBase = IIf(IsNothing(tb!DrivePathFile), "", tb!DrivePathFile)
  End If
  tb.Close
  db.Close
 
End Function
 
'''''-------------------------------------------------------------------------
' TITLE:   Verifile
' AUTHOR:  djs
' DATE:    06/24/1993
' PURPOSE: To verify that Key exists in Tablename
' DESCRIPTION: CANNOT HANDLE MULTIPLE PART KEYS
'              CAN ONLY BE USED ON JET TABLES
' Same Usage: Exists% = Verifile(0,"Customer",100)
'------------------------------------------------------------------------+++++
Function Verifile(iTableAttachId As Integer, sTablename As String, vKey) As Integer
  Dim db As Database
  Dim tb As Table
  Dim iRet
  Dim sDBname As String
 
  iRet = True
 
  If IsNull(vKey) Then
    ' it won't be in the table if it is nothing
    iRet = False
  Else
    sDBname = GetDataBase(iTableAttachId)
    If Len(sDBname) And iTableAttachId > 0 Then
      Set db = OpenDatabase(sDBname)
    Else
      Set db = CurrentDb()
    End If
    Set tb = db.OpenTable(sTablename)
    tb.Index = "PrimaryKey"
    tb.Seek "=", vKey
    If tb.NoMatch Then
      iRet = False
    End If
    tb.Close
    db.Close
  End If
 
  Verifile = iRet
 
End Function

Sorry for all the code here, I realize that there is more than just the AttachAllTables function, but the rest could be cogent to the point also.

This macro/function refreshes the linked tables when the database is installed onto different computers. There are two tables in the front end .mdb file name TableDef and TableAttach. These files store the current path/location of the linked back end .mdd file as well as other information.
Their meta data look like this:

TableDef
NameOfTable Text PK (I know, I know not the usual AutoNumber field for primary key, but in this case and probably only in this case it works)
TableAttachID Number FK Identifier of where to attach this table
LastAttach Text Drive\Path\File where this table resides
Comments Text
OriginalSource Text
OnGoingSource Text
ResponsibleParties Text
UpdateFrequency Text

Each table in your backend needs to have it's own record in this table... TableAttachID will be 1 for backend tables and 0 for front end tables...

TableAttach
TableAttachID Number PK
SourceType Text Source Type: either MSACCESS or SQLSRVR
DrivePathFile Text example: E:\Databases\AddBk\AddrDataWA.mdd
Comments Text
IfCorruptNotify Text
DfltFile Text Default File name for when DrivePathFile is left blank.

In this table you only have two records: 0, for local tables, and 1 for backend tables to attach... Needless to say, DrivePathFile is only filled in for the second record.

Running this macro after changing the path in TableAttach to reflect changes to the location of the back end refreshes the links..

Not sure if this is what you're looking for, but thought I'd throw my 5th of a cent into the thing :-)

Regards,
Scott

[Scott Price]

Excuse me, should have specified that this is Access 2000 database format, I'm using it with Access 2003 without any problems.

Regards,
Scott

[NeoPa]

I've done it before. Delete the link and then do a TransferDatabase to relink a table from a different database. I did it because I was afraid people would keep moving the database around.

Sorry Rabbit. I just realised your post was a delete and relink - rather than a fix connection answer. This can work in a basic way, unfortunately most of my linked tables have local edits to them (there are some attributes of a linked table that can be set locally), so this would not be appropriate for them - Access OR ODBC. Never mind - it may well suit the OP.

[NeoPa]

Excuse me, should have specified that this is Access 2000 database format, I'm using it with Access 2003 without any problems.

Regards,
Scott

This is good stuff Scott.
This still works on the basic detach - reattach principle though. This should be clear for the OP so that she knows what it will and won't do.

Interestingly, from this code one could develop a system which used the existing connection information (rather than a separately maintained set of tables) to drive the reconnect.

[Scott Price]

This is good stuff Scott.
This still works on the basic detach - reattach principle though. This should be clear for the OP so that she knows what it will and won't do.

Interestingly, from this code one could develop a system which used the existing connection information (rather than a separately maintained set of tables) to drive the reconnect.

Thanks, NeoPa! The code for the AttachAllTables function doesn't appear to do any detaching first (just scanned it quickly again, so can't say definitively), but there is an added function to detach if so needed/desired. We use this in a small networked application (5 to 6 users at a time). Most of us working on this are laptop warriors, so we end up changing the network location of the commonly accessed backend every time we get together for the job. This code allows us to make one change in every frontend (to the tableattach drivepathfile field) then run the macro, and hey presto! Away we go...

Glad you liked it, but like I said, I really can't take credit for any of it! I didn't have any hand in developing that database, just maintenance and update...

Regards,
Scott

[Scott Price]

Just noticed something that bugged me last night! There IS a query (actually two) that work with these tables... I'll post the sql code here;

qryDbsToAttach

SELECT DISTINCTROW TableAttach.TableAttachId, TableAttach.SourceType, TableAttach.DrivePathFile, TableAttach.IfCorruptNotify, TableAttach.DfltFile
FROM TableAttach
WHERE (((TableAttach.TableAttachId)>0))
ORDER BY TableAttach.TableAttachId
WITH OWNERACCESS OPTION;

qryTablesToAttach

SELECT DISTINCTROW TableDef.NameOfTable, TableAttach.SourceType, TableAttach.DrivePathFile, TableDef.LastAttach, TableDef.TableAttachId
FROM TableDef INNER JOIN TableAttach ON TableDef.TableAttachId = TableAttach.TableAttachId
WHERE (((TableDef.TableAttachId)>0))
ORDER BY TableDef.NameOfTable
WITH OWNERACCESS OPTION;

Regards,
Scott

[NeoPa]

I may have misunderstood you Scott. Did you mean that the AttachAllTables works (changes the .Connect string) for a table even if the table is already linked?

[Scott Price]

I may have misunderstood you Scott. Did you mean that the AttachAllTables works (changes the .Connect string) for a table even if the table is already linked?

Didn't mean to say that if it came out sounding that way, no! The way we are using it, if the .connect string/link location is valid the function does nothing... However, if the attach location is broken, it will give an error message, and then re-attach the tables based on a manually updated backend location in TableAttach.

I only meant that inside the AttachAllTables function itself there does not appear to be any built in ability to detach before reattaching.

Interesting question, whether it would have the ability (with some added coding) to FIND the new backend location, and update automatically rather than manually as it stands now? Probably wouldn't be worth pursuing too far, though, as I think the demands of searching one hard drive, let alone a full LAN for the new location would be prohibitively time consuming...

Regards,
Scott

[puppydogbuddy]

I may have misunderstood you Scott. Did you mean that the AttachAllTables works (changes the .Connect string) for a table even if the table is already linked?

Adrian,
Noticed this question and thought you might be interested in the following code from the tip page of www.aadconsulting.com. I think this code is exactly what you and the OP were looking for:

Public Function RefreshLinks(strFilename As String) As Boolean
' Refresh table links to a backend database - strFilename (full path)
' Returns True if successful. 
 
___Dim dbs As Database
___Dim tdf As TableDef
 
___' Loop through all tables in the database.
___Set dbs = CurrentDb
______For Each tdf In dbs.TableDefs
_________' If the table has a connect string, it's a linked table.
_________If Len(tdf.Connect) > 0 Then
____________tdf.Connect = ";DATABASE=" & strFilename
____________Err = 0
____________On Error Resume Next
____________tdf.RefreshLink ' Relink the table.
_______________ If Err <> 0 Then
__________________RefreshLinks = False
__________________Exit Function
_______________ End If
_________End If
______Next tdf
 
___RefreshLinks = True ' Relinking complete.
 
End Function

[NeoPa]

Sorry for the long delay guys.
This looks interesting PDB. I'll check it out and let you know what I come up with :)

[NeoPa]

Sorry for the long delay guys.
This looks interesting PDB. I'll check it out and let you know what I come up with :)

Nice one Richard.
So the basic concept is first to change the tdf.Connect string (set it up as you require it to be), then issue a call to tdf.RefreshLink which applies the new Connect string.
That could definitely prove useful :)

[Rabbit]

Sorry Rabbit. I just realised your post was a delete and relink - rather than a fix connection answer. This can work in a basic way, unfortunately most of my linked tables have local edits to them (there are some attributes of a linked table that can be set locally), so this would not be appropriate for them - Access OR ODBC. Never mind - it may well suit the OP.

I forgot all about this.

You can reset the attributes after deleting and reconnecting.
It took a lot of trial and error for me to get this working but I used this, after deleting and relinking the table, to set up the relationships.

    Dim rel As Relation
    Dim fld As Field
    Set rel = CurrentDb.CreateRelation("Table1Table2", "Table1", "Table2", 4352)
    Set fld = rel.CreateField("ID1")
    rel.Fields.Append fld
    rel.Fields("ID1").ForeignName = "ID1"
    CurrentDb.Relations.Append rel
 
    Dim rel As Relation
    For Each rel In CurrentDb.Relations
        Debug.Print rel.Name & "=" & rel.Attributes
    Next rel
 

This was a while ago and it took be the better part of the day to get it working correctly. The first part, if I remember correctly, creates a one-to-many relationship that has referential integrity and cascades updates and deletes.

The second part I used to list all the attributes of a relationship so I could see what I was dealing with before I started messing around with it.

[puppydogbuddy]

Nice one Richard.
So the basic concept is first to change the tdf.Connect string (set it up as you require it to be), then issue a call to tdf.RefreshLink which applies the new Connect string.
That could definitely prove useful :)

Adrian,
More specifically, it loops through all the tabledefs in the back end DB looking for ones that have connect strings (any table that has a connect string is, by definition, a linked table) and uses the RefreshLink method to apply the new connect string. I think it is great piece of code by Tony D of aadconsulting.

pDog

[NeoPa]

Adrian,
More specifically, it loops through all the tabledefs in the back end DB looking for ones that have connect strings (any table that has a connect string is, by definition, a linked table) and uses the RefreshLink method to apply the new connect string. I think it is great piece of code by Tony D of aadconsulting.

pDog

You're right of course, but in most of my databases I have multiple linked back-ends. Including, but not restricted to, Access DBs. I would not want to specify a single back-end DB for all my linked tables, but I would (and will) happily use the concept to switch a certain group of tables from a test back-end to a live one (for instance). Now I know the concept the usages needn't be restricted to a global change, however useful that certainly can be.

As you've probably gathered from an earlier post in here, I'd already tried simply changing the .Connect string. What I was missing before was the .RefreshLink part of the equation. So you can guess how pleased I am to have the solution (I'll have to run some tests of course, but I expect it to work).

[NeoPa]

I forgot all about this.

You can reset the attributes after deleting and reconnecting.
It took a lot of trial and error for me to get this working but I used this, after deleting and relinking the table, to set up the relationships.
...

I appreciate your answer Rabbit (and any work put into it), but I have to admit to preferring the more "appropriate" concept of redirecting the connection rather than trying to recreate something that needn't be deleted in the first place. If it were necessary it may be worth the extra effort (relationships are only one of many possible elements that may need to be recreated I'm afraid), but seeing as there is a more "direct" alternative I feel that is more what I'm looking for.
Thanks to you all for your suggestions though. I hope, like me, you get to pick up some extra ideas after trying to help in these forums :)

[Scott Price]

Thanks to you all for your suggestions though. I hope, like me, you get to pick up some extra ideas after trying to help in these forums :)

As for me, I find that is the primary benefit of trying to help out!! I have learned far more myself by trying to help than I've learned by asking questions :-)

Like with this thread, I've found now two other possible ways to accomplish a linked table refresh... And, will probably try one of the other approaches when I get to that point in the development of this current db I'm working on

Regards,
Scott

[NeoPa]

Becky (OP),

Did you find your question answered?
I'm not sure if any of this discussion went over your head. If so, just let us know and we'll explain. I think your question is answered in here.

-NeoPa.

[FishVal]

Just subscribing.