We help IT Professionals succeed at work.

INSERT INTO help (MS Access and VB6)

Medium Priority
619 Views
Last Modified: 2012-05-06
I cannot seem to get this statement to work. It throws a Syntax Error
MyConnAcct.Execute ("INSERT INTO UserAccounts ([attempts]) WHERE ID =" & Form1.curracct & " VALUES (" & Form1.LoginCounts & ")")

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Try this:

MyConnAcct.Execute "INSERT INTO UserAccounts ([attempts]) WHERE ID =" & Form1.curracct & " VALUES (" & Form1.LoginCounts & ")"

Commented:
use this.. an Insert statement do not have a where cluase..

MyConnAcct.Execute ("INSERT INTO UserAccounts ([attempts]) " VALUES (" & Form1.LoginCounts & ")")

Commented:
it should be

MyConnAcct.Execute ("INSERT INTO UserAccounts ([attempts])  VALUES (" & Form1.LoginCounts & ")")
I have to do a WHERE, so I used this instead:

MyConnAcct.Execute ("UPDATE UserAccounts SET attempts =" & Form1.LoginCounts & " WHERE accounts ='" & Form1.username.Text & "'")

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
RobinSoftware Engineer

Commented:
You can not use the WHERE statement in the INSERT.

If you want to update the login attempts for a certain id, use the UPDATE statement, assuming
the ID exists in the table:


MyConnAcct.Execute("UPDATE UserAccounts SET [attempts]=" & Form1.LoginCounts & " WHERE ID=" & Form1.curracct)

Open in new window

Commented:
an update and an insert statement structures differs. You asked an insert but resulted with an update statement.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"You can not use the WHERE statement in the INSERT."

Sure you can.  Example:

INSERT INTO Table1 ( FIELD1 )
SELECT Table2.FIELD1
FROM Table2
WHERE (((Table2.ID)=3));


mx

Commented:
DatabaseMX the where clause is not a part of the insert statement its a part of the select statement which is supplying values to the insert statement.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
What I posted is an Access Append query example ... but I see what you meant by that post.

mx
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.