Update linked table details using VBA

Question

I want to be able to update the location of an Excel worksheet linked to a database table by entering the new path in a form's textbox and then excuting whatever VBA code is necessary to update the link (I don't want users having to access the Linked Table Manager and other Menus etc.) I know how to provide the means for Users to use a dialog box to select the file path, its the VBA code needed to then amend the location as you would normally do in the Linked Table Manager that I need. Is this possible please?

Answer 1

Hi grimwadec,

There's no such function as ELookup(). In Access, it's DLookup().

Also, since all you're doing is changing the path to the file, you don't need to specify the worksheet, since you had to specify it when you first connected. Just set the Connect property to the path and filename.

"grimwadec" wrote in message news:*** Email address is removed for privacy *** .com...

OK so here's the code I tried:

Dim tdf As DAO.TableDef, ExcelPath As String

ExcelPath = ELookup("ExcelPath", "tblSundries", , "ExcelPath DESC")

Set tdf = DBEngine(0)(0).TableDefs("tblClubsSrc")

tdf.Connect = Left(tdf.Connect, InStr(1, tdf.Connect, "DATABASE=") + 8) & (ExcelPath & "\Clubs$")tdf.RefreshLink

Set tdf = Nothing

The worksheet in EntrySheet.xls is called "Clubs". I get Run-time error 3044 message*"C:\TrialFolder\EntrySheet.xls\Clubs$"* is not a valid path but I note that the preceding italicised text matches what I achieve using the Linked Table Manager which reflectstblClubsSrc  (C:\TrialFolder\EntrySheet.xls\Clubs$)

so I presume there is something wrong with the way I have put the ExcelPath and the Worksheet name together. Perhaps if I had tried to solve a problem I previously had using GetExcel and Detect Excel I might have been able to solve this issue! Help please!

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 2

Hi grimwadec,

The code is as follows:

Dim tdf As DAO.TableDef

Set tdf = DBEngine(0)(0).TableDefs("mytablename")

tdf.Connect = left(tdf.Connect,instr(1,tdf.Connect,"DATABASE=")+8) & strMyNewPath

tdf.RefreshLink

Set tdf = Nothing

"grimwadec" wrote in message news:*** Email address is removed for privacy *** .com...

I want to be able to update the location of an Excel worksheet linked to a database table by entering the new path in a form's textbox and then excuting whatever VBA code is necessary to update the link (I don't want users having to access the Linked Table Manager and other Menus etc.) I know how to provide the means for Users to use a dialog box to select the file path, its the VBA code needed to then amend the location as you would normally do in the Linked Table Manager that I need. Is this possible please?

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 3

If you still don't feel confident with loops or DAO, or whatever, try asking a new question. That's what we're here for.

"grimwadec" wrote in message news:*** Email address is removed for privacy *** .com...

Thanks Graham...will try it and let you know (that it works presumably) ...I asked a question some time ago re Loops stating I knew so little about them and one of your fellow MVP's told me that my shortcoming was in DAO' s not loops...I tried to pick up some knowledge of them but a good source of learning about them eludes me.

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 4

Thanks Graham...will try it and let you know (that it works presumably) ...I asked a question some time ago re Loops stating I knew so little about them and one of your fellow MVP's told me that my shortcoming was in DAO' s not loops...I tried to pick up some knowledge of them but a good source of learning about them eludes me.

Answer 5

You need to make two calls on the tabledef object: set the Connect property, and call the RefreshLink method.

---

-Tom. Microsoft Access MVP