Launching a presaved UPDATE query in Access from ASP

[Nsynan]

I am working with an Access2000 data base that has an extremely large update query. This query does not collect information from a form but rather takes over 100 fields from one table and updates them to another table. Is there a way to have a user click on a button or a link on an ASP page to launch this update query? The SQL statement in the Access update query uses SWITCH() and other Access functions so it would not work if I cut and pasted the update query string into ASP.

 

[codestorm]

You can use ADO to call an Access Query object the same way you'd use it to call an MSSQL Server stored procedure. <insert witticism here>
codestorm

 

[Nsynan]

How do I do that?

 

[codestorm]

This is done from memory - haven't had to use Access dbs in a while.
Also this has no error handling.
Finally this requires global.asa to reference msado15.dll or similar to enable use of constants referred to below (e.g. adParamInput)

Say you have an Access query object called
MoveSomeRecords
which does something like
PARAMETERS thisnumber Long;
insert into table2
select * from table1
where field1=thisnumber;

Your ASP code might be something like
<%
dim obj_Connection
dim obj_Command
dim obj_Parameter
dim str_Connection
dim lng_ThisNumber

lng_ThisNumber= clng(Request.Form(&quot;thisnumber&quot)
str_Connection= &quot;driver=Microsoft Access (*.mdb);dbq=&quot; & Server.MapPath(&quot;<database-name-here.mdb>&quot

obj_Connection.Open str_Connection
obj_Command.ActiveConnection= obj_Connection
set obj_Parameter = obj_Command.CreateParameter(&quot;thisnumber&quot;, adInteger, adParamInput, 4, lng_ThisNumber)
obj_Command.Parameters.Append obj_Parameter
obj_Command.CommandType= adCmdStoredProc
obj_Command.Execute

set obj_Parameter= nothing
set obj_Command= nothing
set obj_Connection= nothing
%>

<insert witticism here>
codestorm

 

[Nsynan]

Well guys,

Thanks for your help, but I believe there are no objects in Access classified as a &quot;Stored Procedure&quot;, therefore the .CommandType = 4 or CommandType = adCmdStoredProc the ASP code has issues with executing the command (error: &quot;Data type mismatch in criteria expression.
.&quot. If I changed the .CommandType = 2 (query) I get the error &quot;An action query cannot be used as a row source.&quot;

 

[codestorm]

No, from my experience adCmdStoredProc is correct - Query objects are MS Access' (limited) version of SPs.

The error sounds to be caused by problems with parameters in the where clause - perhaps what you are passing in? <insert witticism here>
codestorm

 

[Nsynan]

There are no parameters being passed, just calling a completely self contained access &quot;UPDATE&quot; query. If you're in Access, you just double click on the update query and it warns you that you're about to update several hundred rows...

 

[codestorm]

2 things:
1. from memory of Access you can use set warnings off os something similar.
2. in the ASP are trying to return the query into a recordset? <insert witticism here>
codestorm

 

[Nsynan]

No recordset is being return, just updating fields from one table to another. When the update query is double clicked on in Access, the user sees nothing other than the warning message, he then click ok, and only the hour glass shows until the update has completed.

 

[codestorm]

Yes but when you are trying to execute this from ASP what is the code you are using?

(i'll continue in the morning - 1am here - going to sleep) <insert witticism here>
codestorm