Guys i always get an error in this...it says "Syntax error in Update Statement"
please help...

Sub updateaccount(ByVal Username As String, ByVal Password As String, ByVal FName As String, ByVal MName As String, ByVal LName As String, ByVal Restrict As String, ByVal FullName As String)
        connect = New ADODB.Connection
        rs = New ADODB.Recordset
        Dim rss As New ADODB.Recordset
        connect.ConnectionString = "PROVIDER=Microsoft.JET.OLEDB.4.0;Data Source=C:\Users\Spongebob\Desktop\DataBase1.mdb"

        Dim sql1, sql As String

        sql = "Select AUsername from AdminAccount"
        rs.Open(sql, connect, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockPessimistic)

        If Username = "" Or Password = "" Or FName = "" Or MName = "" Or LName = "" Then
            MsgBox("Input asked values.")
            sql1 = "UPDATE AdminAccount where (AUsername = '" & Trim(Username) & "') set (APassword = '" & Trim(Password) & "', AFName ='" & Trim(FName) & "', AMName ='" & Trim(MName) & "', ALName ='" & Trim(LName) & "', Restrict? ='" & Trim(Restrict) & "', AFullName ='" & Trim(FullName) & "')"
            rss.Open(sql1, connect, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockPessimistic)
            MsgBox("Change(s) of the Account " & Username & " was saved!")
        End If

    End Sub

Edited by amf101: n/a

6 Years
Discussion Span
Last Post by BitBlt

Before executing take the query and run in sql server so u will come to know where the syntax is going wrong..


I'm fairly certain that you have to put the "SET" clause before the "WHERE" clause. That's fairly standard SQL syntax, even MSAccess SQL.
Here's some sample syntax that you can adapt to your specific situation:

SET MyTable.MyNumeric = 100
WHERE (((MyTable.MyCharacter)="One")) 
OR (((MyTable.MyCharacter)="Two"));

One other little thing...on line 16, there appears to be a superfluous question-mark after the column name "Restrict". Probably has nothing to do with your syntax problem, but you might want to check into it anyway.

Hope this helps! Good luck!

Edited by BitBlt: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.