Create an access application sourced from SQL stored procs to create 3 different files
Posted on 2011-09-02
the reason I have asked the whole is in case i can avoid any common pitfalls.. and will break this down into multiple q's if i have too.
I would like to have a form with a combobox in Access 2000 or Access 2003 which will display the names of the stored procedures I (or a user) have access to in my SQL 2005/2008 database.
Most likely be making this into an an MDE file. This would be distributed to around 5 users.
Once I have selected a stored procedure it will dynamically create additional comboboxes below, depending on how many and listing the with the optional parameters that I have specified in my stored procedure.
Once I have selected the parameters, I want to be able to choose
- Excel file then when clicking on a 'OK' button this would export the results to a new .xls file that I would specify in the destination txtbox
- Access Database then when clicking on a 'OK' button this would export the results to a new table in an .mdb file that I would specify in the destination txtbox
- Excel Pivot Table, this would allow additional form elements to be created allowing me to assign any of the fields returned by the stored procedure e.g.
Category1 to Page field
Category2 To Row Field
Category3 To Row field
Category4 to Column Field
And then I could choose a the data field and an aggregate function
e.g Count, SUM etc but more specifically Count UNIQUE that i could sent to the data area.
Then I could specify the destination xls file in a textbox then when clicking on a 'OK' button this would create the new .xls file that I would specify in the destination txtbox
any errors would end the procedure and write to a text file for debugging.
Is this asking to much? I realise the 1% inspiration 99% perspiration might very much apply to this.