store SELECT results in VB?

B

Brett D

Hi,
I want to write a VB script that performs SELECT queries on a database and
then stores the results in a variable so I can manipulate them later. Is this
possible? I have not been able to find anything like this in the Help or
discussions. I don't know VB very well but I think if I got past this
stumbling block I'd be OK after that.

Thanks very much in advance,
bd
 
G

guru_ross

I don't understand why people still use DAO. I recommend ADO. You
question does not specify what kind of variable you want to store th
results in, but I'm going to assume you want to get the results an
then disconnect from the database. Depending on the structure of you
data, XML would be an excellent choice for complex data, collection
would work fine for relatively simple data, and a String might eve
work for very simple data. I'll use a collection for this example.
have also taken shortcuts and left our error handling, of course - fo
robust bug-free code, this is just the start.


Code
-------------------

Dim colStoredInfo As Collection
Set colStoredInfo = New Collection

Dim arsData As ADODB.Recordset
Set arsData = New ADODB.Recordset

Dim strQuery As String
strQuery = "SELECT RecID, SomeData FROM yourtable"
arsData.Open strQuery, "(db cxn string here)", adOpenStatic, adLockReadOnly

Do While Not arsData.EOF
colStoredInfo.Add arsData("SomeData"), arsData("RecID")
arsData.MoveNext
Loop

arsData.Close
 
Top