Reference a Microsoft Access 2010 Table Value in a VBA Subroutine

Question

I am trying to reference a particular field from a table within a function call. Specifically, I would like to open an executable and get the path from the table. Here, the path is hardcoded in:

    Shell(“C:\ECLIPSE_CPP\eclipse.exe”)

However, I would like to store the path within a table and call a function (maybe the Shell function?) on a path from that table.

This is what I have tried:

    strSQL = “SELECT Path FROM Paths WHERE Tool = “”Eclipse””;”
    Shell(strSQL)

The Paths table looks like this:

 
    Tool    | Path
    ========|=============================

    Eclipse | “C:\ECLIPSE_CPP\eclipse.exe”

Does anyone know way to reference this table value in VBA code?

Smandoli · Accepted Answer · 2013-07-19 04:12:21Z

2

strPath = DLookup("Path" , "Paths" , "Tool = 'Eclipse'")
Shell(strPath)

DLookup is not efficient. It may be fine for your purpose, but if not, you can use a recordset to retrieve the value.

edited Jul 19, 2013 at 4:12

answered Jul 19, 2013 at 4:02

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Smandoli · Accepted Answer · 2013-07-19 14:39:01Z

2

Dim dbs as DAO.Database
Dim rs as DAO.Recordset
Dim strpath as String

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select Path from Paths where Tool = 'Eclipse'")

strpath = rs.Fields(0)
    '   = rs!Path       alternative
    '   = rs("Path")    alternative

Application.FollowHyperlink(strpath)

edited Jul 19, 2013 at 14:39

Smandoli

7,0593 gold badges53 silver badges83 bronze badges

answered Jul 19, 2013 at 9:58

Siddharth Sawjiani

976 bronze badges

1 Comment

Smandoli Over a year ago

Upvote -- nice answer for recordset code, and even an Application function.

Collectives™ on Stack Overflow

Reference a Microsoft Access 2010 Table Value in a VBA Subroutine

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related