Search code examples
sqlms-accesssql-insertms-access-2016

How to fix end of statement when trying to insert multiple values in SQL?

I just started a database course that is going over SQL using Access 2016. I am getting a End of statement error and I am unsure how to fix it.

My table code is `

Create Table Pet_Owner (
OwnerID autoincrement Null,
OwnerLastName Char(30) Not Null,
OwnerFirstName Char(30) Not Null,
OwnerPhone Int Null,
OwnerEmail VarChar(100) Not Null Unique,
Constraint Pet_Owner_PK Primary Key(OwnerID)
);

and the insert query I have so far that I am trying to add is 

Insert Into Pet_Owner (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) Values (
'Downs' , 'Marsha' , '555-537-8765' , 'Marsha.downs@somewhere.com') 
Into Pet_Owner (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) Values (
'James' , 'Richard' , '555-537-7654' , 'Richard.James@somewhere.com') 
Insert Into Pet_Owner (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail) Values (
'Frier' , 'Liz' , '555-537-6543' , 'Liz.Frier@somewhere.com') 
Insert Into Pet_Owner (OwnerLastName, OwnerFirstName, OwnerEmail) Values (
'Trent', 'Miles', 'Miles.Trent@somewhere.com') ;

When I use append, it is saying I am missing a semicolon at the end of the first set of values, but if i add that then it says i have characters at the end of my statement. If anyone can help me out or point me into the right direction to solve the error I would greatly appreciate it. Thank you

Solution

  • Your insert query includes 4 INSERT ... VALUES statements. So when you attempt to run that query, you're asking Access to execute a batch of statements. However Access will not execute batched statements. You must execute them separately.

    And when you execute them one at a time, you will discover another 2 problems:

    1. OwnerPhone Int Null will not accept a text value such as '555-537-8765'; Make that a text instead of numeric type field.

    2. The second insert statement does not include the INTO keyword.

    I used this statement to create the table:

    Create Table Pet_Owner (
OwnerID autoincrement Primary Key,
OwnerLastName Char(30) Not Null,
OwnerFirstName Char(30) Not Null,
OwnerPhone VarChar(12) Null,
OwnerEmail VarChar(100) Not Null Unique
);

    (OwnerPhone was the critical change. I changed the primary key assignment just to show you a different method for that.)

    Then processed each insert statement separately by loading it into a variable, strInsert, and executing it like this:

    CurrentDb.Execute strInsert, dbFailOnError

    That approach successfully added 4 rows to the Pet_Owner table.