Error when calling a stored procedure from the MS Access code

[accesser2003]

The following code is written inside a public function in my MS Access project Module. It calls a stored procedure which its work successully when I execute it directly. But when I call it from this function, the error generated is "ODBC call failed"


Dim BuildAttendanceEvents_GroupByEmp As QueryDef

Dim LoadFrom As Date
Dim LoadTo As Date

DoCmd.OpenQuery "TruncAttendanceEvents_GroupByEmp"

LoadFrom = FromDate
LoadTo = DateAdd("d", 1, ToDate & " " & "23:59:59")

Set BuildAttendanceEvents_GroupByEmp = CurrentDb.CreateQueryDef("")
BuildAttendanceEvents_GroupByEmp.Connect = ("ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes")
BuildAttendanceEvents_GroupByEmp.ReturnsRecords = False
BuildAttendanceEvents_GroupByEmp.ODBCTimeout = 60
BuildAttendanceEvents_GroupByEmp.SQL = "BuildAttendanceEvents_GroupByEmp" & " " & LoadFrom & "," & LoadTo & ";"
BuildAttendanceEvents_GroupByEmp.Execute

 

[DJkarl]

The following code is written inside a public function in my MS Access project Module. It calls a stored procedure which its work successully when I execute it directly. But when I call it from this function, the error generated is "ODBC call failed"


Dim BuildAttendanceEvents_GroupByEmp As QueryDef

Dim LoadFrom As Date
Dim LoadTo As Date

DoCmd.OpenQuery "TruncAttendanceEvents_GroupByEmp"

LoadFrom = FromDate
LoadTo = DateAdd("d", 1, ToDate & " " & "23:59:59")

Set BuildAttendanceEvents_GroupByEmp = CurrentDb.CreateQueryDef("")
BuildAttendanceEvents_GroupByEmp.Connect = ("ODBC;DSN=BAHRAIN;Description=BAHRAIN;UID=Administrator;DATABASE=Bahrain;Trusted_Connection=Yes")
BuildAttendanceEvents_GroupByEmp.ReturnsRecords = False
BuildAttendanceEvents_GroupByEmp.ODBCTimeout = 60
BuildAttendanceEvents_GroupByEmp.SQL = "BuildAttendanceEvents_GroupByEmp" & " " & LoadFrom & "," & LoadTo & ";"
BuildAttendanceEvents_GroupByEmp.Execute

I don't see FromDate or ToDate defined anywhere in your code, you might be getting an error if these aren't populated with valid dates.

 

[boblarson]

Why are you building a connection to this? If this is an Access Project (adp) you are already connected to it.

 

[accesser2003]

It is no access object. I want to make it through code.