Retrieving ONE value from SQL-Server table (DLookup)

Tiger955

Registered User.
Local time
Today, 21:15
Joined
Sep 13, 2013
Messages
140
Hi!

Front end Access 2010, back-end SQL-server 2008 R2.

Normally I retrieve a certain value by Dlookup("myvalue", "mytable",...)

or

strSQL = "SELECT myvalue FROM mytable...;"

Set rs = CurrentDb.OpenRecordset(strSQL, 4)

But is there any faster way to retrieve a single value from an SQL-server table, beside doing doing the select by a stored procedure running through a pass through query, then open a recordset

Set rs = CurrentDb.OpenRecordset("mypassthroughquery")
just to retrieve ONE value?

I could not find something like DLookup("...) for an SQL-Server or in T-SQL.

Any idea?

Thanks
Michael
 
It "depends*
If this is one record for a form, a recordset won't take that long.
The more a pure sql statement can be used, the more efficient the ODBC will automatically convert the SQL to TSQL. In majority of cases, it won't be worth creating a Stored Procedure for efficiency.
This means, that it is important at all cost to pass SQL any Access functions that are not native to TSQL.

It "depends" - my use of the following is for a Rules Engine. This rule is used for one record, not a problem. When it and dozens of others are used in a report for 10,000 records - it is being moved to SQL Server for efficiency.

This is an example of a function that passes in an ID to build a SQL statement. The SQL is translated into TSQL by the SQL ODBC (SQL Server Native Client in my case). The logic is completed back in MS Access in this case based on a record count value returned.
Passing the If - Then into SQL Server would reduce the efficiency of indexing and TSQL translation.

Code:
Public Function SurfaceOrMineralOwnerIsFederal(ID_Well) As Boolean
' AKA Rule #8 For lease type St or Fee - either surface owner or mineral owner have FED
Dim SQLStr As String
Dim rstMisc                         As DAO.Recordset  
On Error Resume Next
10    SurfaceOrMineralOwnerIsFederal = False ' default tofalse
20    SQLStr = "SELECT Wells_Lease.ID_Wells, Wells_Lease.MineralOwnerID, Wells_Lease.SurfaceOwnerID " & _
" FROM Wells_Lease " & _
" WHERE (((Wells_Lease.ID_Wells)= " & ID_Well & ") AND ((Wells_Lease.MineralOwnerID) In (1))) " & _
" OR (((Wells_Lease.ID_Wells)= " & ID_Well & ") AND ((Wells_Lease.SurfaceOwnerID) In (1))); "
30    Set rstMisc = CurrentDb.OpenRecordset(SQLStr, dbOpenDynaset, dbSeeChanges)
50            rstMisc.MoveLast    ' should only be one record in this case, just for reference
60              If rstMisc.RecordCount > 0 Then
70                  SurfaceOrMineralOwnerIsFederal = True
80              Else
90                  SurfaceOrMineralOwnerIsFederal = False
100             End If
110             If Err.Number <> 0 Then
120                 Err.Clear
130                 Exit Function
140             End If
            Set rstMisc = Nothing
End Function
 
http://www.sqlservercentral.com/articles/Access/69416/
Just as a follow-up to using Access IIF or DLookup to SQL Linked tables
This is a lenthy, yet very detailed article about how to convert a MS Access request with IIF or DLookup into TSQL (T-SQL) for efficiency.
Be sure and wear sunglasses... the Colors are very bright!
If it is a onece in a while during a session, let ODBC translate the result.
However, it this is being used in a Report's query or other intensive use, then converting might be worth the time.

In my case, prototyping in Access during the design phase is quick to code, but can take a long time to run the validation reports. Once the Subject-Matter-Experts sign off on the quality of the specific rule, then it is worth converting it to T-SQL.
The result T-SQL Code can be run side-by-side with the Access code for Quality validation. Then, the final product uses the much faster T-SQL code.

For once in a while, this kind of effort should be justified. Will saving a few miliseconds here and there justify a couple of hours of a programmer's time? That depends on the workload and backlog of the programmer.
 
Sometimes I use a parameterised Stored Procedure to directly return a value.
 

Users who are viewing this thread

Back
Top Bottom