Newbie(ish) Question about class modules

[finchy]

I am writing a fairly Maths-intensive application for an Engineering firm and am trying to based it on a database. I am using class modules to tie in with each table in the database and use the following code to connect (copied from COM tutorial 2):

Private Sub Class_Initialize()
Set rs = New Recordset
    
rs.ActiveConnection = "Provider=Microsoft." & _
    "Jet.OLEDB.4.0;Data Source=" & App.Path & _
    "\CentralDB.mdb;" & "Persist Security Info=False"

rs.Open "select * from JointTypes", , adOpenKeyset, adLockOptimistic

End Sub

Private Sub Class_Terminate()
rs.Close

Set rs = Nothing

End Sub

I am trying to keep the number of global variables as low as possible. If the class module is called Joint then for example I will put this code locally and pass parameters between subroutines:

Dim uJoint as Joint
Set uJoint= New Joint

The program works very well, but it is too slow as it is having to connect to the database all the time and I need a way of keeping the CurrentRecord the same when switching between different subs, modules, forms etc as the program is quite complicated.

This may seem a really elementary question but remember I am totally new at this (since yesterday when I read the database and COM tutorials)

Thanks

[honeybee]

You can use a collection of Joint type objects, called Joints. Then in this collection, you can open the required recordset. When you are done with the collection, ideally at the end of the program, you can close the recordset.

In the meantime you can create objects of type Joint, which will be appended to the collection and will share the recordset in the collection.

.

[mvrp350]

You said you want to keep the number of global variables as low as possible, but have you thouht about making the recordsets global.

If you do that you're able to reuse a recordset, I also made a separate function to connect so I don't need to reconnect over and over again:

To make the connection:

Sub connectDB(ByVal UserName As String, ByVal Password As String, ByVal DatabaseName As String)
 
'identify the new connection
Set con = New ADODB.connection
 
'if the connection is closed, open it
If con.State = adStateClosed Then
    con.Provider = YourDBProvide
    con.ConnectionString = YourConnectionString
    con.Open
End If
    
End Sub

To open the recordset:

Sub OpenRec(ByVal rs As ADODB.Recordset, ByVal con As ADODB.connection, ByVal strSQL As String)
 
'open the recordset on remote
With rs
    .ActiveConnection = con
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockBatchOptimistic
    .Source = strSQL
    .Open
End With
    
'disconnect recordset from connection
Set rs.ActiveConnection = Nothing
 
End Sub

keep in mind that my app runs on server/client, so I remove the connection to my recordset.

To update my recordset:

Sub UpdateRec(ByVal rs As ADODB.Recordset, ByVal con As ADODB.connection)
 
'reconnect recordset to connection
Set rs.ActiveConnection = con
 
'update recordset
rs.MarshalOptions = adMarshalModifiedOnly
rs.UpdateBatch
 
'disconnect recordset from connection
Set rs.ActiveConnection = Nothing
 
End Sub

Usage:

'To open a recordset
Set YourRecSet = New ADODB.Recordset
strSQL = "SELECT Blah Blah Blah ........."
Call OpenRec(YourRecSet, con, strSQL)
 
'Place your code here
 
'To update the recordset
UpdateRec YourRecSet,con