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"
        connect.Open()

        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.")
        Else
            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

Recommended Answers

All 2 Replies

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:

UPDATE MyTable 
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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.