vba code for pass through query to get SQL Server server name

Question

I would like to use code to create a temporary pass through query to get the server name of a SQL Server db that Access is attached to. I can create a pass through query manually, set the ODBC connect string  and run it without a problem. But I'm hoping there is a way to use code to do the same, so that I don't have an extra object in the accdb. My code starting point is below (which does not work)

Public Function GetServerName()

    Dim qdf As dao.QueryDef

    Set qdf = CurrentDb.CreateQueryDef("")

    qdf.SQL = "SELECT @@SERVER AS 'ServerName'"

    qdf.Connect = "ODBC;DSN=JP6700;UID=SYSADM;PWD=sysadm;APP=Advantage"

End Function

I get an error on qdf.sql, presumably because there is no from clause. ps - there are numerous users with different ODBC connections using the same accdb.

Answer 1

These both work for me:

These two examples work for me:

Public Function GetServerName() As String

With CurrentDb.QueryDefs("qryPassReturn")

.SQL = "select @@SERVERNAME as 'sname' "

GetServerName = .OpenRecordset()(0)

End With

End Function

Public Function GetServerName2() As String

Dim rst              As DAO.Recordset

Dim ServerName       As String

With CurrentDb.QueryDefs("qryPassReturn")

.SQL = "select @@SERVERNAME as 'sname' "

Set rst = .OpenRecordset

GetServerName2 = rst(0)       ' or rst!sname

End With

End Function

In your sample, you execute the command but don’t grab or set any return values.

In my above examples, I also don’t set the connection string, but in fact saved the connection string when I saved the above pass-though.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 2

The correct command is:

SELECT @@SERVERNAME AS 'ServerName'

So above should work assuming your connection is correct.

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 3

Thank you - this worked for me as well when I created a pass through query first (e.g. "qryPassReturn".

Initially, I was trying to create a querydef with an empty string, so as to not add another query object to the accdb. Perhaps this is not doable.

Answer 4

Thanks, my mistake - but I still get "syntax error -missing operator"