How do I assign a SQL query reslut to a VBA variable ? 1

[beetlebailey]

I have a simple SQL query within Excel VBA as follows:

Dim H as Integer
--I have successfully opened a connection here--
SQL="Select Hour1 from vpd where line=502"
Execute SQL
--I have successfully closed the connection here--

Now how do get the results of Hour1 assigned to a variable H
I Know the connection is working because I am sending data from the Excel sheet to SQL and it is making it there just fine. H = integer of 1 to 300. This seems simple enough but I am having a rough time of it.

Thanks in advance, Mark

 

[PHV]

Take a look at the Fields collection of the Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

How can we give advice, when we don't know what methods you're using? There's a hint of ADO, about it, though.

[tt]dim rs as adodb.recordset
set rs=cn.execute(SQL,,adcmdtext) ' cn - the connection
if not rs.bof and not rs.eof then
H = rs.fields("Hour1").value
end if[/tt]

Roy-Vidar

 

[beetlebailey]

RoyVidar:
So sorry, I forgot to mention that, yes it is ADO. I am new to SQL and appreciate your help and all the help from the members of this web site very much. Thanks for the assistance. P.S. by the way your code example was just the answer I have been looking for the past 3 days. Awesome.