Returning the value of an access query to a variable

[mal2ty]

I'm trying to use an access query within an excel macro to get a certain value. The query results are being stored in a recordset. Is there a way to assign the query result (it's always only 1 field of 1 record) to an excel variable?

Any help will be appreciated.

 

[mal2ty]

I'm still looking for a solution to this problem. In the meantime, I've worked out this temporary solution. It's not pretty, and I'm sure there's a better way, but it's all I could figure out on my own.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Dim db As Database
Dim rs As Recordset
Dim strVar As String

strVar = "Test"

'Set the Database
Set db = Workspaces(0).OpenDatabase("\\server\path\database.mdb&quot

'Return the query results
Set rs = db.OpenRecordset("SELECT TOP 1 TABLE.DESCRIPTION" & _
" FROM TABLE" & _
" WHERE (((TABLE.TABLE)=" """" & strVar & """" & &quot);&quot

'Copy the query results to a temporary worksheet
numberOfRows = Sheets(15).Range("A1&quot.CopyFromRecordset(rs)

'Close the query and database
rs.Close
db.Close
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

 

[mymou]

You could always put the result in a hidden field if you want to cheat.


Stew

 

[mal2ty]

How could this be done?

 

[Kaos2800]

The way I've been cheating right now is to create a variable at the top of your VBA Code, before any Subs or Functions, this way it becomes public that way one sub can store information to it and another sub can grab it. Then no one on the page can mess it up and you can have as many sheets as you want.

-David