SQL Update Query - Cannot get it to work!

[Stockton.S]

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

[Skeen]

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

[paulw]

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.

[Stockton.S]

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

[paulw]

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