-
i have a form, which requires when loading up to extract data from three different tables in side one database.
currently what i am doing is opening up a new connection to the three tables and then opening them
seperatly
this takes a long time to do as there are more than 3000 entries in one table,
is there any way i can speed it up even a little bit
here is my code right now
i am sure that there is another way of doing this
Private Sub Form_Load()
Set rs = New Recordset
Set rs3 = New Recordset
Set rs4 = New Recordset
Dim Table1 As String
Dim te As Table
Table1 = "auditlog"
rs.ActiveConnection = "Provider=Microsoft." & "Jet.OLEDB.4.0;Data Source=P:\Tracker\EPCS\EPCS_Tracker_app.mde;" & "Persist Security Info=False"
rs.Open "select * from [Change Request/Fault Summary Table]", , adOpenKeyset, adLockOptimistic
rs3.ActiveConnection = "Provider=Microsoft." & "Jet.OLEDB.4.0;Data Source=P:\Tracker\EPCS\EPCS_Tracker_app.mde;" & "Persist Security Info=False"
rs3.Open "select * from [Intech Staff]", , adOpenKeyset, adLockOptimistic
rs4.ActiveConnection = "Provider=Microsoft." & "Jet.OLEDB.4.0;Data Source=P:\Tracker\EPCS\EPCS_Tracker_app.mde;" & "Persist Security Info=False"
rs4.Open "select * from [Status Pick List]", , adOpenKeyset, adLockOptimistic
rs.MoveLast
intRecCount = rs.RecordCount
rs.MoveFirst
For intCounter = 1 To intRecCount
Combo1.AddItem rs![Change Request /Fault Number]
rs.MoveNext
Next intCounter
rs3.MoveLast
intRecCount = rs3.RecordCount
rs3.MoveFirst
For intCounter = 1 To intRecCount
Combo4.AddItem rs3![Staff Name]
Combo5.AddItem rs3![Staff Name]
Combo6.AddItem rs3![Staff Name]
Combo7.AddItem rs3![Staff Name]
rs3.MoveNext
Next intCounter
rs4.MoveLast
intRecCount = rs4.RecordCount
rs4.MoveFirst
For intCounter = 1 To intRecCount
Combo8.AddItem rs4![Status]
Combo9.AddItem rs4![Status]
Combo10.AddItem rs4![Status]
Combo11.AddItem rs4![Status]
rs4.MoveNext
Next intCounter
Combo1.Text = envfaultnum
End Sub
-
There are a couple of things that I see that may help you:
First, you don't need to open a separate connection for each recordset that you are using. Open the connection first, then open each recordset as you need them. If you need some code examples of the, let me know and I will post them.
Second, when you read the tables try this:
Code:
Do Until rs.EOF = True
Combo1.AddItem rs![Change Request /Fault Number]
rs.MoveNext
Loop
It will also run faster if you use the With/End With statements.
Code:
With rs.
Do Until .EOF = True
Combo1.AddItem ![Change Request /Fault]
.MoveNext
Loop
End With
Finally, when you load the form try doing this instead of just using the show method.
Code:
Load frmFormName
frmFormName.Show
This will load the form without displaying it.
-
could you please show me code of what you mean in number one
-
Hope this helps..
Code:
Option Explicit
Public goConn As New ADODB.Connection
Private Sub Form_Load()
Dim boolPerform As Boolean
Dim rs As Recordset
Dim rs2 As Recordset
Dim strSQL As String
' Open the ADO Connection
boolPerform = DataOpen(goConn)
'Define the SQL String to Open Recordset with
strSQL = "Select * from tblMyTable Order by CustomerID"
'Define Recordset
Set rs = New ADODB.Recordset
'open Connection1
rs.Open strSQL, goConn, adOpenKeyset, adLockOptimistic
'Now, do the same for recordset 2. It doesn't need another connection,
'because the connection (goConn) to the table is already open.
strSQL = "Select * from tblMySecondTable"
Set rs2 = New ADODB.Recordset
rs2.Open strSQL, goConn, adOpenKeyset, adLockOptimistic
End Sub
Public Function DataOpen(oConn As Connection) As Boolean
On Error GoTo Open_EH
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & "M:\Project Control\ProjectControl.mdb"
oConn.Mode = adModeReadWrite
oConn.Open
DataOpen = True
Exit Function
Open_EH:
Call ErrorHandler(goConn)
DataOpen = False
Exit Function
End Function
