How to make a local table based on results of a remote sql stored procedure

[Quizzed]

Hi, From a MS Access97 db i am attempting to write a function that will run a SQL 2000 stored procedure(SP). As part of the function, i need to add some selection criteria that will be used at the point the SP is executed.

I have defined the ODBC settings and tested these ok along with the selection citeria of Date / DepotID / ContractID

How do i call / execute the SP to make a local table of the results whilst also apply the selection criteria?

An example of one of my attempts is below;

DoCmd.Execute "SELECT SPNAME.* INTO " _
& "[LocalTableName] WHERE DeliveryDate = 'datDeliveryDate' & DepotID = 'DepotID' & ContractID = 'ContractID'"

At this current time, the above code appears to run without error but no records are returned - as if it hasn't run at all.
Thanks inadvance

[sierra7]

Hi,
I presume by testing the link you mean your SP is listed in the Queries tab of Access and when you double click on it, it opens and shows the data in Datasheet view?

If this does not happen then your code won't run. (Well, It might but you won't get any data!)

To link to an SP you need to create a Pass-Through query and set the 'Returns Records=Yes' in the property sheet.

Personally, for the simple task in hand I would have created a view or linked directly to the table.
S7

[sierra7]

Just noticed that you have missed out a FROM clause in your SQL statement
S7

[Quizzed]

Thanks S7, appreciate your comments.
Rather than complicate my first attempt with an insert, i have changed my query to be the following;

DECLARE @DeliveryDate smalldatetime
DECLARE @DepotID int
DECLARE @ContractID int

set @deliverydate = '2011.11.09'
set @depotid = 43
set @contractid = 21

EXEC AMT_DipsOrderSummaryByDate @DeliveryDate, @DepotID, @ContractID

The SP does exist within the Queries tab and the return records para is set to Yes. Unfortunately no records are being returned. The msg box is;

Pass-through query with ReturnRecords property set to True did not return any records.

Appreciate if you can suggest a next step. Tks.

[sierra7]

Q,
If you go this route your parameters are hard coded into the SP

If you have access to the SQL Management Studio just create a View (if you are joining tables) or link directly to the table (in Access;- External Data,ODBC,Link option, select the DSN, then point to the table name or View)
If you then substitute this name for SPNAME in your first post, add a FROM clause and all should be ok.
S7