Recordset closes after ending sub, can it stay open?

[EddyLLC]

I use the following subs in VB6 to open a connection and a recordset in an Access97 database. The problem is in the Open_Recordset sub when I end the sum the recordset closes. It opens fine correctly but I need it to stay open after ending the sub so I can work with it. Is this possible, thanks.

If Conn.State <> adStateOpen Then
With Conn
.CursorLocation = adUseServer
.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;" & _
"Data Source=C:\Collections\Collections.accdb; Persist Security Info = False;"
.Open
End With
End If
End Sub

Public Sub Open_RecordSet(SQL As String)
If Conn.State <> adStateOpen Then
Open_Connection
If rs.State = adStateOpen Then rs.Close
rs.Open SQL, Conn, adOpenStatic, adLockOptimistic
Else
If rs.State = adStateOpen Then rs.Close
rs.Open SQL, Conn, adOpenKeyset, adLockOptimistic
End If
End Sub

 

[EddyLLC]

Found the answer. No need for to bother. Thanks.

 

[ChrisHirst]

I think the answer deserves publishing just in case others need to know.

The answer is to scope the variable correctly.

When dimensioned in the subroutine it is scoped as a local variable, therefore accessible only in the subroutine. If the variable is dimensioned before entering the subroutine it will be scoped as a module variable and accessible to all the functions and procedures in the module.


Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum