is there any other way

[iissa]

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

[MikeHost]

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:

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.

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.

Load frmFormName
frmFormName.Show

This will load the form without displaying it.

[iissa]

could you please show me code of what you mean in number one

[MikeHost]

Hope this helps..

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