Form takes four minutes to open when linked to SQL tables

J

Johnrg

Registered User.
Local time
Tomorrow, 03:48
Joined
Sep 25, 2008
Messages
115
Guys,

I am a novice here but can anyone see anything simple in this code that would make a form that opens in seconds when connected to an Access Back End take four minutes to open when connected to upsized SQL tables?

Here is the code behind the main menu command buttons event procedure.

Code: 
Private Sub cmdProcessChange_Click()
On Error Resume Next
 
    sbPark.SetFocus
    If TempVars!tuserid = 1 Then
        MsgBox "This option is not available when signed on as ""Administrator""", vbCritical + vbOKOnly, TempVars!tmsgbox
        Exit Sub
    Else
        If DCount("*", "PackDetails") = 0 Then
            MsgBox "There are no Packets entered that meet the criteria for" & vbCr & "Process Changes", vbCritical + vbOKOnly, TempVars!tmsgbox
            Exit Sub
        End If
    End If
    DoCmd.OpenForm "PackProcessChange"
End Sub

Also heres the on load code when the form opens:

Code: 
Private Sub Form_Load()
On Error Resume Next
Dim varWet, varDry
varWet = DCount("*", "ProcesscboPackNoUTGS") + DCount("*", "ProcesscboPackNoCCH")
varDry = DCount("*", "ProcesscboPackNoUTKD")
txtWet = varWet: txtDry = varDry
If varDry > 0 And varWet > 0 Then
cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Wet' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Treatment' Or (ProcessingDepartments.ProcessCostType)='Drying') AND ((ProcessingDepartments.ProcessBranchId)=[tempvars]![tbranchid])) ORDER BY ProcessingDepartments.ProcessDept;"
Else
If varDry = 0 And varWet > 0 Then
cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Drying') AND ((ProcessingDepartments.ProcessBranchId)=[tempvars]![tbranchid])) ORDER BY ProcessingDepartments.ProcessDept;"
Else
If varDry > 0 And varWet = 0 Then
cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Treatment') AND ((ProcessingDepartments.ProcessBranchId)=[tempvars]![tbranchid])) ORDER BY ProcessingDepartments.ProcessDept;"
End If
End If
End If
 
cboChargeNoF.RowSource = "SELECT DISTINCT PackProcess.PPChargeNo FROM PackProcess GROUP BY PackProcess.PPChargeNo, PackProcess.PPProcessType, PackProcess.PPProcessID, PackProcess.PPBranchID HAVING (((PackProcess.PPChargeNo) Is Not Null) And ((PackProcess.PPProcessType)='Treatment' Or (PackProcess.PPProcessType)='Drying')) ORDER BY PackProcess.PPChargeNo DESC; "
cboProcessBatchF.RowSource = "SELECT DISTINCT PackProcess.PPBatchNo, PackProcess.PPProcessType, PackProcess.PPProcessID FROM PackProcess GROUP BY PackProcess.PPBatchNo, PackProcess.PPProcessType, PackProcess.PPProcessID, PackProcess.PPBranchID HAVING (((PackProcess.PPBatchNo) Is Not Null) AND ((PackProcess.PPProcessType)='Treatment' Or (PackProcess.PPProcessType)='Drying')) ORDER BY PackProcess.PPBatchNo DESC;"
Me.ShortcutMenu = False
DoCmd.SetWarnings False
 
If TempVars!twoffset + TempVars!thoffset > 0 Then
DoCmd.MoveSize 0, 0
Me.InsideWidth = Me.InsideWidth + (TempVars!twoffset * 2)
Me.InsideHeight = Me.InsideHeight + (TempVars!thoffset * 2)
Dim varctrl As Control
For Each varctrl In Me.Controls
varctrl.Left = varctrl.Left + TempVars!twoffset
varctrl.Top = varctrl.Top + TempVars!thoffset
Next varctrl
End If
ClearMainMenu

Thanks

JohnG
 
Last edited:
With it taking minutes against SQL Server, perhaps you could open the form with a debugger break point on the earliest event (Perhaps Form Open or Load) and step through the code looking for areas which do not instantly complete / become ready for the next LOC.

One guess is that the way you have coded the queries will not perform the query on the server / send only the qualifying results back to the client.

I have good results with both ADO and DAO objects... must supply the correct connection object / connection string such that the object passes the query through to the server and asks it to run the query on the client's (Access's) behalf.

Client/Server Architecture
http://www.access-programmers.co.uk/forums/showpost.php?p=1110794&postcount=5

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259
 
My fear is DCount may not be interpreted by the server the way you would hope it would (not sure what is sent via ODBC). If that's the case, Michael's recommendation above would uncover that.
 
The reference to TempVars is the problem. This forces the query to be processed by Access.

Change
Code: 
AND ((ProcessingDepartments.ProcessBranchId)=[tempvars]![tbranchid]) ORDER BY

To
Code: 
AND ((ProcessingDepartments.ProcessBranchId)=" & [tempvars]![tbranchid] & ") ORDER BY

This concatenates the value of the TempVar into the string before it is sent for processing by the database engine. (I assumed the value is numeric.)
 
Hi There,
I tried the following code but still the same result.
Any further ideas, or something i did wrong?

Code: 
Private Sub Form_Load()
On Error Resume Next
    Dim varWet, varDry
    varWet = DCount("*", "ProcesscboPackNoUTGS") + DCount("*", "ProcesscboPackNoCCH")
    varDry = DCount("*", "ProcesscboPackNoUTKD")
    txtWet = varWet: txtDry = varDry
    If varDry > 0 And varWet > 0 Then
        cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Wet' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Treatment' Or (ProcessingDepartments.ProcessCostType)='Drying') AND ((ProcessingDepartments.ProcessBranchId)=" & [TempVars]![tbranchid] & ") ORDER BY ProcessingDepartments.ProcessDept;"
    Else
        If varDry = 0 And varWet > 0 Then
            cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Drying') AND ((ProcessingDepartments.ProcessBranchId)=" & [TempVars]![tbranchid] & ") ORDER BY ProcessingDepartments.ProcessDept;"
        Else
            If varDry > 0 And varWet = 0 Then
                cboProcess.RowSource = "SELECT DISTINCT ProcessingDepartments.PRDID, ProcessingDepartments.ProcessDept, ProcessingDepartments.ProcessCost1, ProcessingDepartments.ProcessCostType, ProcessingDepartments.ProcessCode FROM ProcessingDepartments WHERE (((ProcessingDepartments.ProcessDept)<>'Untreated' And (ProcessingDepartments.ProcessDept)<>'Wet After' And (ProcessingDepartments.ProcessDept)<>'Green Sawn') AND ((ProcessingDepartments.ProcessCostType)='Treatment') AND ((ProcessingDepartments.ProcessBranchId)=" & [TempVars]![tbranchid] & ") ORDER BY ProcessingDepartments.ProcessDept;"
            End If
        End If
    End If
    
    cboChargeNoF.RowSource = "SELECT DISTINCT PackProcess.PPChargeNo FROM PackProcess GROUP BY PackProcess.PPChargeNo, PackProcess.PPProcessType, PackProcess.PPProcessID, PackProcess.PPBranchID HAVING (((PackProcess.PPChargeNo) Is Not Null) And ((PackProcess.PPProcessType)='Treatment' Or (PackProcess.PPProcessType)='Drying')) ORDER BY PackProcess.PPChargeNo DESC; "
    cboProcessBatchF.RowSource = "SELECT DISTINCT PackProcess.PPBatchNo, PackProcess.PPProcessType, PackProcess.PPProcessID FROM PackProcess GROUP BY PackProcess.PPBatchNo, PackProcess.PPProcessType, PackProcess.PPProcessID, PackProcess.PPBranchID HAVING (((PackProcess.PPBatchNo) Is Not Null) AND ((PackProcess.PPProcessType)='Treatment' Or (PackProcess.PPProcessType)='Drying')) ORDER BY PackProcess.PPBatchNo DESC;"
    Me.ShortcutMenu = False
    DoCmd.SetWarnings False
    
    If TempVars!twoffset + TempVars!thoffset > 0 Then
        DoCmd.MoveSize 0, 0
        Me.InsideWidth = Me.InsideWidth + (TempVars!twoffset * 2)
        Me.InsideHeight = Me.InsideHeight + (TempVars!thoffset * 2)
        Dim varctrl As Control
        For Each varctrl In Me.Controls
            varctrl.Left = varctrl.Left + TempVars!twoffset
            varctrl.Top = varctrl.Top + TempVars!thoffset
        Next varctrl
    End If
    ClearMainMenu
 
Do you hvae indexes on the database columns used in the comparisons?
 
An index is an ordered list of encoded values for a field (or column in SQL Server) held in the database. It provides a quick way to find the records that match the criteria. Without an index the engine must read every record to find a match.

Do you have SQL Server Management Studio? It is the easiest way for novices to see and add indexes.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom