getting output from a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My code is as follows:

Dim con2 As ADODB.Connection
Dim sqltext As String

sqltext = some query

Set con2 = CurrentProject.Connection
con2.Execute (sqltext)

Simply put I would like to assign the output from the 'some query' (SELECT)
to a string variable. I've been tearing my hair out trying to find an answer
but I presume there is a simple solution!

Thanks.
 
Hi, James.

You want to create a recordset object and then retrieve the results
from it, as in

Set rset = New ADODB.Recordset
rset.Open "SELECT foo, bar FROM baz", con2
strVariable1 = rset("foo")
strVariable2 = rset("bar")
rset.Close
Set rset = Nothing
 
You culd use a recordset object to browse through the query result and
collect it in a variable, e.g.:

Dim con2 As ADODB.Connection
Dim sqltext As String
Dim rs As ADODB.Recordset

Dim i_cnt As Integer
Dim s_result As String

sqltext = "SELECT ...."
Set con2 = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open sqltext, con2, adOpenStatic, adLockReadOnly, adCmdText

rs.MoveFirst
Do While Not (rs.EOF Or rs.BOF)
For i_cnt = 0 To rs.Fields.Count
s_result = s_result & " "_
& rs.Field(i_cnt).Value ' Put extra formatting functions in as
necceary
Next
s_result = s_result & vbNewLine
rs.MoveNext
Loop

Note that the code is written off hand to give you an idea. You could use
rs.Fields("<fieldname>") if you only need certain fields.


Regards,

Frank M.
 
Thanks to both of you. That'll do nicely.

Frank M said:
You culd use a recordset object to browse through the query result and
collect it in a variable, e.g.:

Dim con2 As ADODB.Connection
Dim sqltext As String
Dim rs As ADODB.Recordset

Dim i_cnt As Integer
Dim s_result As String

sqltext = "SELECT ...."
Set con2 = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open sqltext, con2, adOpenStatic, adLockReadOnly, adCmdText

rs.MoveFirst
Do While Not (rs.EOF Or rs.BOF)
For i_cnt = 0 To rs.Fields.Count
s_result = s_result & " "_
& rs.Field(i_cnt).Value ' Put extra formatting functions in as
necceary
Next
s_result = s_result & vbNewLine
rs.MoveNext
Loop

Note that the code is written off hand to give you an idea. You could use
rs.Fields("<fieldname>") if you only need certain fields.


Regards,

Frank M.
Click to expand...
 
Back
Top