DMax lookup in sql server

[DataMiner]

I want to convert a DMax function that I use in my 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?