March 17, 2006 at 9:51 am
For the life of me I can't find information on how to do this although I'm sure it's very basic.
What I need to do is simply use a form inside of Access to enter in date informtion (Start / End) and then be able to create a pass-through query that will take the date information from the form and call the stored proc with the parameters.
Anyone can answer this for me please?
March 17, 2006 at 10:30 am
One older method is to create a table which holds the start and end date, then use the dlookup function to pass the dates.
March 18, 2006 at 9:54 am
Is the question how do you call a stored procedure from Access? THis should do it:
Public Function InsertDate( _
strTableName As String, _
strStartDate As String, _
strEndDate As String, _
On Error GoTo Error_Handler
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open fstrADODBConnectionString()
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = strSP
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@TableName", adVarChar, adParamInput, 20, strTableName)
.Parameters.Append .CreateParameter("@StartDate", adVarChar, adParamInput, 20, strStartDate)
.Parameters.Append .CreateParameter("@EndDate", adVarChar, adParamInput, 20, strEndDate)
.Execute
End With
Exit_Handler:
Exit Function
Error_Handler:
ShowError
Resume Exit_Handler
End Function
March 19, 2006 at 9:51 am
is there no easy way to simply do something like create a pass-through query and have something along the following:
exec testquery Forms!Test!Start,Forms!Test!End
I thought for sure I'd seen such a beast but can't find any sort of way to do it myself.
March 20, 2006 at 4:36 pm
Sure.
The easiest way is to set up the pass-through query then change its SQL and execute it.
If your pass-through is qryPassThrough, then
Function QdEfMe()
Dim qDef As QueryDef
Set qDef = CurrentDb.QueryDefs("qryPassThrough")
qDef.SQL = "exec testquery " & Forms!Test!Start & ", " & Forms!Test!End
qDef.Close
'for action query:
qDef.Execute
Set qDef = Nothing
'for select query:
'DoCmd.OpenQuery "qryPassThrough"
End Function
Let me know if it doesn't work for you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply