Simple queries/sp executions are submitted complexly.

[ESquared]

Access 2003 ADP.

I've been researching and trying to verify what are the most efficient methods of query usage in Access ADPs. For the most part, I've been using SPs for recordsources and rowsources. However, sometimes I want to do some action queries that don't need a bound recordset. For example, I want to create a new blank row, get the identity value back, then load a form with its record source set to have just that one record. The problem is that Access can't just do what I tell it, no, it has to do all sorts of shenanigans. First of all, getting column names

What should be a simple "EXEC CreateNewWidget, EXEC something else, EXEC" looks something like the following SQL Server profiler output (evidently not completely order because of when rows have the same datetime value):

declare @P1 int set @P1=402 exec sp_prepare @P1 output, N'@P1 sql_variant OUTPUT,@P2 int', N' EXEC @P1=CreateNewWidget @P2 ', 1 select @P1
SET FMTONLY ON
-- CreateNewWidget RETURN SCOPE_IDENTITY()
EXEC @P1=CreateNewWidget @P2
exec sp_execute 402,0,0
SET FMTONLY OFF
-- CreateNewWidget INSERT tblWidgetsHdr ( WIDGETNumber, ParentWidgetID ) SELECT WIDGETNumber = '', ParentWidgetID = @ParentWidgetID
-- CreateNewWidget RETURN SCOPE_IDENTITY()
EXEC @P1=CreateNewWidget @P2
declare @P1 sql_variant set @P1=12039 exec sp_execute 402, @P1 output, 22267 select @P1
exec sp_unprepare 402

Here's the VB code that generated this:

Public Function GetNewIDbySP(TheStoredProcedure As String, ParamArray TheInput() As Variant) As Long
   On Error Resume Next
   Dim comN As New ADODB.Command
   Dim lngCntr As Long
   Dim lngLBound As Long
   Dim lngUBound As Long
   
   With comN
      Set .ActiveConnection = Application.CurrentProject.Connection
      .CommandText = TheStoredProcedure
      .Parameters.Append .CreateParameter("NewID", adVariant, adParamReturnValue)
      lngLBound = LBound(TheInput())
      lngUBound = UBound(TheInput())
      For lngCntr = lngLBound To lngUBound
         If VarType(TheInput(lngCntr)) = vbString Then
            .Parameters.Append .CreateParameter( _
                  , _
                  ADOVarType(TheInput(lngCntr)), _
                  adParamInput, _
                  Len(TheInput(lngCntr)), _
                  TheInput(lngCntr) _
               )
         Else
            .Parameters.Append .CreateParameter( _
                  , _
                  ADOVarType(TheInput(lngCntr)), _
                  adParamInput, _
                  , _
                  TheInput(lngCntr) _
               )
         End If
      Next
      .Prepared = True
      .Execute , , adExecuteNoRecords + adCmdStoredProc
      GetNewIDbySP = .Parameters("NewID")
   End With

   Set comN = Nothing
End Function

I tried using ADO commands instead of just regular ad-hoc recordset stuff like "EXEC CreateNewWidget " & ParentWidgetID on the theory that it's faster. And also on the theory that letting ADO escape my strings is safer (SQL injection-wise). But what's with exec sp_execute and sp_prepare and sp_unprepare and all the extra overhead? Can I avoid it somehow?

With other queries and actions there's the repeated use of MShelpcolumns as well. Why can't Access just submit the query and figure out the columns from the result set? Is there anything I can do to prevent this kind of overhead?

Erik