where to put the ADO cmds..FormLoad?


I have a Timer that will be running a query to see if any records need to be processed
Should I put the whole ado connection stuff in the timer, or should I put the DIMming in the Formload, and Closing in the Unload?

Timer1_Timer()
    Dim cnBC As New ADODB.Connection
    Dim rsBC As New ADODB.Recordset
    Dim strConnect As String
    Dim tsql As String
   
 
strConnect = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source = d:\db\data.mdb; Persist Security Info = False"
tsql = "Select * from AAA WHERE Processed = 'Y' "
rsBC.Open theBCsql, strConnect, adOpenForwardOnly, adLockPessimistic

If NOT rsBC.EOF Then
   StartProcess
End if    

rsBC.Close
Set rsBC = Nothing

End Sub
williamsF1Asked:
Who is Participating?
 
ShauliConnect With a Mentor Commented:
In form declaration:
    Dim cnBC As New ADODB.Connection
    Dim rsBC As New ADODB.Recordset
    Dim strConnect As String
    Dim tsql As String

In Form_Load: 'To open a connection
cnBC .Open "Provider= Microsoft.Jet.OLEDB.4.0; Data Source = d:\db\data.mdb; Persist Security Info = False"

In Timer: 'run your recordset
rsBC.Open theBCsql, strConnect, adOpenForwardOnly, adLockPessimistic

If NOT rsBC.EOF Then
   StartProcess
End if    

rsBC.Close
Set rsBC = Nothing


In Form_Query_UnLoad: 'to close the connection
cnBC .Close

S
0
 
leonstrykerCommented:
The connection should be declared Global since you will probably will be reusing it.  The rest I would put in behind the form itself.

Leon
0
 
ShauliCommented:
One correction:
rsBC.Open theBCsql, strConnect, adOpenForwardOnly, adLockPessimistic

Should be:
rsBC.Open theBCsql, cnBC, adOpenForwardOnly, adLockPessimistic
                              ^^^

S

0
 
ScrptMastaCommented:
Just as a suggestion, consider switching to SQL/MSDE. This way you can load a stored procedure in the database itself and not have to worry about constantly checking to see with the sub you have up there. SQL will automatically do what it supposed to when trigger takes place.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.