database performance problem

[mech55]

This is my code for one of my commands. My Problem is that all of this even on a fast connection to a pretty fast server can take as long as 7 seconds to process and either open the form or not depending on whether my conditions were met. Is there a faster way for me to do this?

Private Sub Command3_Click()

varx = DLookup("[St1_Status]", "Serial_Number_Log", "[Serial_Number] = Text5")
varx2 = DLookup("[St2_Status]", "Serial_Number_Log", "[Serial_Number] = Text5")
varx3 = DLookup("[St3_Status]", "Serial_Number_Log", "[Serial_Number] = Text5")
varx4 = DLookup("[St4_Status]", "Serial_Number_Log", "[Serial_Number] = Text5")
varx5 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 1' and [Defect Fixed?]=false")
varx6 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 2' and [Defect Fixed?]=false")
varx7 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 3' and [Defect Fixed?]=false")
varx8 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 4' and [Defect Fixed?]=false")
var5 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 1'")
var6 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 2'")
var7 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 3'")
var8 = DLookup("[Defect Fixed?]", "Defect_Log", "[Serial_Number]= text5 And [Station] like 'Station 4'")
Closed = DLookup("[Closed]", "Serial_Number_Log", "[Serial_Number] = Text5")
If (Text5.Value = "" Or Text5.Value = Null Or Text5.Value = 0 Or Closed = True) Then
MsgBox ("Please Enter a Valid Serial Number!")
Else
                                                          
    If (varx = "NA" And varx2 = "NA" And varx3 = "NA" And varx4 = "NA") Then
    DoCmd.OpenForm "Radio Test Data Entry", acNormal, "", "[Forms]![Enter Serial]![Text5]=[Serial_Number_Log]![Serial_Number] And [Serial_Number_Log]![St1_Status]=""NA"" And [Serial_Number_Log]![St2_Status]=""NA"" And [Serial_Number_Log]![St3_Status]=""NA"" And [Serial_Number_Log]![St4_Status]=""NA""", acEdit, acNormal
    
    ElseIf (varx = "Pass" And varx2 = "Pass" And varx3 = "Pass" And varx4 = "Pass") Then
    DoCmd.OpenForm "Radio Test Data Entry", acNormal, "", "[Forms]![Enter Serial]![Text5]=[Serial_Number_Log]![Serial_Number] And [Serial_Number_Log]![St1_Status]=""Pass"" And [Serial_Number_Log]![St2_Status]=""Pass"" And [Serial_Number_Log]![St3_Status]=""Pass"" And [Serial_Number_Log]![St4_Status]=""Pass""", acEdit, acNormal
    

        'Test if the serial number status for a particular station is Pass or Fail and whether the defect has been fixed
        'if the status is fail and defect hasn't been fixed don't let the user thru
        'if the defect has been fixed let the user thru.

        ElseIf ((varx = "Fail" And varx5 = False) Or (varx2 = "Fail" And varx6 = False) Or (varx3 = "Fail" And varx7 = False) Or (varx4 = "Fail" And varx8 = False)) Then
            MsgBox ("This serial number needs rework on one of it's stations!")
        
        ElseIf ((varx = "Fail" And var5 = True) Or (varx2 = "Fail" And var6 = True) Or (varx3 = "Fail" And var7 = True) Or (varx4 = "Fail" And var8 = True)) Then
             DoCmd.OpenForm "Radio Test Data Entry", acNormal, "", "[Forms]![Enter Serial]![Text5]=[Serial_Number_Log]![Serial_Number]", acEdit, acNormal
       
    End If
End If
End Sub

 

[modest]

Dlookup() is essentially a built-in query builder function. This means, from what I can see, that you're performing at least 13 simple, select on 3 different tables.

You may want to create a recordset from DAO. And use the filtering/find/seek features to retrieve the field values you need. This should keep your data in memory and speed up retrieval speed.

 

[reclusivemonkey]

Isn't DAO on its way out and ADO the preferred object now?

 

[Summerwind]

Yes. ADO is the way to go

Yes, DLookup is very ineffeicient.

Yes. I am in an agreeable mood today. WHAT IS WRONG WITH ME????????

 

[reclusivemonkey]

Sorry to contradict but ADO is preferred for ODBC tables but DAO is preferred for Jet tables.

LOL No problem Pat, it was a question; not rhetorical. Good to know as I use a mixture of my own tables and ODBC links at work, so its nice to know the preferred methods for each.

 

[modest]

Isn't DAO on its way out and ADO the preferred object now?

Yes and no. If you read my post, it wasn't about what the best object to use was. ADO is a little more confusing then DAO because ADO requires connection strings and other parameters.

My point about DAO is that you could do all of the searching in 3 different recordsets instead of performing 13 separate queries. If you wanted this to be best, I'd say use ADO and make it server-side processing.

However, using ADO over DAO probably won't save you much time. It may, but I believe DAO is still simpler to use.


As for "DAO is on the way out", I believe the changes in 3.6 contradict this statement.

 

[modest]

So are you saying the CursorType would have no affect on the solution primarily because you're connecting to another JET database? What if you converted it to an ODBC database?

 

[reclusivemonkey]

Yes and no. If you read my post, it wasn't about what the best object to use was. ADO is a little more confusing then DAO because ADO requires connection strings and other parameters.

I did read your post modest. I didn't want to get in a debate about semantics. When I asked about the "preferred" object (DAO or ADO), I was doing so because this is something I have just started to use, and the advice I got from SAMS "Teach Yourself Microsoft Access 2002 Programming", in the "Using Advanced Data Types to Manage Data" was;

DAO was yesterday's database standard. Although you may find examples of VBA code using DAO, DAO is probably going the way of the dinosaur, and I encourage you to use ADO in new development.

So I wasn't trying to start a ADO/DAO flamefest, just trying to find out whether this was correct or not. From the advice given by our resident guru, it would seem that DAO has made a miraculous "rise from the grave" and is back fighting for supremacy...

 

[mech55]

i've already redone the whole app in Vb.net. Ado.net and Datasets are the way to go. MUCH better performance...

thanks though )