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.
This has worked fine for years, and has never been touched, until recently, everytime I run the function I get an error:
Run-time error '-2147217904(8004e10)': Too few parameters. Expected 2.
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.
The code is below, but again, it has worked as-is for year with no changes and works after a C&R.
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 = "qryAccCheckPackagesAdded"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set rstPackageCount = cmd.Execute(, params)
If (rstPackageCount("packageCount") = 0) Then
AccCompleteOrder = False
Exit Function
End If
'Move dummy records to Temp table
If TableExists("tmpTblAccDeletion") Then
DoCmd.DeleteObject acTable, "tmpTblAccDeletion"
End If
cmd.CommandText = "qryAccMoveToTemp"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute , params
'delete old lines from table
cmd.CommandText = "qryAccDeleteFromWIL"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute , params
'Append real data from Genophyle orders
cmd.CommandText = "qryAccAppendfrmGenophyle"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Execute , params
'Get tempData recordset
cmd.CommandText = "qryAccSelectTmpData"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
Set rstTmpData = cmd.Execute
'Get real Data (from WIL) dataset
cmd.CommandText = "qryAccSelectRealData"
cmd.CommandType = adCmdStoredProc
cmd.Parameters.Refresh
cmd.Parameters.Append cmd.CreateParameter("LabID", adChar, , 10, LabID)
cmd.Parameters.Append cmd.CreateParameter("OrderID", adBigInt, , 10, OrderID)
rstRealData.Open cmd, , adOpenDynamic, adLockOptimistic
Do While Not rstRealData.EOF
rstRealData("Country") = rstTmpData("Country")
Do While Not rstTmpData.EOF
If (rstRealData.Fields("Platform") = rstTmpData.Fields("Platform")) 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 = "qryAccUpdateAccessioningCheckOrderComplete"
cmd.Parameters.Refresh
cmd.Parameters.Append cmd.CreateParameter("LabID", adChar, , 10, LabID)
cmd.Parameters.Append cmd.CreateParameter("OrderID", adBigInt, , 10, OrderID)
cmd.Parameters.Append cmd.CreateParameter("ComDate", adChar, , 50, Format(Now(), "dd/mm/yyyy hh:MM:ss"))
cmd.Parameters.Append cmd.CreateParameter("ComCheck", adBoolean, , , True)
cmd.Execute
AccCompleteOrder = True
End Function
The debugger indicates the error is when it reaches the line
rstRealData.Open cmd, , adOpenDynamic, adLockOptimistic
This has me stumped.
