How to access SQL Server scalar functions in MS Ac

Question

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

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