• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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
0
williamsF1
Asked:
williamsF1
  • 2
1 Solution
 
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:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now