title: Access "too few parameters" or crash but only before compact and repair
body: <p>Bit of an odd one here, essentially I have a VBA function in Microsoft Access that takes two arguments provided and cuts the data from the existing table to a temp table, and then compares this to the latest data from an external an SQL database and then reappends the updated information.</p>
  <p>This has worked fine for years, and has never been touched, until recently, everytime I run the function I get an error:</p>
  <blockquote>
  <p>Run-time error '-2147217904(8004e10)':
  Too few parameters. Expected 2.</p>
  </blockquote>
  <p>However, if I manually compact and repair, or recompile the database, this error goes away and the function completes as normal. But only for that session, currently the staff that use this function have to compact and repair everytime they open the Acces front end to make the function complete. Compact and repair on close does not work.</p>
  <p>The code is below, but again, it has worked as-is for year with no changes and works after a C&amp;R.</p>
  <pre><code>Function AccCompleteOrder(LabID As String, OrderID As String) As Boolean
    
    Dim cmd As New ADODB.Command
    Dim conn As New ADODB.Connection
    Dim rstPackageCount As ADODB.Recordset
    Dim rstTmpData As ADODB.Recordset
    Dim rstRealData As New ADODB.Recordset
    Dim i As Integer
    Dim params() As Variant
  
    'set up cmd and query parameters
    params = Array(LabID, OrderID)
    Set conn = CurrentProject.Connection
    cmd.ActiveConnection = conn
    
    'check that packages have been added to the order in genophyle
    cmd.CommandText = &quot;qryAccCheckPackagesAdded&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    Set rstPackageCount = cmd.Execute(, params)
    
    If (rstPackageCount(&quot;packageCount&quot;) = 0) Then
      AccCompleteOrder = False
      Exit Function
    End If
    
    'Move dummy records to Temp table
    If TableExists(&quot;tmpTblAccDeletion&quot;) Then
        DoCmd.DeleteObject acTable, &quot;tmpTblAccDeletion&quot;
    End If
    cmd.CommandText = &quot;qryAccMoveToTemp&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    cmd.Execute , params
    
    'delete old lines from table
    cmd.CommandText = &quot;qryAccDeleteFromWIL&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    cmd.Execute , params
    
    'Append real data from Genophyle orders
    cmd.CommandText = &quot;qryAccAppendfrmGenophyle&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    cmd.Execute , params
    
    'Get tempData recordset
    cmd.CommandText = &quot;qryAccSelectTmpData&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    Set rstTmpData = cmd.Execute
    
    'Get real Data (from WIL) dataset
    cmd.CommandText = &quot;qryAccSelectRealData&quot;
    cmd.CommandType = adCmdStoredProc
    cmd.Parameters.Refresh
    
    cmd.Parameters.Append cmd.CreateParameter(&quot;LabID&quot;, adChar, , 10, LabID)
    cmd.Parameters.Append cmd.CreateParameter(&quot;OrderID&quot;, adBigInt, , 10, OrderID)
    
    rstRealData.Open cmd, , adOpenDynamic, adLockOptimistic
    Do While Not rstRealData.EOF
      rstRealData(&quot;Country&quot;) = rstTmpData(&quot;Country&quot;)
      Do While Not rstTmpData.EOF
        If (rstRealData.Fields(&quot;Platform&quot;) = rstTmpData.Fields(&quot;Platform&quot;)) Then
          For i = 0 To rstRealData.Fields.Count - 1
            If (IsNull(rstRealData.Fields(i)) Or rstRealData.Fields(i) = 0) Then
              rstRealData.Fields(i) = rstTmpData.Fields(i)
            End If
          Next
        End If
        rstTmpData.MoveNext
      Loop
      rstTmpData.MoveFirst
      rstRealData.Update
      rstRealData.MoveNext
    Loop
  
    rstRealData.Close
    
    'update the accessioning check table
    cmd.CommandText = &quot;qryAccUpdateAccessioningCheckOrderComplete&quot;
    cmd.Parameters.Refresh
    
    cmd.Parameters.Append cmd.CreateParameter(&quot;LabID&quot;, adChar, , 10, LabID)
    cmd.Parameters.Append cmd.CreateParameter(&quot;OrderID&quot;, adBigInt, , 10, OrderID)
    cmd.Parameters.Append cmd.CreateParameter(&quot;ComDate&quot;, adChar, , 50, Format(Now(), &quot;dd/mm/yyyy hh:MM:ss&quot;))
    cmd.Parameters.Append cmd.CreateParameter(&quot;ComCheck&quot;, adBoolean, , , True)
    
    cmd.Execute
    
    AccCompleteOrder = True
    
  End Function
  </code></pre>
  <p>The debugger indicates the error is when it reaches the line</p>
  <pre><code>rstRealData.Open cmd, , adOpenDynamic, adLockOptimistic
  </code></pre>
  <p>This has me stumped.</p>