Characters found after end of SQL statement

[Fred Horton]

When I try to use "Insert Into..." to populate tables in
Access with multiple records, I get the following error
message: Characters found after end of SQL statement

Below is an example of the SQL statement I am using. When
I use this with SQL Server 2000 it works fine, but in
Access, I always get that error message. If someone has
solved this problem, I would very much appreciate some
help solving this.

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2001/5/1', '1000000001');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2001/1/12', '1000000003');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2001/1/30', '1000000004');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2001/2/3', '1000000005');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2001/2/8', '1000000001');

 

[Rick Brandt]

When I try to use "Insert Into..." to populate tables in
Access with multiple records, I get the following error
message: Characters found after end of SQL statement

Access queries cannot execute more than one statement (unlike SQL Server).

You could do the equivalent in VBA code...

CurrentDB.Execute "INSERT INTO...", dbFailOnError

CurrentDB.Execute "INSERT INTO...", dbFailOnError

CurrentDB.Execute "INSERT INTO...", dbFailOnError

 

[John Vinson]

Below is an example of the SQL statement I am using. When
I use this with SQL Server 2000 it works fine, but in
Access, I always get that error message.

SQL/Server supports multiple queries in a SQL statement... Access does
not. You can have only one INSERT statement in the SQL of a query.

 

[Rosco]

When I try to use "Insert Into..." to populate tables in
Access with multiple records, I get the following error
message: Characters found after end of SQL statement

Below is an example of the SQL statement I am using. When
I use this with SQL Server 2000 it works fine, but in
Access, I always get that error message. If someone has
solved this problem, I would very much appreciate some
help solving this.

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2001/5/1', '1000000001');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2001/1/12', '1000000003');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2001/1/30', '1000000004');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2001/2/3', '1000000005');

INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2001/2/8', '1000000001');

You might try the following:

INSERT INTO Orders( order_num, order_date, cust_id)
SELECT 20009, #2001/2/8#, '1000000001';

If cust_id is NOT text, then delete the single quotes around the number.
Also I'm not sure access supports the date format you are showing.
My tests come back formatted 2/1/2001.

Hope this helps
Rosco

 

[Tony Toews]

You might try the following:

INSERT INTO Orders( order_num, order_date, cust_id)
SELECT 20009, #2001/2/8#, '1000000001';

If cust_id is NOT text, then delete the single quotes around the number.
Also I'm not sure access supports the date format you are showing.
My tests come back formatted 2/1/2001.

I'd leave the VALUES and the brackets as is. I've never tried the
SELECT statement in what you have but it could very well work.
You're correct in the # around the date

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

INSERT INTO Orders( order_num, order_date, cust_id)
SELECT 20009, #2001/2/8#, '1000000001';

Note to lurkers. If you are ever inserting a date I would strongly
urge you to use the following logic.

SQL statements require that the dates be either completely unambiguous
or in mm/dd/yy, or mm/dd/yyyy format. Otherwise Access/Jet will do
it's best to interpret the date with unknown results depending on the
specific date it is working with. You can't assume that the system
you are working on is using those date formats. Thus you should use
the logic at the following web page.
Return Dates in US #mm/dd/yyyy# format
http://www.mvps.org/access/datetime/date0005.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm