CODEWITHSUNDEEP
sqlsql-serverms-accessvba
beeba
beeba

Reputation: 432

Formatting SQL queries in MS Access

I am trying to use an SQL query in Access. I have had this issue before but still do not fully understand what I am doing wrong. I am trying to run a strSQL statement in MS Access, however it is giving me a "Too few paramters - expected 1" error when I try to run the code. 

Sub SampleReadCurve()

Dim rs As Recordset
Dim iRow As Long, iField As Long
Dim strSQL As String
Dim CurveID As Long
Dim MarkRunID As Long
Dim ZeroCurveID As String

CurveID = 15

strSQL = "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & "ORDER BY MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)

[...]

I want to select from a table in Access, VolatilityOutput, for all instances in which CurveID = 15. What is wrong with the way I have used strSQL?

Upvotes: 0

Views: 1086

Answers (1)

Hambone
Hambone

Reputation: 16377

For what it's worth, I can repeat your error message if I declare a parameter and don't assign it:

strSQL = "parameters [CID] number; " & _
   "SELECT * FROM VolatilityOutput WHERE CurveID=" & CurveID & " ORDER BY MaturityDate"

Set rs = CurrentDb.OpenRecordset(strSQL, Type:=dbOpenDynaset, Options:=dbSeeChanges)

Gives me the same thing you are reporting:

Too Few Parameters. Expected 1

I think you might be able to kill two birds with one stone here -- fix your error and use parameters, the way several on this thread are suggesting. This is an example of how you would invoke the parameter:

Sub SampleReadCurve()

  Dim rs As Recordset
  Dim iRow As Long, iField As Long
  Dim strSQL As String
  Dim CurveID As Long
  Dim MarkRunID As Long
  Dim ZeroCurveID As String
  Dim qry As QueryDef

  CurveID = 15

  strSQL = "parameters [CID] number; " & _
     "SELECT * FROM VolatilityOutput WHERE CurveID = [CID] ORDER BY MaturityDate"

  Set qry = CurrentDb.CreateQueryDef("GetCurve", strSQL)
  qry.Parameters("CID") = CurveID
  Set rs = qry.OpenRecordset

  CurrentDb.QueryDefs.Delete ("GetCurve")

End Sub

Bear in mind this creates the query definition and clobbers it each time the sub runs, which is not best practice. I would change this to declare the query ahead of time (through normal Access, not VBA) and invoke it within the sub, leaving it there for the next time you need it:

Set qry = CurrentDb.QueryDefs("GetCurve")

As a final note, among the numerous advantages of using the bind variable / parameter are that datatypes are managed. This means if CurveID was a date or a string, this methodology would still work. You would not need to change the SQL to include quotes or do any special handling for date formats -- the bind variables take care of that for you. This means if your string was:

I'd like to say "hello"

(both single and double quotes), there would be no special handling required. Pretty awesome, right?

Upvotes: 2

Related Questions