Software: Microsoft Access 2000, SQL Server 2000
I’m trying to figure out how to bring data down from an SQL server into Access using an ADO SHAPE command against a stored procedure with up to 18 parameters.
I played with this a bit in the Data Environment Designer. I came up with this SHAPE command:
I can’t figure out how to make this work in DED, but I really would prefer to use it in ADO, using something like:
My problem is that I can't figure out the correct syntax, (especially how to get the parameters in there).
This is for a sales history application that has millions of records. It is a legacy system, so it is a read-only database.
The user can select from up to 18 parameters (9 from/to pairs), and can choose which ones to include and group by in the output. So first I get the data from the stored procedure (using an ADO command object), then create a SELECT…GROUP BY statement at runtime to display the results the user wants. I’ve got a process that works, but it uses two temp tables and is really slow.
It looks as if the ADO SHAPE command might be able to do it in one step. I don’t know if this is the best way to handle this, but I’d like to try and see what happens.
Thanks
Rob
I’m trying to figure out how to bring data down from an SQL server into Access using an ADO SHAPE command against a stored procedure with up to 18 parameters.
I played with this a bit in the Data Environment Designer. I came up with this SHAPE command:
Code:
SHAPE {{? = CALL dbo.sp_SalesHistory( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }} AS Command1 COMPUTE Command1, SUM(Command1.'sum_qty_invoiced') AS qty_invoiced, SUM(Command1.'sum_price_extended') AS price_extended BY 'ship_to_business','input_period','part_number','customer_group'I can’t figure out how to make this work in DED, but I really would prefer to use it in ADO, using something like:
Code:
strSQL = “SHAPE …” ‘built from user input
rs.Open strSQL …My problem is that I can't figure out the correct syntax, (especially how to get the parameters in there).
This is for a sales history application that has millions of records. It is a legacy system, so it is a read-only database.
The user can select from up to 18 parameters (9 from/to pairs), and can choose which ones to include and group by in the output. So first I get the data from the stored procedure (using an ADO command object), then create a SELECT…GROUP BY statement at runtime to display the results the user wants. I’ve got a process that works, but it uses two temp tables and is really slow.
It looks as if the ADO SHAPE command might be able to do it in one step. I don’t know if this is the best way to handle this, but I’d like to try and see what happens.
Thanks
Rob
