Replace MS Access DMax lookup in SQL server

Question

Post reply

Replace MS Access DMax lookup in SQL server

She Through Whom All Data Flows

Ten Centuries

Points: 1209
More actions
April 21, 2009 at 4:18 pm

#199726

I want to convert a DMax function that I use in my MS Access vb code to something that will more efficiently run on the server.
code currently is:
ddate=dmax("rundate","runlog","itemname='myitem')
RunLog is a SQL server table that's currently linked to my db frontend.
So I thought instead I could just use a passthrough temporary query and do something like:
:
Function GetMaxRunDate(strItemName As String) As Date
Dim DB As dao.Database, Qdef As dao.QueryDef
Set DB = CurrentDb
Set Qdef = DB.CreateQueryDef("", "select max(rundate)as maxrundate from runlog where itemname='" & strItemName & "'")
Qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=dtmevtmsql01;UID=pcamain-user;PWD=MainUsr#3;DATABASE=PCAMain"
Qdef.ReturnsRecords = True
'GetMaxRunDate=????? how do I use the results???
End Function
The SQL works fine as a pass-through query , but how do I set this up to use the results in my code?
I see that I could make this a NON-temporary querydef, and then still use dlookup within Access to return the value to the code, something like:
:
Function GetMaxRunDate(strItemName As String) As Date
Dim DB As dao.Database, Qdef As dao.QueryDef
Set DB = CurrentDb
Set Qdef = DB.CreateQueryDef("MyNewQuery", "select max(rundate)as maxrundate from runlog where itemname='" & strItemName & "'")
Qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=dtmevtmsql01;UID=pcamain-user;PWD=MainUsr#3;DATABASE=PCAMain"
Qdef.ReturnsRecords = True
GetMaxRunDate=dlookup("maxrundate","mynewquery")
End Function
But that seems sorta bass-ackward.
Or should I somehow be using a stored procedure or SQL user function instead?
__________________
She Through Whom All Data Flows
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

You want, I think, to look at the CASE statement. Something like

select

case when rundate > runlog then myitem

else 'something else'

Or is rundate in Access?

If it is, I'm not sure what you can do here. You're joining data from two disparate sources, so one has to do the comparison and pull the data from the other one.

jfmccabe SSCrazy Points: 2547 More actions · Answer 2

Basically, you are along the right lines.

You should create a stored procedure

CREATE PROCEDURE ProcMaxDate

(@ItemName NVARCHAR(70) )

AS

select max(rundate)as maxrundate from runlog where itemname= @ItemName

GO

Create a Pass Through query directly in Access with SQL:

ProcMaxDate 'Temp'

and suitable connection string etc call it qpMaxDate

The Access function is now:

Function GetMaxRunDate(strItemName As String) As Date

Dim db As DAO.Database

Dim qdf As DAO.QueryDef

Set db = CurrentDb

Set qdf = db.QueryDefs("qpMaxDate")

qdf.SQL = "ProcMaxDate '" & strItemName & "'"

db.QueryDefs.Refresh

GetMaxRunDate=dlookup("maxrundate","qpMaxDate")

End Function

OK it's a bit bass ackward but it does work. You only create the pass through query once and then modify the SQL each time. You don't need a stored procedure, it's just that bit neater rather than passing SQL directly to the server.

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 3

She Through Whom All Data Flows (4/21/2009)
I want to convert a DMax function that I use in my MS Access vb code to something that will more efficiently run on the server.
:
Function GetMaxRunDate(strItemName As String) As Date
Dim DB As dao.Database, Qdef As dao.QueryDef
' (1) add this line:
Dim rs As dao.RecordSet
Set DB = CurrentDb
Set Qdef = DB.CreateQueryDef("", "select max(rundate)as maxrundate from runlog where itemname='" & strItemName & "'")
Qdef.Connect = "ODBC;DRIVER=SQL Server;SERVER=dtmevtmsql01;UID=pcamain-user;PWD=MainUsr#3;DATABASE=PCAMain"
Qdef.ReturnsRecords = True
'GetMaxRunDate=????? how do I use the results???
' (2) add these two lines:
Set rs = qdf.OpenRecordset()
GetMaxRunDate = rs("maxrundate")
End Function
The SQL works fine as a pass-through query , but how do I set this up to use the results in my code?

She Through Whom All Data Flows Ten Centuries Points: 1209 More actions · Answer 4

Thanks for the replies. William and Jim, it seems like your solutions are basically equivalent to what I'm alread doing, so I guess I'll just keep it as is. Seems to be working OK.

I don't see how the select case statement would help me...? The rundate info is all in sql server. I just want to lookup a single date and return it for use in my Access VBA code. The main issue is how to get the value to the code. Apparently I do have to do this via first creating some sort of recordset in Access for the code to look in.

Thanks again,

Vickie

[font="Comic Sans MS"]She Through Whom All Data Flows[/font]

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 5

Your first code example was almost complete, except for opening the recordset to retrieve the value.

Your second code example will only work the first time, then it will fail with error 3012 "Object 'mynewquery' already exists."