DeanCovey
Posts: 22
|
| Posted: 02/24/2007, 7:44 AM |
|
I have seen several post regarding running Access queries from CodeCharge. This is simple. You can run update and delete queries.
First of all, create your query in Access like normal. If you are going to limit the records to be updated, then go ahead and create a generic prompt on the field you want to filter on. I use the field name in my prompt so . This will be important in ASP.
This example, a query named DedPost_Global, has multiple fields being filtered, but only one prompt, Which Header:
UPDATE PayrollDetail SET PayrollDetail.Ext = DLookUp("[Calc]","DedCalc_Global","[PayID] =" & [PayID])
WHERE (((PayrollDetail.PayHeaderID)=[Which Header]) AND ((PayrollDetail.PayFldType)<>"Income") AND ((PayrollDetail.OverrideCalc)<>-1));
To execute this query on some event,
Dim tSQL
tSQL="DedPost_Global '" & CCGetParam("HeaderID",-1) & "'"
dbconnection.Execute(tSQL)
To connect the name of the query and your parameter(s) be sure to add ' around each parameter. The parameters must be in the exact order as they come up when you manually open the query in Access. 
Additional Notes: I have also tried putting this string directly into the execute function instead of using a variable. I could not get that to work. Most functions in Access seem to work. I have not tried the nz() function for example. Also I have not tried custom functions in Access either. I hope to get time to try soon. I'll probably add to this article.
Hope this helps others.
|
 |
 |
|
