asked on

Calling a SQL function from MS Access

I wathced the video on the EE webstie on how to call a SQL Scalar and Table function from MS Access. I thought I got the steps down. However, I am running into a bit of problem trying to apply what I saw to calling a table function that requires two dates to return output to a PassThru query.

First here is my SQL Table Function. It works fine when running it from a SQL stored procedure.

USE [livedb]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_getHRTurnovers]    Script Date: 07/05/2015 11:48:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




Create FUNCTION [dbo].[fn_getHRTurnovers]
    (
     @BDate datetime
    ,@EDate datetime
    )
    
    
RETURNS TABLE
AS
RETURN

    /* Prints out all the applicable config parameters and their respective values for the requested report in one XML line */
    (SELECT     TOP (100) PERCENT EmployeeID, EffDt, NewDeptCode, OldPos, MAX(OldPosTitle) AS OldPosTitle, NewPos, MAX(NewPosTitle) AS NewPosTitle, Name, 
                      RIGHT(NewJobCode, 4) AS JobCode, CASE WHEN NewPos = OldPos THEN 0 ELSE 1 END AS TrnsfrChk
FROM         dbo.vw_TransferData
GROUP BY EmployeeID, EffDt, NewDeptCode, OldPos, NewPos, Name, NewJobCode
HAVING      (EmployeeID IS NOT NULL) AND (EffDt > @BDate) and (EffDt < @EDate)
ORDER BY EmployeeID)

Open in new window

Next, here is my Access Pass Thru query:

It is call "qpass"

Select * from dbo.fn_getHRTurnovers(@BDate,@EDate);

Finally here is the VBA within Access to call the function:

Option Compare Database

Sub ExecuteSQLFunction()
    Dim qdef As DAO.QueryDef
    Set qdef = CurrentDb.QueryDefs("qpass")
    Dim qBDate As Date
    Dim qEDate As Date
    Dim strDate1 As String
    Dim strDate2 As String
    strDate1 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qBDate)
    strDate2 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qEDate)
    qry.SQL = "Select * from dbo.fn_getHRTurnovers  (strDate1,strDate2)"
    qdef.Close
    Set rst = CurrentDb.OpenRecordset("Select * from qpass", dbOpenDynaset)
    
    
    
    
End Sub

Open in new window

When I try to run this, I get Time variables, not Date.

I have attached the expected outout when I enter 12/31/2014 and Today as the date parameters.

Can someone help with the proper syntax?

Thanks
new-employees.xlsx

ste5an

Yup, you need to insert T-SQL date literals. The most common is 'yyyymmdd'. So I would use a helper function:

Public Function SqlDate(ADate As Date) As String

  SqlDate = Format(Now, "'yyyymmdd'")

End Function

Open in new window

and

qry.SQL = "SELECT * FROM dbo.fn_getHRTurnovers  (" & SqlDate(strDate1) & " , " & SqlDate(strDate2) & ");"

Open in new window

GPSPOW

ASKER

I tried the solution. I am getting the following error:

ByRef argument type mismatch.

Here is what I input into VBA:

Option Compare Database

Sub ExecuteSQLFunction()
Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qpass")
Dim strDate1 As String
Dim strDate2 As String
strDate1 = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", SqlDate(strDate1))
strDate2 = InputBox("Please enter a Beginning and Ending Date", "Enter Ending Date", SqlDate(strDate2))
qry.SQL = "Select * from dbo.fn_getHRTurnovers (" & SqlDate(strDate1) & ", " & SqlDate(strDate2) & ");"

qdef.Close
Set rst = CurrentDb.OpenRecordset("Select * from qpass", dbOpenDynaset)

What could be wrong?

Thanks

Glen

End Sub
Public Function SqlDate(ADate As Date) As String
SqlDate = Format(Now, "'yyyymmdd'")
End Function

ste5an

Don't touch the date input lines..

GPSPOW

ASKER

i re-wrote the function and vba per your suggestions.

Please see attached and let me know why I am still getting a ByRef argument error.

thank you

Glen
SQL-Access-Function.txt

ste5an

It's a type mismatch:

Option Compare Database
Option Explicit

Public Sub ExecuteSQLFunction()

    Dim qdef As DAO.QueryDef
    
    Dim qBDate As Date
    Dim qEdate As Date
    
    qBDate = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qBDate)
    qEdate = InputBox("Please enter a Beginning and Ending Date", "Enter Beginning Date", qEdate)

    Set qdef = CurrentDb.QueryDefs("qpass")
    qdef.SQL = "Select * from dbo.fn_getHRTurnovers (" & SqlDate(qBDate) & " , " & SqlDate(qEdate) & ");"
    qdef.Close
    
End Sub

Public Function SqlDate(ADate As Date) As String
    SqlDate = Format(Now, "'yyyymmdd'")
End Function

Open in new window

btw, please use Option Explicit in all your code modules. See Tools\Options in the VBA IDE.

GPSPOW

ASKER

I now have the qPass query working. However, the SqlDate function is setting the qBDate and qEDate to today's date, not what is input, so the output is null.

I am inputting 2014-12-31 and 2015-06-30 as my qBDate and qEDates.

Also, when the input screen displays, the format is a time not a date.

Thanks

Glen

ste5an

Decouple your function from the user interaction:

ublic Sub ExecuteSQLFunction(ABeginDate As Date, AEndDate As Date)

    Dim qdef As DAO.QueryDef
    
    Set qdef = CurrentDb.QueryDefs("qpass")
    qdef.SQL = "Select * from dbo.fn_getHRTurnovers (" & SqlDate(ABeginDate) & " , " & SqlDate(AEndDate) & ");"
    qdef.Close
    
End Sub

Open in new window

GPSPOW

ASKER

I have been tinkering with this for a few days and have a qorking pass thru query to a SQL procedure, instead of a function.

Exec dbo.sp_getHRTurnovers_View_Test '2015-01-01','2015-03-31'

What i want to do is replace the hardcoded dates for two [TempVars] - "DateFrom" and "DateTo"

Here is what I tried but the syntax is not right:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb

Set qdf = db.QueryDefs("qpass_transfers")
qdf.SQL="Exec "dbo.sp_getHRTransfers_View_Test' " &[TempVars]![DateFrom]&","&[TempVars]![DateTo]&'" "

I get messed up on the apostrophes and qutoes.

Can someone help me out?

Thanks

Glen

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

thanks