?
Solved

INSERT INTO help (MS Access and VB6)

Posted on 2009-02-09
9
Medium Priority
?
600 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

0
Comment
Question by:Taylor814
9 Comments
 
LVL 75
ID: 23595112
Try this:

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

Expert Comment

by:tigin44
ID: 23595114
use this.. an Insert statement do not have a where cluase..

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

Expert Comment

by:tigin44
ID: 23595131
it should be

MyConnAcct.Execute ("INSERT INTO UserAccounts ([attempts])  VALUES (" & Form1.LoginCounts & ")")
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Accepted Solution

by:
Taylor814 earned 0 total points
ID: 23595133
I have to do a WHERE, so I used this instead:

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

Expert Comment

by:Robin
ID: 23595143
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

0
 
LVL 26

Expert Comment

by:tigin44
ID: 23595158
an update and an insert statement structures differs. You asked an insert but resulted with an update statement.
0
 
LVL 75
ID: 23595198
"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
0
 
LVL 26

Expert Comment

by:tigin44
ID: 23595250
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.
0
 
LVL 75
ID: 23595287
What I posted is an Access Append query example ... but I see what you meant by that post.

mx
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question