Select a value from a table in current DB and use a variable in Access VBA

Question

In Access VBA, I want to use values from a "Settings" table, instead of hard-coding folder locations etc. in the code. I can't figure out how to load a value from the table and use it in the code.

Dim oFSystem As Object
Dim oFolder As Object
Dim oFile As Object
Dim sFolderPath As String



sFolderPath = "C:\Documents and Settings\Main\Desktop\Files" 'BAD BAD, I WANT TO AVOID THIS

I have created a table "Settings", and I want to use the value

SELECT TOP 1 Settings.SettingsValue FROM Settings WHERE (((Settings.SettingName)="Files Folder Location"));

Answer 1

One way:

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim sFolderPath As String

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("SELECT TOP 1 SettingsValue FROM Settings WHERE SettingName="Files Folder Location")

If rs1.RecordCount > 0 Then
    rs1.MoveFirst
    sFolderPath = rs1.Fields("SettingsValue")
End If
rs1.Close
set rs1 = Nothing
set db = Nothing

Answer 2

You could use the DLookup function if you have only one record where SettingName="Files Folder Location".

sFolderPath = DLookup("SettingsValue", "Settings", "SettingName=""Files Folder Location""")