2

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

GMB
  • 216,147
  • 25
  • 84
  • 135
Eddie
  • 23
  • 5
  • 1
    @GordonLinoff, Access does support [`AUTOINCREMENT` type](https://stackoverflow.com/a/6000332/1422451). – Parfait May 29 '20 at 01:03
  • Why are you declaring `OwnerPhone` as a numeric datatype? A text datatype would be more appropriate. – Applecore May 29 '20 at 08:05

2 Answers2

1

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • Thank you for the explanations since this is literally my first time. It took longer but separating them out worked. Again thank you. – Eddie May 29 '20 at 16:36
  • You're welcome. It's a shame the error message doesn't give you a better explanation of the problem. – HansUp May 29 '20 at 16:41
0

You would need to start each statement with insert into, and separate them with ;.

Insert Into Pet_Owner (OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)
Values ('Downs' , 'Marsha' , '555-537-8765' , 'Marsha.downs@somewhere.com');

Insert 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');
GMB
  • 216,147
  • 25
  • 84
  • 135