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

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.

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

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????????

Isn't DAO on its way out and ADO the preferred object now? Sorry to contradict but ADO is preferred for ODBC tables but DAO is preferred for Jet tables. In A2003, DAO is back to being the default and ADO is the option. This is because MS never actually changed Access which relies internally on DAO in many situations. DAO is specifically for Jet and has more features and is more tightly integrated.

Plus, in the version of Access which is currently in Beta, Jet has been taken over by the Access team and will probably be called ACE in the future to distinguish it from its predecessor and it will have features only accessable via DAO. Jet tables will continue to be supported but it is unlikely that any future versions will be released. ACE will replace it as the built in Access database.

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.

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.

you wanted this to be best, I'd say use ADO and make it server-side processing. Jet doesn't have a server side process. It is a file server, not a database server. ALL jet processing is done on the local machine after first obtaining the data from the back end database.

I believe that version 4.0 is the final version for Jet. That's what caused all the rumors regarding Access being dead. Way too many people (including many Microsoft people) equate Jet with Access and cannot in their minds separate the two. However, Access is alive and quite well. The Access team has taken over the code base for Jet and modified it to add additional features. The new db engine is being referred to now as ACE. Support for Jet will be retained but not enhanced.

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?

Jet is a file server. SQL Server, Oracle, DB2, MySQL, etc. are database servers. Database servers have processes that run on the server that handle requests and respond with recordsets. Jet does NOT have such a process. Jet ONLY runs on the client. Jet opens the be and extracts the table it needs and processes it locally. EVERYTHING happens on the client. So correct, there is no such thing as a server side cursor for Jet.

You can't connect to Jet via ODBC from Access so you'll have to check some other way but if you open a connection to a Jet database from VB for example, you should not have the option to create a server side cursor available even when using ADO. ADO does not provide functionality that the database manager does not support.

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...

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

thanks though ) :D

You could have used DAO (and possibly ADO) in Access and acheived the same results and not had to recreate your entire application.

it would seem that DAO has made a miraculous "rise from the grave" and is back fighting for supremacy... You have to understand the reason for this. Jet was under the control of the SQL Server group and they decided they had no further use for the product and that's where all those report's of Access' demise came from a few years ago. As a result of the SQL group's relinquishing control, Jet is now under the control of the Access team. They have taken the existing code base and made numerous modifications which you will see next year when Office 2006 is released. It is likely that the "new" Jet will be renamed "ACE" to separate it from its roots.