Where to go for help: Execute SP from Access form

[wireless bill]

Can someone tell me how to do this, or where I can learn? I need a way to
retrieve data from SQL Server into Access, using an existing stored procedure
that accepts two parameters (for a date range) that are specified on an
Access form.

This is what I have:
-- An Access 2000 database, used as a front-end GUI.
-- A SQL Server 2000 database, to hold the data and the stored procedures.
-- My PC has an ODBC connection to the SQL Server database.

This is what I need to do:
-- User selects a time period from a combo box on the Access form.
-- User clicks a button on this form to "run the import process."
-- In VBA, the click event connects to SQL Server, executes the SQL Server's
stored procedure using the parameters from the Access form, and creates a new
table of the result set.
-- The stored procedure is long and complex, and it's maintained by another
work group, so I just want to refer to it. The final section goes like this:

SELECT DISTINCT
#EventInfo.Type as Type,
#Corp.corporate_account,
etc..... several columns
FROM #TempTable
GO

I've tried a pass-through query and sample code using ADO and DAO, and
nothing seems to work. Is it possible to do this? Any help you can lend
would be much appreciated! (I'm not sure which discussion group I should
post this to.)

 

[Douglas J. Steele]

You cannot have a pass-through query recognize parameters. You must
dynamically change the SQL of the QueryDef object before you run it.

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

strSQL = "CALL SQLServerSP @Date1='" & _
Me.cboDate1 & "', '" & Me.cboDate2 & "'"

Set dbCurr = CurrentDb
Set qdfCurr = dbCurr.QueryDefs("MyPassthroughQuery")
qdfCurr.SQL = strSQL

qdfCurr.Excecute dbFailOnError

 

[Chuck Wood]

If the stored procedure has parameters, you can use the ADO Command object's
Parameters collection to pass parameters to and from the stored procedure.

 

[wireless bill]

Douglas and Chuck:

Thank you for your replies. The code that Douglas supplied is a slick way
to build a query dynamically; the passing of parameter values from the form
to the query works great.

However, I'm still working through this, because Access is now returning
error 3065 "Cannot execute a select query" when it reaches the Execute
statement. I've seen Julie's 8/31 post in Access.Queries regarding error
3065, but I don't know how to apply the solution to my case. I'm not sure if
the problem is in my Access VBA code or with the SQL Server procedure.

After I solve this immediate issue, I need to figure out how to store the
data in a table. This is what I've written so far, but it has not yet been
tested.

Set rst = qdf.OpenRecordset
Do While Not rst.EOF
sSQL = "INSERT INTO tblTEST_THIS ( <columns> ) SELECT rst!Type,
rst!<etc...> "
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
rst.MoveNext
Loop

And then I close my objects. Seems clumsy, but it's all I can come up with.

I'm still looking through other reference materials (between meetings), but
if you have other thoughts to help me through this, that would be wonderful!

-- Bill

 

[Douglas J. Steele]

Absolutely correct: Execute only works on Action queries (Update, Insert,
Delete)

I'm assuming you're actually storing the pass-through query as, say,
qryPassThrough.

Try creating a second query

INSERT INTO tblTEST_THIS ( <columns> ) SELECT <fields> FROM qryPassThrough

and executing it.

This second query would not change.

 

[wireless bill]

Doug --

Brilliant! This works perfectly! I ran through a test and it behaved
exactly as I hoped. Thank you very much for your assistance on this!

-- Bill