1

I have a linked table, tblREDEIMPORT that is set to a specific path that's only accessible programatically through a FSO import process where it overwrites the previous day's version.

However, while the .xls linked file is always named the same thing, the name of the sheet that it's on changes every day giving me an error like 215380_REDEFILEIMPORTREPORT_230$ is not a valid name, because that was yesterdays sheet name, todays sheetname has a completely different set of numbers before and after.

Through the linked table manager, I'm unable to point the linked table to anything but that sheet. How can I change the linked table path to either always look at the first (and only) spreadsheet in the workbook, or at least change it to dynamically update the name to the worksheet it should be pointing at?

Smandoli
  • 6,919
  • 3
  • 49
  • 83
Seth E
  • 165
  • 2
  • 3
  • 11
  • 1
    You can re-create Linked tables via VBA, which will allow dynamic changing of things like worksheet names. Something like this (sample from a tool I made ages ago) DoCmd.TransferDatabase acLink, "Microsoft Access", fileloc, , tablename, "DB_OCIE" – Alan Waage Oct 24 '13 at 17:09
  • I did not know acLink was a thing, This should help, I'll give it a shot. Thank you! – Seth E Oct 24 '13 at 17:31

1 Answers1

4

As an alternative to DoCmd.TransferSpreadsheet acLink, ... you can simply "clone" the existing TableDef object, tweak the .SourceTableName property, and swap the updated TableDef object for the existing one. This approach would have the advantage of preserving the existing file location, Excel document type, etc., saving you from the temptation of hard-coding those values into the DoCmd.TransferSpreadsheet statement.

For example, I have a linked table in Access named [LinkedTableInExcel] that points to a sheet named OldSheetName in an Excel document. I can verify that the linked table is working by using a DCount() expression in the VBA Immediate Window

?DCount("*","LinkedTableInExcel")
 2

Now if I open the document in Excel and change the sheet name to NewSheetName the linked table in Access stops working

BadSheetName.png

However, I can update the linked table as follows

Sub UpdateExcelLinkedTable()
Dim cdb As DAO.Database
Dim tbd As DAO.TableDef, tbdNew As DAO.TableDef
Dim n As Long
Const LinkedTableName = "LinkedTableInExcel"
Set cdb = CurrentDb

Set tbd = cdb.TableDefs(LinkedTableName)
Debug.Print "Current .SourceTableName is: " & tbd.SourceTableName
On Error Resume Next
n = DCount("*", LinkedTableName)
Debug.Print "The linked table is " & IIf(Err.Number = 0, "", "NOT ") & "working."
On Error GoTo 0

Set tbdNew = New DAO.TableDef
tbdNew.Name = tbd.Name
tbdNew.Connect = tbd.Connect
tbdNew.SourceTableName = "NewSheetName$"
Set tbd = Nothing
cdb.TableDefs.Delete LinkedTableName
cdb.TableDefs.Append tbdNew
Set tbdNew = Nothing

Set tbd = cdb.TableDefs(LinkedTableName)
Debug.Print "Updated .SourceTableName is: " & tbd.SourceTableName
On Error Resume Next
n = DCount("*", LinkedTableName)
Debug.Print "The linked table is " & IIf(Err.Number = 0, "", "NOT ") & "working."
On Error GoTo 0

Set tbd = Nothing
Set cdb = Nothing
End Sub

results:

Current .SourceTableName is: OldSheetName$
The linked table is NOT working.
Updated .SourceTableName is: NewSheetName$
The linked table is working.
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    I had to work the code a bit. I stored `tbd.SourceTableName` in a variable so I could reuse it when declaring `tbdNew.SourceTableName`. I may not have understood everything fully. All that said --- VERY HELPFUL and THANKS. – Smandoli Jan 14 '14 at 23:28