The Problem (Q-score 13, ranked #5th of 67 in the Access VBA archive)
The scenario as originally posted in 2009
I have been debugging this query for the last 40 minutes, and the problem apparently is the order of the parameters after all.
SELECT * FROM tblSomeThing WHERE id = @id AND debut = @dtDebut AND fin = @dtFin
Then I add the parameters this way, notice that the two last parameters are switched, I get no results.
cmd.Parameters.Add("@id", OleDbType.Integer).Value = idSociete;
cmd.Parameters.Add("@dtFin", OleDbType.Date).Value = dateTraitementFin;
cmd.Parameters.Add("@dtDebut", OleDbType.Date).Value = dateTraitementDebut;
When I declare the parameters the way they appear in the queury everything works perfectly.
I thought named parameters were at first place to address this problem! what am I missing here?
Thank you
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds elite answer (top 10 %%) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — elite answer (top 10 %%) (+28)
Advisory answer — community consensus with reference links
Note: the verified answer below is a reference / advisory response rather than a copy-ready snippet.
According to http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx OleDbCommand does not support named parameter
The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used. For example:
SELECT * FROM Customers WHERE CustomerID = ?
Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection must directly correspond to the position of the question mark placeholder for the parameter in the command text.
So order of parameter is important.
When to Use It — vintage (14+ years old, pre-2013)
A top-10 Access VBA pattern — why it still holds up
Ranks #5th of 67 in the Access VBA archive. The only pattern ranked immediately above it is “MS Access library for python” — compare both if you’re choosing between approaches.
What changed between 2009 and 2026
The answer is 17 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.