Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
stored query change possible?
- Thread starter kingz2000
- Start date
There are many ways to skin that particular cat, perhaps if you explained what you intend to do with the results a more directed response could be delivered.
While you cant pass variables to a query as you would a stored procedure you can mimic this pass if you have a function which collects the variable as the criteria for a field. This might help. Or (simpler) dont use a query but use a recordset. I'm not aware of being able to change the underlying SQL of a query in the way you can with a Table using ALTER but you could just delete and recreate the query easliy enough. Though to do so is a bit redundant as a recordset does the job more easily.
Want the best answers? See FAQ181-2886
While you cant pass variables to a query as you would a stored procedure you can mimic this pass if you have a function which collects the variable as the criteria for a field. This might help. Or (simpler) dont use a query but use a recordset. I'm not aware of being able to change the underlying SQL of a query in the way you can with a Table using ALTER but you could just delete and recreate the query easliy enough. Though to do so is a bit redundant as a recordset does the job more easily.
Want the best answers? See FAQ181-2886
Using DAO, it seems changing the SQL contents of the query is rather popular. I don't use DAO so I wouldn't know, but do a search for querydefs (help file and here), and you'll probably find samples where the .sql property of the querydef object is altered.
Changing queries in ADO is also possible (would be using ADOX), though I can't say I've ever heard much recommandation for it - but here's a Microsoft article Creating and Modifying Stored Queries in Microsoft Access Databases with ADOX
But passing parameters to stored queries - here are some samples - more can probably be found through search
Paramters to stored queries in ADO thread709-1019057
This has some suggestions with DAO and some ADO thread702-946750
here resolving parameters from forms in ADO, note there's a difference between 2000 and later versions thread709-819033
Roy-Vidar
Changing queries in ADO is also possible (would be using ADOX), though I can't say I've ever heard much recommandation for it - but here's a Microsoft article Creating and Modifying Stored Queries in Microsoft Access Databases with ADOX
But passing parameters to stored queries - here are some samples - more can probably be found through search
Paramters to stored queries in ADO thread709-1019057
This has some suggestions with DAO and some ADO thread702-946750
here resolving parameters from forms in ADO, note there's a difference between 2000 and later versions thread709-819033
Roy-Vidar
- Thread starter
- #4
Ok, I'll explain what I want to do:
I am using Views and tables from 2 different DB Schemes for a crystal Report Project which consists of approximately 10 reports. I get the information for my reports via stored queries in access of the linked tables and views from Oracle! The point of having Access in between Crystal Reports and the underlying Oracle Database was merley for a more controlled access.
You see, my stored queries have 2 fators which I eventually want to control on an access form: the customerID and the date!
Now a little form witha combo of the different customerIDs and a combo for the moth should specify, or update my stored procedure to the relevant SQL which I can then just give Crystal Report.
So what is the best way for me to update my stored procedure?? a bit of code would be cool..*G*
I am using Views and tables from 2 different DB Schemes for a crystal Report Project which consists of approximately 10 reports. I get the information for my reports via stored queries in access of the linked tables and views from Oracle! The point of having Access in between Crystal Reports and the underlying Oracle Database was merley for a more controlled access.
You see, my stored queries have 2 fators which I eventually want to control on an access form: the customerID and the date!
Now a little form witha combo of the different customerIDs and a combo for the moth should specify, or update my stored procedure to the relevant SQL which I can then just give Crystal Report.
So what is the best way for me to update my stored procedure?? a bit of code would be cool..*G*
- Status
Similar threads
- Locked
- Question
- Locked
- Question