Create an access application sourced from SQL stored procs to create 3 different files
Hi,
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
from radiobuttons
- 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.
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
from radiobuttons
- 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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the advice.
I will either use SSRS or
The quick and dirty way based on a few tables
I will use pass through queries to exec the sprocs
After the combobox selection is made, the form requeries.
and then the following should help:
DoCmd.TransferDatabase
DoCmd.TransferSpreadsheet
again thanks all for your avice it will save me hours of time.
I will either use SSRS or
The quick and dirty way based on a few tables
I will use pass through queries to exec the sprocs
After the combobox selection is made, the form requeries.
and then the following should help:
DoCmd.TransferDatabase
DoCmd.TransferSpreadsheet
again thanks all for your avice it will save me hours of time.
tables... that way you minimise you dependance on a specific version of sql server...