Table field as file path in vba

[Danny_H]

Hi

I need to use a table field as a file path rather than typing the exact text in the vba

I need this as the file path needs to be able to be easily updated with an update query rather than having to keep going into the vba and altering it manually

Currently my sheet path is:

MySheetPath = "S:\Global_Purchasing1\Global Purchasing UK (D Blood)\Purchasing Exec Operations UK (A. Swan)\General Machining\PSSU Supplier Dev & Qual\SIG\Sabre Self Asses Compliance\SABRe deployment governance PSSU - REV 2.xlsx"

What I need it to be is:

MySheetPath = "Tables!Tbl_Tracker_Link_Details!Tracker_Link"

However when I use this I am getting an error box saying"Automation Error, Invalid syntax"

Help much Appreciated

 

[mdlueck]

I am not familiar with your proposed syntax of reading from a DB table via ! type syntax and going string to where you hope to get the value from as if it is a spreadsheet cell.

Since you say you need to read a previously stored value, in database lingo that is a SELECT statement. Here is how to use ADO objects to run a SELECT query:

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

 

[Kiwiman]

Howzit

You can also use dlookup:

Mysheetpath = dlookup("Tracker_Link","Tbl_Tracker_Link_Details")

 

[Danny_H]

Thanks Kiwiman, this is what I have ended up doing and it works spot on, thankyou.