By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Linked Table Manager Macro

bwesenberg
P: 17
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
Aug 6 '07 #1
Share this Question
Share on Google+
24 Replies


Rabbit
Expert Mod 10K+
P: 12,389
Why would you need to refresh the links? People moving the tables all the time?
Aug 6 '07 #2

NeoPa
Expert Mod 15k+
P: 31,660
Are you talking about the RecordSource of the form or some other tables?
What changes are you anticipating that would require the Requery?
Aug 6 '07 #3

NeoPa
Expert Mod 15k+
P: 31,660
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 :)
Aug 6 '07 #4

Rabbit
Expert Mod 10K+
P: 12,389
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.
Aug 6 '07 #5

NeoPa
Expert Mod 15k+
P: 31,660
Does that work for ODBC links too?
Aug 7 '07 #6

Rabbit
Expert Mod 10K+
P: 12,389
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.
Aug 7 '07 #7

Scott Price
Expert 100+
P: 1,384
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:
Expand|Select|Wrap|Line Numbers
  1. '~~~~~------------------------------------------------------------------------
  2. ' MODULE:      Attach Table Lib
  3. ' AUTHOR:      Curtis Consulting Group
  4. ' DATE:        08/02/1993
  5. ' PURPOSE:     To Attach and Detach Tables
  6. ' DESCRIPTION: Uses Tables: TableDef and TableAttach
  7. '              Uses Queries: qryTablesToAttach, qryDBsToAttach
  8. '              Uses Modules: MailLib (Send mail if corrupt)
  9. '              Uses APP_NAME constant for msgboxes
  10. '
  11. ' MODIFICATIONS (MOST RECENT ON TOP)
  12. 'DATE    -Function-INI-CHANGE
  13. '04/11/94 DetachTable-djs-rewrote
  14. '03/xx/94 AttachAllTables and AttachTable-kdh-mod for SQL Server
  15. '01/20/94 Verifile-djs-moved from StdLib module
  16. '01/20/94 AttachAllTables-simplified error message
  17. '12/07/93 AttachAllTables-elm-add drive/path/file to error message
  18. '12/02/93 AttachAllTables-djs-prompt user for path if DrivePathFile is blank
  19. '11/02/93 AttachAllTables-djs-test opening each db - requires qryDBsToAttach and new field in TableAttach
  20. '------------------------------------------------------------------------+++++
  21. Option Compare Database   'Use database order for string comparisons
  22. Option Explicit
  23.  
  24. Dim dum
  25.  
  26. '''''-------------------------------------------------------------------------
  27. ' TITLE:   Attach All Tables
  28. ' AUTHOR:  D.J. Schuba
  29. ' DATE:    08/02/1993
  30. ' PURPOSE: To attach all tables according to TableDef and TableAttach
  31. ' DESCRIPTION:
  32. '
  33. '------------------------------------------------------------------------+++++
  34. Function AttachAllTables() As Integer
  35.   Dim db As Database, dbTest As Database, ds As Recordset
  36.   Dim DrivePathFile$, DrivePath$, TableName$, LastAttach$, Notify$
  37.   Dim retval%, SourceType$
  38.   Dim TblCnt
  39.  
  40.   DoCmd.Hourglass True
  41.   retval = True
  42.  
  43.   ' Test open all databases
  44.   On Error GoTo CannotOpenDB
  45.   Set db = CurrentDb()
  46.   Set ds = db.OpenRecordset("qryDBsToAttach")
  47.   If Not ds.EOF Then
  48.     Do Until ds.EOF
  49.       If IsNull(ds!DrivePathFile) Then
  50.         ' Need to ask user where the data is
  51.         Do
  52.           If Len(DrivePath) = 0 Then
  53.             DrivePath = InputBox$("Enter the drive\path for " & ds!DfltFile & ". Please use the universal naming convention for network drives (e.g., \\serverx\sharey ).", App_Name)
  54.           End If
  55.  
  56.           If Len(DrivePath) = 0 Then
  57.             ' the user did not even try so quit
  58.             DoCmd.Quit
  59.           Else
  60.             If Right(DrivePath, 1) = "\" Then
  61.               DrivePathFile = DrivePath & ds!DfltFile
  62.             Else
  63.               DrivePathFile = DrivePath & "\" & ds!DfltFile
  64.             End If
  65.             Set dbTest = OpenDatabase(DrivePathFile)
  66.             If Len(DrivePath) Then
  67.               ds.Edit
  68.               ds!DrivePathFile = DrivePathFile
  69.               ds.Update
  70.             End If
  71.           End If
  72.         Loop Until Len(DrivePath)
  73.  
  74.       Else
  75.         ' test open for corruption only for MS Access Tables
  76.         If ds!SourceType = "MSACCESS" Then
  77.           Set dbTest = OpenDatabase(ds!DrivePathFile)
  78.         End If
  79.       End If
  80.  
  81.       ds.MoveNext
  82.     Loop
  83.   End If
  84.   ds.Close
  85.  
  86.   ' Now let's see if everything is attached correctly
  87.   On Error GoTo 0
  88.   Set ds = db.OpenRecordset("qryTablesToAttach")
  89.  
  90.   If Not ds.EOF Then
  91.     ds.MoveLast
  92.     TblCnt = ds.RecordCount
  93.     dum = SysCmd(1, "Attaching Tables...", TblCnt)
  94.     TblCnt = 0
  95.     ds.MoveFirst
  96.     Do Until ds.EOF
  97.       TableName = IIf(IsNull(ds!NameOfTable), "", ds!NameOfTable)
  98.       DrivePathFile = IIf(IsNull(ds!DrivePathFile), "", ds!DrivePathFile)
  99.       LastAttach = IIf(IsNull(ds!LastAttach), "", ds!LastAttach)
  100.       SourceType = IIf(IsNull(ds!SourceType), "", ds!SourceType)
  101.  
  102.       If DrivePathFile <> LastAttach Then
  103.         If Not AttachTable(DrivePathFile, SourceType, TableName) Then GoTo Error_AttachAllTables
  104.         ds.Edit
  105.         ds!LastAttach = DrivePathFile
  106.         ds.Update
  107.       End If
  108.  
  109.       TblCnt = TblCnt + 1
  110.       dum = SysCmd(2, TblCnt)
  111.       ds.MoveNext
  112.     Loop
  113.   End If
  114.  
  115. Exit_AttachAllTables:
  116.   On Error Resume Next
  117.   ds.Close
  118.   db.Close
  119.   DoCmd.Hourglass False
  120.   dum = SysCmd(3)  'shutdown status bar
  121.   AttachAllTables = retval
  122.   Exit Function
  123.  
  124. Error_AttachAllTables:
  125.   MsgBox "Failed to attach " & TableName & " in " & DrivePathFile & ".", w_MB_ICONSTOP, "Attach Failed"
  126.   retval = False
  127.   GoTo Exit_AttachAllTables
  128.  
  129. CannotOpenDB:
  130.   Select Case Err
  131.   Case 3049
  132.     ' Corrupt file - fatal error!
  133.     MsgBox App_Name & " needs to be repaired. Please try " & App_Name & " again later.", w_MB_ICONSTOP, "Open Database Failed"
  134.     Notify = CNull(ds!IfCorruptNotify, "")
  135.     'dum = SendMail(ds!DrivePathFile & " is corrupt and needs to be repaired.", Notify, "", "", "", "")
  136.     DoCmd.Quit
  137.   Case Else
  138.     MsgBox Error$, w_MB_ICONINFORMATION, App_Name
  139.     DrivePath = ""
  140.     Resume Next
  141.   End Select
  142.  
  143.   Exit Function
  144.  
  145. End Function
  146.  
  147. '''''-------------------------------------------------------------------------
  148. ' TITLE:   Attach Table
  149. ' AUTHOR:  Bob Hamilton
  150. ' DATE:    mm/dd/1993
  151. ' PURPOSE: To Attach a table
  152. ' DESCRIPTION:
  153. '
  154. '------------------------------------------------------------------------+++++
  155. Function AttachTable(DBName$, SourceType$, TableName$) As Integer
  156.   Dim retval%
  157.  
  158.   retval = False             ' assume failure
  159.   On Error GoTo Exit_AttachTable
  160.  
  161.   dum = DetachTable(TableName)
  162.   ' don't care if no detach because it may not have been attached
  163.  
  164.   Select Case SourceType
  165.     Case "MSACCESS"
  166.       DoCmd.TransferDatabase A_ATTACH, "Microsoft Access", DBName, A_TABLE, TableName, TableName, False
  167.       retval = True              ' success
  168.     Case "SQLSERVER"
  169.       DoCmd.TransferDatabase A_ATTACH, "<SQL Database>", "ODBC;DSN=;UID=WhoRyou;;APP=Microsoft Access;DATABASE=" & DBName, A_TABLE, "dbo." & TableName, TableName
  170.       retval = True              ' success
  171.     Case Else
  172.       retval = False
  173.   End Select
  174.  
  175. Exit_AttachTable:
  176.   AttachTable = retval
  177.   Exit Function
  178. End Function
  179.  
  180. '''''-------------------------------------------------------------------------
  181. ' TITLE:   Detach All Tables
  182. ' AUTHOR:  Bob Hamilton
  183. ' DATE:    mm/dd/1993
  184. ' PURPOSE: Detaches all attached tables
  185. ' DESCRIPTION:
  186. '
  187. '------------------------------------------------------------------------+++++
  188. Function DetachAllTables() As Integer
  189.   Dim db As Database
  190.   Dim ss As Snapshot
  191.  
  192.   Set db = CurrentDb()
  193.   Set ss = db.ListTables()
  194.  
  195.   ss.MoveFirst
  196.   Do While Not ss.EOF
  197.     If (ss!TableType = DB_ATTACHEDTABLE Or ss!TableType = DB_ATTACHEDODBC) And Left$(ss!Name, 4) <> "~TMP" Then
  198.       DoCmd.SelectObject A_TABLE, ss!Name, True
  199.       DoCmd.SetWarnings False
  200.       ' domenuitem Database, Edit, Delete
  201.       DoCmd.DoMenuItem 1, 1, 4
  202.       DoCmd.SetWarnings True
  203.     End If
  204.     ss.MoveNext
  205.   Loop
  206.  
  207.   DetachAllTables = True
  208. End Function
  209.  
  210. '''''-------------------------------------------------------------------------
  211. ' TITLE:   Detach Table
  212. ' AUTHOR:  Dave Schuba
  213. ' DATE:    04/11/1994
  214. ' PURPOSE: Detach a table
  215. ' DESCRIPTION:
  216. '------------------------------------------------------------------------+++++
  217. Function DetachTable(TableName$) As Integer
  218.   Dim retval%
  219.  
  220.   On Error GoTo Exit_DetachTable
  221.  
  222.   retval = True
  223.  
  224.   DoCmd.SelectObject A_TABLE, TableName, True
  225.   DoCmd.SetWarnings False
  226.   DoCmd.DoMenuItem 1, 1, 4                 ' Database, Edit, Delete
  227.   DoCmd.SetWarnings True
  228.  
  229. Exit_DetachTable:
  230.   DetachTable = retval
  231.   Exit Function
  232. End Function
  233.  
  234. '''''-------------------------------------------------------------------------
  235. ' TITLE:   Get Database spec
  236. ' AUTHOR:  djs
  237. ' DATE:    07/29/1993
  238. ' PURPOSE: Get spec from TableAttach
  239. ' DESCRIPTION: TableAttach should always be local
  240. '------------------------------------------------------------------------+++++
  241. Function GetDataBase(vId) As String
  242.   Dim db As Database
  243.   Dim tb As Table
  244.  
  245.   Set db = CurrentDb()
  246.   Set tb = db.OpenTable("TableAttach")
  247.   tb.Index = "PrimaryKey"
  248.   tb.Seek "=", vId
  249.   If tb.NoMatch Then
  250.     GetDataBase = ""
  251.   Else
  252.     GetDataBase = IIf(IsNothing(tb!DrivePathFile), "", tb!DrivePathFile)
  253.   End If
  254.   tb.Close
  255.   db.Close
  256.  
  257. End Function
  258.  
  259. '''''-------------------------------------------------------------------------
  260. ' TITLE:   Verifile
  261. ' AUTHOR:  djs
  262. ' DATE:    06/24/1993
  263. ' PURPOSE: To verify that Key exists in Tablename
  264. ' DESCRIPTION: CANNOT HANDLE MULTIPLE PART KEYS
  265. '              CAN ONLY BE USED ON JET TABLES
  266. ' Same Usage: Exists% = Verifile(0,"Customer",100)
  267. '------------------------------------------------------------------------+++++
  268. Function Verifile(iTableAttachId As Integer, sTablename As String, vKey) As Integer
  269.   Dim db As Database
  270.   Dim tb As Table
  271.   Dim iRet
  272.   Dim sDBname As String
  273.  
  274.   iRet = True
  275.  
  276.   If IsNull(vKey) Then
  277.     ' it won't be in the table if it is nothing
  278.     iRet = False
  279.   Else
  280.     sDBname = GetDataBase(iTableAttachId)
  281.     If Len(sDBname) And iTableAttachId > 0 Then
  282.       Set db = OpenDatabase(sDBname)
  283.     Else
  284.       Set db = CurrentDb()
  285.     End If
  286.     Set tb = db.OpenTable(sTablename)
  287.     tb.Index = "PrimaryKey"
  288.     tb.Seek "=", vKey
  289.     If tb.NoMatch Then
  290.       iRet = False
  291.     End If
  292.     tb.Close
  293.     db.Close
  294.   End If
  295.  
  296.   Verifile = iRet
  297.  
  298. 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
Aug 7 '07 #8

Scott Price
Expert 100+
P: 1,384
Excuse me, should have specified that this is Access 2000 database format, I'm using it with Access 2003 without any problems.

Regards,
Scott
Aug 7 '07 #9

NeoPa
Expert Mod 15k+
P: 31,660
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.
Aug 7 '07 #10

NeoPa
Expert Mod 15k+
P: 31,660
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.
Aug 7 '07 #11

Scott Price
Expert 100+
P: 1,384
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
Aug 7 '07 #12

Scott Price
Expert 100+
P: 1,384
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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW TableAttach.TableAttachId, TableAttach.SourceType, TableAttach.DrivePathFile, TableAttach.IfCorruptNotify, TableAttach.DfltFile
  2. FROM TableAttach
  3. WHERE (((TableAttach.TableAttachId)>0))
  4. ORDER BY TableAttach.TableAttachId
  5. WITH OWNERACCESS OPTION;
qryTablesToAttach
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW TableDef.NameOfTable, TableAttach.SourceType, TableAttach.DrivePathFile, TableDef.LastAttach, TableDef.TableAttachId
  2. FROM TableDef INNER JOIN TableAttach ON TableDef.TableAttachId = TableAttach.TableAttachId
  3. WHERE (((TableDef.TableAttachId)>0))
  4. ORDER BY TableDef.NameOfTable
  5. WITH OWNERACCESS OPTION;
Regards,
Scott
Aug 7 '07 #13

NeoPa
Expert Mod 15k+
P: 31,660
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?
Aug 7 '07 #14

Scott Price
Expert 100+
P: 1,384
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
Aug 7 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function RefreshLinks(strFilename As String) As Boolean
  2. ' Refresh table links to a backend database - strFilename (full path)
  3. ' Returns True if successful. 
  4.  
  5. ___Dim dbs As Database
  6. ___Dim tdf As TableDef
  7.  
  8. ___' Loop through all tables in the database.
  9. ___Set dbs = CurrentDb
  10. ______For Each tdf In dbs.TableDefs
  11. _________' If the table has a connect string, it's a linked table.
  12. _________If Len(tdf.Connect) > 0 Then
  13. ____________tdf.Connect = ";DATABASE=" & strFilename
  14. ____________Err = 0
  15. ____________On Error Resume Next
  16. ____________tdf.RefreshLink ' Relink the table.
  17. _______________ If Err <> 0 Then
  18. __________________RefreshLinks = False
  19. __________________Exit Function
  20. _______________ End If
  21. _________End If
  22. ______Next tdf
  23.  
  24. ___RefreshLinks = True ' Relinking complete.
  25.  
  26. End Function
Aug 11 '07 #16

NeoPa
Expert Mod 15k+
P: 31,660
Sorry for the long delay guys.
This looks interesting PDB. I'll check it out and let you know what I come up with :)
Aug 20 '07 #17

NeoPa
Expert Mod 15k+
P: 31,660
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 :)
Aug 20 '07 #18

Rabbit
Expert Mod 10K+
P: 12,389
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim rel As Relation
  2.     Dim fld As Field
  3.     Set rel = CurrentDb.CreateRelation("Table1Table2", "Table1", "Table2", 4352)
  4.     Set fld = rel.CreateField("ID1")
  5.     rel.Fields.Append fld
  6.     rel.Fields("ID1").ForeignName = "ID1"
  7.     CurrentDb.Relations.Append rel
  8.  
  9.     Dim rel As Relation
  10.     For Each rel In CurrentDb.Relations
  11.         Debug.Print rel.Name & "=" & rel.Attributes
  12.     Next rel
  13.  
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.
Aug 20 '07 #19

puppydogbuddy
Expert 100+
P: 1,923
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
Aug 20 '07 #20

NeoPa
Expert Mod 15k+
P: 31,660
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).
Aug 21 '07 #21

NeoPa
Expert Mod 15k+
P: 31,660
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 :)
Aug 21 '07 #22

Scott Price
Expert 100+
P: 1,384
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
Aug 21 '07 #23

NeoPa
Expert Mod 15k+
P: 31,660
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.
Aug 21 '07 #24

FishVal
Expert 2.5K+
P: 2,653
Just subscribing.
Sep 18 '07 #25

Post your reply

Sign in to post your reply or Sign up for a free account.