none
VBA using SQL EXISTS() to check if a record exists in a table. RRS feed

  • Question

  • My code is kinda jacked, but I was attempting to use an SQL string to give a variable "x" a true or false value based on whether a record exists or not.

    I stumbled across the EXISTS() statement, never used it but tried to get it to work.  I failed.

    Private Function CheckEntry1()
    
        Dim sSQL As String
        Dim x As String
        
        sSQL = "EXISTS(SELECT * FROM tblCloud.IDCloud WHERE tblCloud.[IDCloud] = '000000001');"
        x = DoCmd.RunSQL(sSQL)
        
        MsgBox (x)
    
    End Function

    So I need to be able to look up whether a value exists in a field and if it does I need a yay or nay answer.  I would like to use SQL for speed. 

    Friday, September 7, 2018 2:57 AM

Answers

  • If you want to use a recordset the function could be along these lines:

    Public Function CheckEntry(strValue As String) As Boolean

        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT * FROM TblCloud WHERE IDCloud = """ & strValue & """"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        With rst
            CheckEntry = Not (.BOF And .EOF)
        End With
        
    End Function

    Establishing a recordset is preferable where multiple values need to be found, but to find a single value benchmarking has normally shown a DLookup function call to be more efficient, e.g.

    Public Function CheckEntry(strValue As String) As Boolean

        Dim strCriteria As String

        strCriteria = "IDCloud = """ & strValue & """"
        CheckEntry = Not IsNull(DLookup("IDCloud", "TblCloud", strCriteria))
        
    End Function

    In ether case to return the result in a message box the function would be called like this:

        MsgBox CheckEntry("000000001")

    For an example of the use of the EXISTS predicate you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes, amongst others, the following query:

    SELECT FirstName, LastName, Employer
    FROM Employers INNER JOIN (Contacts
    INNER JOIN ContactEmployers AS CE1
    ON Contacts.ContactID = CE1.ContactID)
    ON Employers.EmployerID = CE1.EmployerID
    WHERE EXISTS
        (SELECT *
          FROM ContactEmployers AS CE2
          WHERE CE2.ContactID <> CE1.ContactID
          AND CE2.EmployerID = CE1.EmployerID)
    ORDER BY Employer;

    This returns all contacts who are employed by the same employer as one or more other contacts.  The EXISTS predicate evaluates to TRUE if the subquery returns one or more rows for the current row returned by the outer query.  The subquery is correlated with the outer query on the employer being the same as that returned by the outer query, but the contact not being the same.  Without the latter restriction the subquery would always return at least one row of course, as it would always return the same row as the outer query's current row.  The two instances of the ContactEmployers table are differentiated by giving them aliases CE1 and CE2 respectively.

    The EXISTS predicate is very efficient because it allows the optimizer to make best use of the indexes by virtue of the SELECT * in the subquery.  You can also use the NOT EXISTS predicate to return a Boolean TRUE value where the subquery returns no rows.  This is often preferable to using the NOT IN predicate as the latter will fail in the event of the column in question being NULL in one or more rows being examined in the subquery.

    Mathematically, the essential difference between the IN and EXISTS predicates is that the former is an operation of the relational algebra, while the latter is an operation of the relational calculus.  The database relational model and the SQL language were originally based solely on algebraic operations, of which there were eight in Codd's first introduction of the model.  Only later was the referential calculus, with its greater efficiency in some contexts,  introduced.

    Ken Sheridan, Stafford, England

    • Marked as answer by Amedean Messan Friday, September 7, 2018 9:12 PM
    Friday, September 7, 2018 9:14 AM

All replies

  • Hi,

    EXISTS is used in a WHERE clause of a main query, so it won't work on its own like that. However, if you simply want to know if a record exists in a table, you could also use either the DLookup() or DCount() function. For example:

    Dim ItExists As Boolean
    
    ItExists = DCount("*", "tblCloud", "IDCloud='000000001'")>0

    Hope it helps...

    • Edited by .theDBguy Friday, September 7, 2018 3:23 AM
    Friday, September 7, 2018 3:09 AM
  • I have read that this may have performance issues in larger databases.  Am I correct?  Maybe a recordset approach in leu?
    Friday, September 7, 2018 3:23 AM
  • I have read that this may have performance issues in larger databases.  Am I correct?  Maybe a recordset approach in leu?

    Hi,

    You'll have to try it out on your large database and let us know. I never noticed any slowness using it. Besides, it is using a recordset internally, I think. So, I am not sure there's any advantage or disadvantage to create your own recordset over using this function.

    Just my 2 cents...



    PS. Can you give us a link to where you read it?
    • Edited by .theDBguy Friday, September 7, 2018 3:27 AM
    Friday, September 7, 2018 3:25 AM
  • At 6:24 he gives a technical explanation.  He says it should be avoided for enterprise solutions.

    youtube.com/watch?v=ZA3gBqcuAz8

    Friday, September 7, 2018 3:35 AM
  • Hi,

    Thanks. Are you saying you have an enterprise application? Access can be used for enterprise solutions but SQL Server is better suited for those.

    For example, most experts agree Access shouldn't be used over WANs, but I know one MVP who has very good success using multiple Access solutions on a GiG WAN.

    Just my 2 cents...

    Friday, September 7, 2018 3:47 AM
  • If you want to use a recordset the function could be along these lines:

    Public Function CheckEntry(strValue As String) As Boolean

        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT * FROM TblCloud WHERE IDCloud = """ & strValue & """"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        With rst
            CheckEntry = Not (.BOF And .EOF)
        End With
        
    End Function

    Establishing a recordset is preferable where multiple values need to be found, but to find a single value benchmarking has normally shown a DLookup function call to be more efficient, e.g.

    Public Function CheckEntry(strValue As String) As Boolean

        Dim strCriteria As String

        strCriteria = "IDCloud = """ & strValue & """"
        CheckEntry = Not IsNull(DLookup("IDCloud", "TblCloud", strCriteria))
        
    End Function

    In ether case to return the result in a message box the function would be called like this:

        MsgBox CheckEntry("000000001")

    For an example of the use of the EXISTS predicate you might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes, amongst others, the following query:

    SELECT FirstName, LastName, Employer
    FROM Employers INNER JOIN (Contacts
    INNER JOIN ContactEmployers AS CE1
    ON Contacts.ContactID = CE1.ContactID)
    ON Employers.EmployerID = CE1.EmployerID
    WHERE EXISTS
        (SELECT *
          FROM ContactEmployers AS CE2
          WHERE CE2.ContactID <> CE1.ContactID
          AND CE2.EmployerID = CE1.EmployerID)
    ORDER BY Employer;

    This returns all contacts who are employed by the same employer as one or more other contacts.  The EXISTS predicate evaluates to TRUE if the subquery returns one or more rows for the current row returned by the outer query.  The subquery is correlated with the outer query on the employer being the same as that returned by the outer query, but the contact not being the same.  Without the latter restriction the subquery would always return at least one row of course, as it would always return the same row as the outer query's current row.  The two instances of the ContactEmployers table are differentiated by giving them aliases CE1 and CE2 respectively.

    The EXISTS predicate is very efficient because it allows the optimizer to make best use of the indexes by virtue of the SELECT * in the subquery.  You can also use the NOT EXISTS predicate to return a Boolean TRUE value where the subquery returns no rows.  This is often preferable to using the NOT IN predicate as the latter will fail in the event of the column in question being NULL in one or more rows being examined in the subquery.

    Mathematically, the essential difference between the IN and EXISTS predicates is that the former is an operation of the relational algebra, while the latter is an operation of the relational calculus.  The database relational model and the SQL language were originally based solely on algebraic operations, of which there were eight in Codd's first introduction of the model.  Only later was the referential calculus, with its greater efficiency in some contexts,  introduced.

    Ken Sheridan, Stafford, England

    • Marked as answer by Amedean Messan Friday, September 7, 2018 9:12 PM
    Friday, September 7, 2018 9:14 AM
  • Like anything, it is a question of context and when to use (or avoid) dlookup().

    Building a WHOLE sql statement, shoving into recordset vs that of a dlookup() will perform IDENTICAL.

    There is ZERO performance difference.

    I tested this over and over for 15+ years in every version of Access, and that also includes when using sql server for the back end.

    Again: same performance.

    So let’s now put into “correct” context “when” one should avoid dlookup().

    Where dlookup() fails or is a bad idea is use inside of a sql query.

    Remember, dlookup() is a VBA function. And placing “any” VBA function inside of sql tends to be a bad idea.  This advice is NOT limited to dlookup(), but using ANY VBA function in the sql should be avoided.

    You see, if you have a query that returns 1000 rows, and you have a dlookup() in that query, then you will execute dlookup() 1000 times. Well, the problem is not REALLY dlookup(), but the user adopting a design in which you executing 1000 brand new separate dlookup() VBA function over and over. This will cause access to build + generate 1000 new separate SQL quires to be executed by Access.

    In 9 out of 10 times, the dlookup() in that sql can be replaced with a left join of the lookup value (so use the left join). And better with a left join is you can use the query builder, and get to join using a graphical interface. (So no writing of sql and no need to write a dlookup() command inside of that sql.

    Dlookup() is NOT slower than building a whole sql query.

    However, if you by accident place a dlookup() inside of a query, then that is where things break down.

    So the REAL advice here?

    Don’t use dlookup() ever inside of a query.

    Even for “enterprise” applications, if you need one sql statement vs that of a one dlookup(), then you are most free to use the dlookup() command.

    Dlookup() is NOT slower than the same sql statement to pull that one row of data.

    The performance is the SAME, but the dlookup() is one heck of a lot less code.

    If you placing a dlookup() inside of a sql query? Don’t do it!!! – avoid this like the plague.

    So dlookup() is not bad, but the miss use of the command is the

    All dlookup() does is behind the scenes generate the SAME sql you would write by hand. It runs the same speed, but by inbreeding the VBA function inside of a sql query, you causing the VBA command to be executed 1000’s of times, or at the very last once for each row – and that is slow.

    In fact placing ANY VBA command inside of your sql will cost you some serous performance penalties.

    So the real advice here is not to avoid dlookup(), but like any access command, not to miss use the command.

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Monday, September 10, 2018 4:14 PM