Solved Does Record Exist Code Error.

[HillTJ]

So, I have the following code that worked fine when the rs,findfirst line was hard coded (now commented out). I wish to make the function more universal by passing the 'find' part as a strwhere that I've built up. But, it doesn't work & returns an invalid argument error 3001. Clearly, there is something wrong with my syntax. Appreciate any help.

Option Compare Database
Option Explicit
Public Function DoesRecordExist(RecordID As Long, TableName As String, FieldtoMatch As String) As Boolean


'On Error GoTo MyErrorProc:
    
    Dim db As Database
    Dim rs As DAO.Recordset
    Dim strWhere As String
    
    If IsNull(RecordID) Then
        Exit Function
    Else
    
        Set db = CurrentDb()
        Set rs = db.OpenRecordset(TableName, dbOpenDynaset)
    
    strWhere = FieldtoMatch & " = " & RecordID
    
    Debug.Print strWhere 'to test what the string looks like
    Debug.Print "InspectionSectionID = " & RecordID 'to test what the original string looks like
    
        'rs.FindFirst "InspectionSectionID = " & RecordID 'the original expression that works
      
        rs.FindFirst Chr(34) & strWhere & Chr(34) 'invalid argument error 3001
            If Not rs.NoMatch Then
                DoesRecordExist = True
            Else
                DoesRecordExist = False
            End If
  End If
ExitError:
        
        Set rs = Nothing
        Set db = Nothing
        On Error Resume Next
        Exit Function


MyErrorProc:
        
ErrorHandler:
    Call DisplayErrorMessage(Err.Number, "DoesRecordExist")
    
        Resume ExitError


End Function

 

[theDBguy]

What happens if you take out the Chr(34)s?

 

[HillTJ]

@theDBguy , all seems OK. you know, I think I was not passing the parameters correctly when testing!!! I got a bit frustrated & called for help. Thanks for the support. Sometimes you have to take a step back from your work.

 

[theDBguy]

@theDBguy , all seems OK. you know, I think I was not passing the parameters correctly when testing!!! I got a bit frustrated & called for help. Thanks for the support. Sometimes you have to take a step back from your work.

Ok. Glad to hear you got it sorted out. Cheers!

 

[Pat Hartman]

I don't like to burst your bubble because I don't want to discourage you from spreading your wings and trying make reusable code and efficient procedures but sorry, this isn't going to be one of those.

I/O (Input/Output) which is the process of reading and writing records is the most resource intensive and time consuming process in normal applications although code loops can be expensive too.

The task this procedure is performing would usually be handled by a dCount() function.

dCount("*", "Yourtableorqueryname", YourCriteria) It is not likely that you would need to run this function on multiple tables at a given time since each form is bound to a single main table. But similar code could exist in multiple forms. Is there any efficiency to be obtained by trying to reuse the code? We're talking about a single line of code that is already abstracted to take arguments. So we're talking about abstracting the arguments which seems to add more code rather than reduce code or offer any efficiencies. Granted you see a lot of warnings to not use domain functions but the warning only applies to two situations - inside a query and inside a loop and the warning is for the same reason. Each domain function runs a query. If it gets run once when the user clicks on a button on a form, that is very different from opening a query to select 10,000 records - that runs 10,000 separate queries. A VBA loop is a similar situation. If the loop is processing 5 items, it really doesn't matter what you do but if it is processing thousands of records, then it does matter. So, the warning about domain functions is (or should be) very specific. They are fine in a one-off situation but there are much better solutions for queries or code loops.

Instead of trying to abstract the domain function which you probably recognized intuitively as non-productive, you wrote a procedure that takes the same arguments the domain function would have taken and instead opened a recordset. And not only did you open a recordset but the recordset had no criteria so it must retrieve every single row in the table and run the find once all that data gets ported over the network. This isn't horrific if the BE is Jet/ACE but it is mindbogglingly bad if the BE is SQL Server since you are actively preventing the server from doing the "heavy lifting" and forcing the workload into the FE.

For the sake of understanding what is going on behind the curtain let me break down just what the db.OpenRecordset is doing.
1. In a relational database, data is not retrieved the way it is with a flat file such as .txt or .csv. If you want to do something to read or write data in a RDBMS, you MUST use a query so since you are opening a table rather than a query, the first thing that happens is that Access creates a query that selects all the columns and all the rows of the table for you. Then it compiles the query and creates an execution plan so it can decide on the optimum method for retrieving the data. ie, should it use an index? In this case, there is no criteria so no index will be used. The execution plan will call for a full table scan. Some people call this RBAR (Row by Agonizing Row). This brings every single row of the table into memory on the local PC. The more rows that get added to a table, the longer this takes.
2. Now that all the data is in memory, the next step is the Find. Well, we've removed the data from the table and so we have no indexes or anything else to help us so we have to use RBAR again and this time Access has to read each row a second time until it finds the record it is searching for if it exists. The more rows that get added to a table, the longer this takes.
3. OK, we have the answer. The record exists or it doesn't exist. If it doesn't exist, that's the end of it. BUT, if it does exist, now what? This procedure doesn't address the "now what" but if we put this procedure in the context where you would use it, the "now what" ends up being doing more I/O either a query with criteria or another RBAR with a find to retrieve the record we just went to all that trouble to find. We were there, we touched it, but we didn't retrieve it then which forces us to have to go back again, possibly using RBAR twice again to bring back the record.

I would never expect a newcomer to automatically recognize the cost of a procedure like this which is why I went into depth to try to explain just how expensive it is in terms of computer resources.

So, the bottom line is, if you are searching for a record, fetch it if you find it. Why go back over and over again? The simplest and most efficient way to fetch a record is by using a query with criteria. That way, the query engine can decide on the best way to satisfy the request AND, if the record exists, you retrieve it as part of the search process AND you only ever have to retrieve the single record you are searching for.

The second lesson is don't abstract the abstract

 

[HillTJ]

Pat, thanks for being so gentle. I kind of thought i was being clever to write this function as reusable code. As a learner and working in isolation (except for when i get stuck and call the forum for help) it is often difficult to determine the correct path. Googling code can send you in the wrong direction if you don't have an understanding of what you wish to achieve. Fully understand (at least to the extent i can understand) your explanation. Will replace my code with a couple of lines using dcount. Will look up how best to implement this. All i aim to do is confirm whether a record exists or not in another table.

 

[June7]

"or not in another table" raises another can of worms. Why would a record possibly be in more than one table?

 

[Pat Hartman]

@HillTJ I am very sensitive to a lot of things that other, even experienced Access developers are not due to my roots in writing online transaction programs for CICS and IMS in the 70's. When you've got thousands of concurrent users, milliseconds count. AND since all our code was executing on ONE CPU rather than on individual workstations, my sloppy code affected EVERYBODY not just some user using one of my transactions.. You have a lot more leeway with an Access application because the FE runs on the local PC and so unless the PC has multiple tasks running, your Access code isn't competing with other threads for resources. You do have to worry about I/O because now you are competing with all the other processes moving data around over the LAN. Most of our CICS and IMS transactions had to return results in less than 1 second so loops couldn't contain a single extraneous line of code. I/O couldn't return a single unnecessary record. An interesting side effect of this is that I am much more inclined to trap errors than to attempt to prevent them. Once a user has a little experience with an application, they become less likely to make mistakes. They have a better sense for what data is required (your interface can be very helpful with this) and what types of data go in which fields. So, if your validation tests are not likely to find errors, then why bother - as long as you have a way to ensure that nothing bad happens. While it is nice to give users simple, easy to understand error messages, sometimes it makes more sense to let the user try to save the record and let the RDBMS trap the error. The errors the database engine can catch are limited because the table level validation rules are simplistic but if they work, it may make sense to go with that rather than trying to prevent every conceivable error. One example of this is when implementing security. Many people go to great lengths to try to lock controls and prevent the user from entering any data but you can always stop the user from changing anything if you trap the change in the correct event. So, in the form's dirty event - I can check the user's security and if he is not allowed to change or add data, I can stop him cold by using Me.Undo and cancelling the event. Even if the time/resources to prevent or catch are equivalent, if you try to prevent, your code runs 100% of the time but if you catch instead, your code runs 5% of the time. The more experience you have, the more efficient you become at choosing what is proper for any given situation.

It is unlikely you will run into much on the web or even in books that talks about the cost of I/O in an Access application, mostly because Access is largely interactive rather than batch although it does process recordsets where you get into the loops and why you shouldn't include domain functions in queries.

With Access you rarely have to work to squeeze out all expensive or gratuitous code but knowing on a scale of 1-10 which operations are inexpensive and which are expensive in terms of resources, you have a good sense of where to spend your time. So, you think about and work to make loops and queries efficient but don't sweat the small stuff for almost everything else.

I could tell when I looked at your code that you were trying to optimize your world by trying to reuse code rather than optimizing processing. Both are valid endeavors but have different objectives. In this case, the two were in conflict.

Reusing code saves you development time and ultimately produces a better product because if you do it correctly you are less likely to create coding anomalies where code in one procedure returns a different result from code in another procedure that should return the same result. I reuse code whenever I can but I rarely set out to create common code. When I find myself writing code that I know I've written elsewhere, I take the original version and abstract it so it can be reused.

Optimizing processing is different. Here you are trying to make code run faster or occupy less space if that is your constraint.

 

[Josef P.]

[a little OT]

The task this procedure is performing would usually be handled by a dCount() function.

Why should you count all hits for "Exists"?
A DLookup (or similar as select) would also be sufficient.

 

[isladogs]

Please check out my recently updated article comparing different methods of performing this task

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

www.isladogs.co.uk

 

[HillTJ]

@june , i open a form with docmd.openform in either 'add' with no filter parameters or 'filtered' by passing a 'where' statement if a related record exists. It's the checking of the 'does record exist' that's the subject of this thread. I'll just use a dlookup as advised by @isladogs.

@Pat Hartman , my philosophy is to develop my applications to 'best practices'. I try to write tight reusable code and use vtools to catalogue these. Unfortunately, i am not blessed with a best practices detector and i often get stuck 'not knowing what i don't know'.

What i have learn't with this simple thread is invaluable stuff. Thanks to all. I hope others have found it informative too.

 

[Pat Hartman]

Why should you count all hits for "Exists"?

Because since all you care is if the ID exists, the "*" is the more efficient method.

A DLookup (or similar as select) would also be sufficient.

Assuming you have the good sense to return the ID which is what you searched for rather than some random data field which might naturally be null.

I'll just use a dlookup as advised by @isladogs.

Just open the form!!!, You can have the form display a message if the recordset is empty or you can have it open ready to add a record. What is the percentage of time that the user will request a record that doesn't exist? If it is 5%, then 95% of the time you are reading the table twice for no reason. If you "trap" the "error", then 5% of the time, you have opened the form for no reason assuming the user isn't going to add the record he couldn't find.

 

[Josef P.]

@HillTJ: I consider encapsulating the Exists exam into an extra function to be good practice. This allows to efficiently support other DBMS later on. Even if e.g. only DLookup is used in this function, I find this better than using DLookup directly.

For comparison: "Check if there is an entry for XYZ and then do something"

Variant 1 (only DLookup):

if Nz(dlookup("True", "Table", "FeldABC = 'xyz'), False) then ' Nz is not required ... only to check false ('not exists')
   DoSomething ...
end if

Variant 2 (Exists function):

If DoesRecordExists("Table", "FeldABC = 'xyz'") then
   DoSomething ...
end if

With function DoesRecordExists:

public function DoesRecordExists(Source as String, Criteria as String) as Boolean
    DoesRecordExists = Nz(dlookup("True", Source, Criteria), False)
end function

Variant 2 does the same as variant 1, but is much more readable for me.

Because since all you care is if the ID exists

Exactly! I don't want to know the number.

Assuming you have the good sense to return the ID which is what you searched for rather than some random data field which might naturally be null.

Neither. I only let True give back.

 

[Pat Hartman]

This allows to efficiently support other DBMS later on.

What do you think the name "Access" stands for You relink your BE and ASSUMING you have used ACCESS methods rather than specific RDBMS methods such as sp and pass through queries, you don't need to change anything. I can switch my BE from SQL Server to Oracle to DB2, to Jet or ACE with NO changes. But not if I've used stored procedures or pass through queries with RDBMS specific SQL

Exactly! I don't want to know the number.

If you are searching for the ID, the result is 0 or 1 which = false or true just by happenstance So you can test the dCount() for True or you test the dLookup() for Not Null Seems to me if you are looking for an existence test, "True" makes more sense than "Not Null".

 

[ebs17]

DCount and DLookup are Access functions. They are executed just like the non-optimal recordset from the top of the frontend, i.e. with a linked table, and each by itself is a table access with virtually the same cost.
Of course, a DCount should be compared to a "SELECT Count(*) ...", not to loading a whole table with all the unneeded fields, and FindFirst is entirely not optimal for an existence check.

 

[isladogs]

I'll just use a dlookup as advised by @isladogs.

Not sure where you got that idea from. Perhaps you didn't read my article in its entirety?
I hope that the field you are checking for a specific record is indexed as that will make a huge difference to the search time whichever method you use.
For indexed fields, the domain functions DLookup & DCount are both relatively slow methods of searching if a record exists.
There are much faster methods available

 

[HillTJ]

All, you have provided me with plenty of approaches to try. I shall digest your recommendations, have a fiddle and see what works best. Thanks