-
Here is the problem:
I am trying to execute the action query UPDATE but I get an error "Too few parameters, expected '1' "
Here is the code:
Dim qdfAppend As QueryDef
Dim AppendDb As Database
Dim AppendRs As Recordset
Dim AppendCon As String
Dim strSQLAppend As String
AppendCon = "ODBC;DRIVER={SQL Server};UID=;PWD=;DATABASE=MAV-DB;SERVER=Exch_Serv"
Set AppendDb = DAO.OpenDatabase("", 0, 0, AppendCon)
Set qdfAppend = AppendDb.CreateQueryDef("", strSQLAppend)
strSQLAppend = "UPDATE " & DataTable & " SET " & Exist & " = " & Chr(39) & "1" & Chr(39) & " WHERE " & PartNo_Field & " = " & PartNumber
' qdfAppend.SQL = strSQLAppend
AppendDb.Execute strSQLAppend
This line is where it errors
Thanks for your help
Simon
-
I don't think you need to put the flags on all of that, only the variables:
strSQLAppend = "UPDATE DataTable " _
& "SET Exist = '" & Chr(39) & "' " _
& " '1' '" & Chr(39) & "' " _
& "WHERE PartNo_Field = '" & PartNumber & "' "
Try That, I rekon it should work, remember the tags go round variables:
= " '" & variablename & "' "
Hope this helps
Cheers 'n' beers
Skeen
-
Simon,
See my e-mail. The problem is definitely in the SQL String. There is some problem with synbolic substitution i.e. DataTable etc.
Cheers,
Paul.
-
Chr(39) = '
Therefore where ever a chr(39) exists the is the flag surrounding the variable.
Thanks for your help but I have used the same SQL string method for all my select statements and they work fine.
I think that I have found the problem in that there are no "'" surrounding the last variable in the UPDATE statement.
Let me try that
Cheers Anyway
Simon
-
So PartNo_Field is a text field? Leaving off "" IS a problem with your SQL String.
BTW Do you have Access? If so, do a bench test with the code on simple test tables, it is quick and easy and often indicates initial problems which you can then correct quickly and easily.
Cheers,
Paul