Faster alternative to DCount on SQL server-side-CodePudding

I want to check if some values are set to Non-Null when opening a record. Dependent on that, I will show a message box to the user.

Currently I use:

Dim blnValueExists As Boolean
If DCount("*", "dbo_tbl_Parts", "Part_Nr = '" & Me!txt_PartNr & "' AND [ValuesExist] is Null") Then
   blnValueExists = False
Else
   blnValueExists = True
End If

But this will do the operation on client side. How could I achieve the same by shifting the request to the server?

I have used DoCmd.RunSQL at other parts, but I do not know how to get a easy "true/false" result from SQL Server back without needing to use recordsets etc.

CodePudding user response：

Try:

If CurrentDb.OpenRecordset("Select Top 1 Part_Nr From dbo_tbl_Parts Where Part_Nr = '" & Me!txt_PartNr.Value & "' And [ValuesExist] Is Null").RecordCount Then

CodePudding user response：

Using dcount() in this way, or even spending all the time and money to build server side SQL store procedure?

ZERO difference in speed. While the dcount() function is a client side function, it creates the correct sql, sends to server, gets the value back.

As a result, there is no need to consider, build, or attempt to create any kind of say pass-though query to sql server.

Even if you migrate say a large table - 2 million rows.

Now, bind a form to that linked table.

Now, do this:

docmd.OpenForm "frmInvoices",,"InvoiceNumber = 121325"

Access will ONLY pull the one rocord down the network pipe, and dispite the form being bound directly to the table of 2 million rows?

Not all rows are pulled to the client side.

So, with your dcount(), there is no need to attempt some kind of server side SQL or even having to adopt some kind of store procedure on the SQL server side.

Your current code is fine, and adopting a recordset to replace dcount() DOES NOT and WILL NOT run faster.