SQL Server Functions & Access ADPs

[gavinjb]

Hi,


I am fairly new to writing Access ADP Projects, and am looking at how I can run SQL Server functions and return the result to Access.



Thanks,


Gavin,

 

[steevie_t]

Select it and return the one row, one column recordset...

Sub GetDate()
Dim rst As New ADODB.Recordset

rst.Open "SELECT GetDate();", CurrentProject.Connection

Debug.Print rst.Fields(0)

End Sub

or even worse...

Sub GetDate()

Debug.Print CurrentProject.Connection.Execute("SELECT GetDate();").Fields(0)

End Sub

 

[gavinjb]

Hi

I wrote the following code

Sub GetAGLH()
Dim rst As New ADODB.Recordset

rst.Open "SELECT AGLH();", CurrentProject.Connection

Debug.Print rst.Fields(0)

End Sub

but all I get back is "AGLH is not a recognized function name"

I have checked to make sure that the user logged onto my ADP has permissions to exec the function

Thanks,


Gavin,

 

[FireStrike]

I think you will want to change aglh() to GetAGLH() as that is your sub name.

 

[gavinjb]

I am trying to access a SQL server function called AGLH(), GetAGLH is only the VBA function, which I want to use to retrieve the result into from SQL Server.

If I change this it deffinately wont work.

 

[gavinjb]

Found the problem I was referencing the function as AGLH(); when I should have referenced as dbo.AGLH();