Entry

Is it possible to call a stored procedure in Access to insert records from ASP?

May 31st, 2000 07:00
unknown unknown, Jacob Sulzbach

You make the "stored procedure" in access as a query. (you can only call
queries from access). Then you make the same connection and call the 
query the same way you would for sql server or whatnot. The difference 
is that you can only call queries AND you cannot have return parameters 
(other then the number of records effected).

The conection goes like this:
set comQuery=Server.CreateObject("ADODB.Command")
with comQuery
    .ActiveConnection=name of connection object you
         made earlier (the one connecting to the db)
    .ComandText="in quotes, the name of the query"
    .CommandType=if you have adovbs.inc included then you can 
         use adCmdStoredProc else it's 4 but including adovbs is better
    .Parameters.Append .CreateParameter("name of paramter in 
        access, usually has a @ before it", type of value being passed 
        (must be one the db supports), adParamInput (again, a adovbs 
        value),(if string then put the max length here),value to pass)
***make a paramters.append for each parameter being passed.
    .Execute (if you want to know how many records were effect put a 
name here for a variable), (only used if passing paramters as an array,
but append is better usually),adExecuteNoRecords (a constant that SHOULD 
be in adovbs but is lacking from most. If it's not in yours add it, it's 
numerical value is 128. Only put this if you don't want a recordset sent 
back from the  database (for inserts and updates)
end with

Just so as you know, to return a recordset you would take the execute 
out of the with and do something like this:
    set recordset=comQuery.Execute

You can call a stored "query," which we will call qryUpdateTable, in 
Access like this:

<%

Const adCmdStoredProc = 4

Dim objConn, strConnect, objCmd

Set objConn = Server.CreateObject("ADODB.Connection")

strConnect = 'insert your connection string here

objConn.Open strConnec

Set objCmd = Server.CreateObject("ADODB.Command")

objCmd.CommandType = adCmdStoredProc

objCmd.CommandText = qryUpdateTable

objCmd.ActiveConnection = objConn

objCmd.Execute

%>

You can also remove the ActiveConnection setting for the Command Object 
as shown above and use the Command Object as your "Source" parameter for 
the Open method of the Recordset Object:

objRS.Open objCmd, objConn, , adLockPessimistic