Running Access - ADOX? For Stored Procedure

Running Access - ADOX? For Stored Procedure

Post by Giacom » Fri, 14 Jun 2002 02:38:37

I'm finding MSAccess confusing.

In SQL OLE, the ExecuteWithResults will bring back my ReturnVal parameters
but in Access, when I run a prepared statement... there is no
ExecuteWithResults in Jet 4.0 OLE.   How do I run a similar query?

I'm wondering  - if I create the procedure in VB Code and then append it to
an adox catalog - I haven't used catalogs before...  do those persist for
the duration of the executable if written properly or do they directly
append and permanently append the query to the Access database?  I tried one
but could not find a record of it in the Access database (Access 2000)
following the catalog append.  Does Access 2000 support the ADOX commands
from the 2.7 library?

There is an example of what I'm trying to do below.  Primarily, I need to
get the Primary Key ID back after a batch update and place the id in another
table to cross reference the transaction.

Help appreciated,
Giacomo aka Jamie

            Set mCmd= New ADODB.Command
            mCommandBatch.ActiveConnection = globalData.adoJetOdbc 'from a
            mCmd.Prepared = True

'Next two lines don't work the way I expect... don't know what will happen
if I add "sp_SearchInsert" to ADOX catalog????

'            mCmd.CommandType = adCmdStoredProc
'            mCmd.CommandText = "sp_SearchInsert"

            mCmd.CommandText = "INSERT INTO [Search] (,nReference," & _
                    "SearchTypeID)" & _
                    " VALUES ([?],[??]);"

'Next parameter won't work so I commented it out as well
'            mCmd.Parameters.Append mCmd.CreateParameter("SearchID",
adInteger, adParamReturnValue)

            mCmd.Parameters.Append mCmd.CreateParameter("nReference",
            mCmd.Parameters.Append mCmd.CreateParameter("vSearch",
adLongVarChar, , 327)

            mCmd("nReference").Value = Me.nReference
            mCmd("vSearch").Value = Right(Space(327) &
(Me.vSearch.ReadArray), 327)

'next line doesn't work
'            Set rs = mCmd.Execute()

'and of course, if I have open a recordset to query the searchid, I defeat
the speed gain of a stored proc
            mCmd.Execute , , adCmdText + adExecuteNoRecords

I'm reopening the darned table here to get the ID... I would like to have it
back from the batch insert above...
            Set rs = New ADODB.Recordset
            rs.Open "[vLastSearchID]",
gDataServices.adoJetOdbc.ConnectionString, adOpenForwardOnly,
adLockReadOnly, adCmdTable
            If Not (rs.EOF And rs.BOF) Then
                Me.SearchID = IIf(IsNull(rs("SearchID").Value), 1,
            End If