How to access SQL Server scalar funtions in MS Access VB

Question

Hello,

I'm trying to find out how I can access scalar functions from the SQL Server database of my adp file.

I have an adp file in access 2007 and a SQL Server 2005 DB.

In access VB I try to get the result of a scalar function with parameters.

I tried with DAO, but it that case currentdb = nothing:

  Dim dbs As DAO.Database
  Dim sql2 As String
  Dim txt As String
  Dim iCount As Integer

  Set dbs = CurrentDb
  txt = "SELECT * FROM dbo.TBL_Klanten" '(tbl_klanten is in the msql DB) '
  dbs.Execute txt, dbFailOnError

Thanks on advance,

Geert

Answer 1

DAO was never designed to access sql server even though its possible.

I believe the CurrentDB property is a DAO connection referencing the access database, and may not work with ADP, even though I don't really know since I've never used them.

ADO is the way you want to go with this.

I'm also assuming you're sticking with VBA rather than doing anything with .NET.

ADO API reference

ADO Objects reference

Answer 2

Use CurrentProject.Connection, which should be your connection to SQL Server. You need to use ADO with CurrentProject.Connection. It's been a while since I've used ADP, but I just created this test code which looks similar to what you're attempting, and it seems to work OK.

Public Sub foo()
    Dim rs As ADODB.Recordset
    Dim strSql As String
    strSql = "SELECT * FROM Facilities;"
    Set rs = CurrentProject.Connection.Execute(strSql)
    Debug.Print rs(0)
    rs.Close
    Set rs = Nothing
End Sub

But I really don't know if this is the best way to proceed. It sounds like your TBL_Klanten may be a user-defined function. With my test ADP, and Access 2003, the UDFs are displayed in the Queries section of the Database Window. So I suspect there may be a better way to use them, but I don't know how.

Edit: Since you mentioned parameters, here is an example with a UDF which expects a string value.

Public Sub foo2()
    Dim rs As ADODB.Recordset
    Dim strSql As String
    strSql = "SELECT * FROM UDF_GetRecipientByAlias('VM2003\hans');"
    Set rs = CurrentProject.Connection.Execute(strSql)
    Debug.Print rs(0)
    rs.Close
    Set rs = Nothing
End Sub