Update query to SQL Table

Question

I am attempting to run an Update query against an SQL Server table but there seems to by a syntax error in the sSQL line. The error returned is Incorret syntax near ')' . I have no clue as to what it wants.

Dim adoCN As ADODB.Connection

Dim sConnString As String

Dim sSQL As String

'Populates the SQL Server tabledbo.Bank\_Transaction 

    'Connection string to SQL Server database 

        sConnString = "DRIVER=SQL Server Native Client 11.0;SERVER=57.187.399.297;UID=sa;PWD=12345678sW#;DATABASE=Auxiliary\_3242\_Secretary;" 

        Set adoCN = CreateObject("ADODB.Connection") 

        adoCN.Open sConnString

    'Updates data in selected table 

        sSQL = "UPDATE dbo.Bank\_Transaction Set Expense =  (' " & txt\_Expense & " ') WHERE (dbo.Bank\_Transaction.Bank\_Transaction\_ID=(' " &       txt\_Bank\_Transaction\_ID & " ')" 

        adoCN.Execute sSQL

    adoCN.Close 

    Set adoCN = Nothing

Answer 1

Good on ya.... didn't think it TOO likely you'd make such a mistake!

To the original question... what are the values of txtExpense and sSQL? You might have some unwanted blanks: should Expense = (' " & txt_Expense & " ') instead be

Expense = ('" & txt_Expense & "')

to avoid trying to insert leading and trailing blanks into the string?

Answer 2

Should have mentioned that I altered both the server and password

Answer 3

Woah! If that Connect string is real, you've just published your ID and password for all the world to see. CHANGE PASSWORDS IMMEDIATELY, and check to be sure nobody has already used this information!

Answer 4

Doesn't even look like you need the ( and ) in either the SET or WHERE clauses. Try taking them out.

Answer 5

Dear Ordnance1,

Good day.

Thanks for posting in Microsoft Community.

Regarding your question on updating query to SQL Table, since we have a specific channel Microsoft Q&A Community coping with query questions, I suggest you post a new thread (assign the tab office-access-dev) there for expert help. I am sure that our experts from that team can address your query effectively and accurately.

Also, we welcome community members to share ideas about the situation if one has related experiences.

Thank you for your cooperation and understanding.

Sincerely,

George