Changing worksheet in linked table definition

[rjp11]

Hi,

I've got a database with many linked tables (from Excel workbooks with multiple worksheets). Recently the names of the worksheet tabs in the source data has changed. I want to update the linked table definition to refer to the new names without having to delete/recreate them.

I can't find a way to do this via the UI so I've been trying to do it programatically.

The nearest I've got is:

CurrentDb.CreateTableDef(sSheetName).SourceTableName = sNewName

...but this gives the error:

Run-time error '3268':
Cannot set this property once the object is part of a collection

...I'm guessing that the property is read only and this can't be done but if anyone knows of a way of doing this then you'd be saving me a nasty repetitive manual process and have my gratitude!!

Rob...

 

[gemma-the-husky]

as you already have created the table defs, then the syntacx is probably just

CurrentDb.TableDefs(sSheetName).SourceTableName = sNewName

 

[rjp11]

Unfortunately I've tried that but I get the error I mentioned in my original post.

 

[rjp11]

Ok, I've found the solution. The property is read-only so you have to delete the original linked table and recreate with the new worksheet name.

Here's the code in case anyone else has the same problem:

Sub RecreateLink(sCurrName As String, sSheetName As String)

    Dim tdfNew As TableDef

    'Copy existing linked table
    Set tdfNew = CurrentDb.CreateTableDef(sCurrName)
    tdfNew.Connect = CurrentDb.TableDefs(sCurrName).Connect

    'Change the source worksheet in the copy
    tdfNew.SourceTableName = sSheetName & "$"

    'Delete the original link
    DoCmd.DeleteObject acTable, sCurrName

    'Create the new link
    CurrentDb.TableDefs.Append tdfNew

End Sub

...where sCurrName is the original linked table name and sSheetName is the name of the new worksheet to point at within the new workbook.