1

I have some VBA that programmatically updates the location of linked tables so that the front end of my database can be easily pointed at a different back end, without needing to click around in the Linked Table Manager. The technique is explained in this Microsoft blog post. This should be as simple as running the following code for each linked table in CurrentDb.TableDefs:

tableDef.Connect = ";DATABASE=" & newBackEndPath
tableDef.RefreshLink

However, when the table contains a multi-valued field, Access provides the following unhelpful and misleading error message:

Run-time error '3125':

'[table name]' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

The same error appears when manually updating the links from the Linked Table Manager, although sometimes after a few attempts, the update will work without an error message. To work around the problem, I'm trying to refresh the link by creating a new TableDef and then renaming it over the old one:

Dim tdf As TableDef
Set tdf = CurrentDb.CreateTableDef(originalTableName & "_Copy")
tdf.Connect = ";DATABASE=" & newBackEndPath
tdf.SourceTableName = tableName
With CurrentDb.TableDefs
    .Append tdf
    .Refresh              ' Required?
End With
    RefreshDatabaseWindow ' Required?
    DoEvents              ' Required?
DoCmd.Rename originalTableNameName, acTable, originalTableNameName & "_Copy"

This usually works, but sometimes DoCmd.Rename throws the following run-time error:

Run-time error '7874':

Microsoft Access can't find the object '[table name]_Copy'.

If I enter the debugger, this usually triggers some kind of refresh and the _Copy table appears in the navigation pane. If I press F5 at this point, DoCmd.Rename executes with no problems. Clearly, there is some delay after the TableDef is appended before it can actually be found and renamed. I've added the TableDefs.Refresh, RefreshDatabaseWindow and DoEvents lines in an attempt to force the new table to appear, but they don't seem to help. Even forcing a delay of a few seconds doesn't seem to work, but somehow, opening the debugger does.

Is there any reliable way to refresh a linked table that contains a multi-valued field?

(I ignored the advice to avoid using multi-valued fields when I started this project, because I wanted users to be able to filter on multi-valued fields using the Quick Filters on a split form datasheet. This function is very helpful, but I'm beginning to wish I just built my own filtering UI after encountering this and numerous other bugs in the implementation of multi-valued fields.)

Community
  • 1
  • 1
sjy
  • 2,702
  • 1
  • 21
  • 22
  • Frankly I would redesign the application instead of fighting with glitches of this buggy feature, you will save time. – Sergey S. Apr 25 '18 at 04:18
  • Believe me, I have considered it! But it would be a huge amount of work to reproduce all the features of the split form. I would have to create a continuous form that allows users to toggle, rearrange, resize and filter each column. When the time comes to replace this prototype with something more robust, I intend to abandon Access and build a proper desktop application. – sjy Apr 25 '18 at 08:48
  • Why you need to reproduce features of split/datasheet forms? Just use a popup form for editing multi values by double click on field, which shows the list of values. In this case you will have even more flexibility than built-in editor of multi valued field – Sergey S. Apr 25 '18 at 09:44
  • Because I want those features! My users need to filter the datasheet to find rows with properties like ‘multi-valued field A has values J or K, and multi-valued field B has values X or Y.’ Quick filters make it very easy for them to do this and see intermediate results as they build the query. They can then hide unneeded columns and change the column width and order, or row height, in a familiar Excel-style interface. It would be hard to reproduce this with a series of subforms and popups. – sjy Apr 25 '18 at 13:46
  • I seem to have resolved the problem by correcting an issue where `CurrentDb` was used to refer to the front end after running `OpenDatabase` to get a reference to the back end. If you've stumbled across this question, my assumption that the problem was related to multi-valued fields may not actually be correct ... – sjy Apr 30 '18 at 05:30

1 Answers1

1

You could try a different approach altogether, by using DoCmd.TransferDatabase:

DoCmd.TransferDatabase acLink, "Microsoft Access", newBackEndPath, acTable, tableName, tableName

DoCmd.TransferDatabase is similar to using the GUI to link the table. It handles steps like refreshing the database pane for you. I usually recommend using TableDefs instead, but this situation might be an exception.

Of course, I support Sergey's advice to not use a multi-valued field. I'm a fan of the user experience of multi-valued fields and the associated comboboxes, but it has far-going consequences to use a MVF, like not being able to migrate your database to SQL server in the future

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thanks for the suggestion! Unfortunately, it has the same problem – I should have mentioned in the question that I had already tried `DoCmd.TransferDatabase`. I'd check the exact error message, but somehow my database has ended up (temporarily) in a state where the relinking process works with any method, so I can't reproduce the problem right now. The joys of Access! – sjy Apr 25 '18 at 08:37