troubleshooting Question

Why does a SQL Statement execute successfully from the QUERY interface when I execute it manually but when it execute the same SQL statement from a module, I get a SQL UPDATE error?

Avatar of zimmer9
zimmer9Flag for United States of America asked on
Microsoft Access
2 Comments1 Solution497 ViewsLast Modified:
Why do I successfully execute a SQL Statement from the QUERY interface when I execute a SQL Command manually as follows:

I update a user's password temporarily with their userID.

Update tblUserSecurity1 set password = 'zimmer9' Where userID = 'zimmer9'

However, when my Access application executes the following module after the command click event takes place, even though the same SQL Statement is executed, I get the following SQL Error:

Run-time error '-2147217900 (80040e14)': Syntax error in UPDATE statement


Private Sub Command62_Click()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim strUserID As String

Set con = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = con

ComboSelect = Me.Combo55.Column(1)
strUserID = " & ComboSelect & """
 cmd.CommandText = "Update tblUserSecurity1 set password = '" & ComboSelect & "' " _
                       & "Where userID = '" & ComboSelect & "'"
Debug.Print cmd.CommandText
cmd.Execute
End Sub

-----------------------

cmd.CommandText displays the following in the Immediate Window as the result of the Debug.Print statement:
Update tblUserSecurity1 set password = 'zimmer9' Where userID = 'zimmer9'
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros