opening/closing multiple recordsets?

Database aspects of Visual Basic

Ray
I've just recently moved into the applications side of things - I've 
been a sysadmin for the last 12 years and have dabbled in VB... but I'm 
far from an expert.  I've inherited a bunch of apps from a guy who's 
retiring, and have a question:

Most of the apps have a connection to a database, and he's created a 
function to connect and open the db and do various things on error... 
but because some apps may require multiple connections and overlapping 
ones, I have stuff like

rs!shipid
rsb!companyid
rsC!moredata
and so on...

Instead of having to manually name them, is it possible to have a 
"generic" function to open/close the recordsets?

I'm thinking along the lines of how you can access files - freefile, 
open, close, etc...

Currently it looks like he cuts and pastes and renames, which works, but 
seems horribly inefficient and a violation of the "don't repeat 
yourself" rule, but I'm not good enough to know if that's the only way 
to do it.

It's VB6, btw.

Ray                                            
Ralph
For specific examples we need to know what data access library you are
using? ADO or DAO?

With either one it is possible to create a routine that generically queries
a database and then the Fields and their datatypes, it contains. But why
would you want to do this?

For the moment let's assume you are using embedded SQL and not stored
procedures. (though the issues would be the same). When you create your SQL
you do so because you need specific fields and plan to work with the
returned data in some very specific ways. ie, you will have provide
information as to what Field is what at some point. So what would you be
saving?

-ralph
                                            
Ray
It's ADO.
And depending on the project, it's embedded SQL or stored procedures.

I understand each recordset must be different - otherwise why are you 
opening a new one to get the same data, but let me try rephrasing the 
question:

You need to open 3 recordsets accessing three different stored 
procedures.  Recordsets 1,2,3.  For each recordset you must come up with 
a unique name, open and close the database connection.

If you were using a text file, you would need to do the same thing, but 
you have functions like freefile that just give you a number for a file 
handle.

What I'm trying to do is have a chunk of code that does something like:
(warning - bad psuedocode follows)

sql = "some sql statement"
rsID= get new recordsetID ' get me a new sql database "handle"
rsData = openSQL ( sql, rsID) ' generic code to open a database 
connection with proper errorchecking

the problem I have is the openSQL line is a cut and paste for each 
recordset.  I'm looking for a way to access a recordset much like you'd 
access a regular file via a file handle, so it scales easier if you need 
to add another recordset in.

Hopefully this makes a bit more sense.

If you still want me to, I'll post a snippet of his code...
Ray
                                            
Steve
Perhaps you mean something like this?

Private Function OpenRS(SQL As String) As ADODB.Recordset

    ' assumes  mConn is a valid open connection object
    Set OpenRS= New ADODB.Recordset
    Call OpenRS.Open(SQL, mConn, adOpenStatic, adLockOptimistic, adCmdText)

    'suitable error checking, etc.

End Function

Used like this?

    Dim rs As ADODB.Recordset

    Set rs = OpenRS("Select * from Table1")
    ' do stuff with Table1 data

    Set rs = OpenRS("Select * From Table2")
    ' now do stuff with Table2 data
                                            
Ray
I copied this code into VB, and will play with it and see if (a) it 
works for what I want or (b) I can use it to better articulate what I'm 
looking for.

Thanks,
Ray
:)
                                            
Ray
This is what I ended up doing to open/access multiple recordsets at once:

Module1:
Public rs() As ADODB.Recordset
Public objDBconn
Public rs_order
Public rs_prod


Sub main()

opendb
rs_prod = assignRS("select * from products")
rs_order = assignRS("select * from orders")
Form1.Show

End Sub

Sub opendb()

Set objDBconn = CreateObject("ADODB.Connection")
objDBconn.ConnectionString = "Driver={SQL 
Server};server=wpg28;database=northwind"
objDBconn.Open

'do errorchecking here

End Sub

Function assignRS(sql) As Integer

On Error Resume Next
If UBound(rs) < 1 Then ReDim rs(0)
assignRS = UBound(rs)
ReDim Preserve rs(UBound(rs) + 1)



Set rs(assignRS) = CreateObject("ADODB.Recordset")
rs(assignRS).Open sql, objDBconn

'error checking here

End Function


Form1:
Private Sub Command1_Click()
rs(rs_order).MoveNext
Text1.Text = rs(rs_order)!customerid

End Sub


Private Sub Command2_Click()
rs(rs_prod).MoveNext
Text1.Text = rs(rs_prod)!productid

End Sub


It's pretty rough - variants all over the place, etc... but what it does 
is allow me to use a recordset "handle" instead of a name and 
encapsulate the connecting and error checking in a function.

Is this good, bad, ugly?  And any suggestions on how to do it without 
the global variables?  I think I'd need to keep them because the reality 
is they're being used to share data across multiple functions, so I'm 
going to need a global variable of some kind...

I guess I'm trying to emulate freefile for recordsets.

I also learned that you don't need to reconnect to the db each time - 
I'm a newbie to VB6 database access - learning a lot on the fly, have a 
couple more books on order... (I'll be supporting these apps for a few 
years likely...)

Ray