put result of query into string variable

[dhxx]

Hello,
I'm just starting with access 2002.

I've a table with only one record (one field)
containing the path of my music directory.
How can i retrieve this info and store it in a vba string variable for further use. I don't want to create a form just
to get the data out and close it again. Their must be a nicer and quicker way.

[andi_g69]

Sorry! I do not get the point where your actual problem is :-)

1) Executing a bit of code independent from a user form?
==> Configure your DB to execute the VBA module on startup

2) The VBA code for retrieving data from a table into a string variable?
==>
Function sGetMusicPath() As String
Dim oDB As Database
Dim oRS As Recordset
Dim sResult

Set oDB = CurrentDb()
Set oRS = oDB.OpenRecordset("SELECT * FROM tblParameters")
If Not oRS.EOF Then
sResult = oRS.Fields("parameter")
Else
sResult = ""
End If
oRS.Close
sGetMusicPath = sResult

Set oRS = Nothing
Set oDB = Nothing

End Function

You can call this function whereever you need the path (in a query, VBA module...)

[dhxx]

thx,
The function is the thing I was looking for but unfortunately it doesn't work . When I compile the function in a module, the compiler tells me at "Dim oDB as database" that we're dealing with an Undefined type??
It seems not to recognize database as a type.
I tried it in acces97 and 2002 but I still got the same problem... Anybody a clue ?

[andi_g69]

I tested this code in Access 97 and it works fine. You should check your object references: In Design view of a module go to Tools-References. In the list of available references you should have checked:
1) Visual Basic For Applications
2) Microsoft Access 8.0 Onject library
3) Microsoft DAC 3.51 Object Library

Actualy No 3 brings the Database objects.

[dhxx]

Thank you very much!
I got it finally working. After I laid in all my references I still had an undefined type warning at
"Set oRS = oDB.OpenRecordset("SELECT * FROM tblParameters")" but when I maid the function public all my problems (maybe not all of them :-)) where over.