Solved

Problem with 2nd insert line

Posted on 2013-11-15
16
370 Views
Last Modified: 2013-11-21
Hi,
In Access 2013, I put these
insert into ite_tab (name,ite_name) values('Ite 1','');
insert into ite_tab (name,ite_name) values('Ite 2','');

in the query. But I get the error shown in the figure
t605.png
0
Comment
Question by:HuaMinChen
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 68

Expert Comment

by:Qlemo
ID: 39650571
You'll have to execute each INSERT individually (two strings, two commands) instead of both as one string.
0
 

Expert Comment

by:Pratima_Pharande
ID: 39650572
With Access SQL you can't combine two INSERT statements. You could run each of them separately.

 But if you need to do it with a single statement, you will need to use a more complex query  like below

INSERT INTO ite_tab
SELECT 'Ite 1',''
FROM Dual
UNION ALL
SELECT 'Ite 2',''
FROM Dual;
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39650660
Sorry  Pratima, I get

"Syntax error in FROM clause" due to the following

insert into ite_tab (name,ite_name) select 'Ite 1','' FROm dual
union all select 'Ite 2','' FROm dual
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39650893
Create 2 queries - one for each insert statement as this would be clearer.  You also are using the field name which is a reserved word in MS Access so surround it with [].  Lastly, no need to insert an empty string into ite_name.  Leave  it out,

Query1 SQL
insert into ite_tab ([name]) values('Ite 1');

Query2 SQL
insert into ite_tab ([name]) values('Ite 2');

If you must use just one query then:

INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;

Regards,

Bill
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39651211
Sorry, I still get the same error - "Syntax error in FROM clause", due to this

insert into ite_tab ([name],[ite_name]) select 'Ite 1' as [name],'' as [ite_name] from ite_tab
union all select 'Ite 2' as [name],'' as [ite_name] from ite_tab
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39651280
Your syntax is still incorrect.  Try to duplicate it exactly as shown below and above.

INSERT INTO ite_tab
SELECT 'Ite 1' as [Name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name] from ite_tab;

Regards,

Bill
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39651423
But I want to insert into 2 columns in the table.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39651513
Hi,

Did you try the code?

Inserting empty data into a column happens automatically when you insert the record.  It is like not inserting anything at all into the column and is not necessary.  In any case if you want to insert blank data into a second column change the SQL statement to:

INSERT INTO ite_tab
SELECT 'Ite 1' as [Name], NULL as [ite_name] from ite_tab
UNION ALL
SELECT 'Ite 2' as [Name], NULL as [ite_name] from ite_tab;

This will give the same result as the prior code.

Regards,

Bill
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 10

Author Comment

by:HuaMinChen
ID: 39653257
Many thanks all.
Sorry, I still get the same error due to this
insert into ite_tab (fld_name,ite_name) select 'Ite 1' as [fld_name],NULL as [ite_name] from ite_tab
union all select 'Ite 2' as [fld_name],NULL as [ite_name] from ite_tab

Open in new window

after having adjusted the table column name from name to fld_name.
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39653277
Hi,

I was wrong.  

You cannot combine INSERT and UNION queries in the same SQL statement.  Qlemo is correct above.

This is by definition.  

You will need to run 2 queries per my original answer above.  You could also create a VBA or macro to do the work.

Regards,

Bill
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 93 total points
ID: 39653283
If I recall correctly, you can use UNION if used that way:
insert into ite_tab (fld_name,ite_name) select * from
(select 'Ite 1' as [fld_name],NULL as [ite_name] from ite_tab
union all
select 'Ite 2' as [fld_name],NULL as [ite_name] from ite_tab) x

Open in new window

0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39653322
Hi Qlemo,

That does not work in MS Access.  It works is SQL Server but the query generator in MS Access causes a parameter failure.

Regards,

Bill
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39653376
Thanks a lot to all.
Bill,
do you have more details to use VBA to do the same thing?
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 39653391
Hi,

Sure.  Open any module or create a new one.  Enter the following:

Public Sub RunMyQueries()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "Query1"
  DoCmd.OpenQuery "Query2"
  DoCmd.SetWarnings True
End Sub

This will create a public subroutine that can be called with a button on a form or in a macro, etc.

It turns off the warning messages, runs the queries and turns the warnings back on.

You could also just add the 4 lines to the OnClick event on a form.

Regards,

Bill
0
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39653863
Sorry, I've got

run-time error '7873'

when trying to run this
Option Compare Database

Public Sub RunMyQueries()
  DoCmd.SetWarnings False
  DoCmd.OpenQuery "insert into ite_tab (fld_name,ite_name) select 'Ite 1' as [fld_name],NULL as [ite_name] from ite_tab"
  DoCmd.SetWarnings True
End Sub

Open in new window

0
 
LVL 14

Assisted Solution

by:Bill Ross
Bill Ross earned 32 total points
ID: 39654397
HI,

Is there some reason you do not want to save the query?  The OpenQuery command requires a saved Query.

The correct syntax if you want to run the SQL statement is:

Public Sub RunMyQueries()
  DoCmd.SetWarnings False
  DoCmd.RunSQL "insert into ite_tab (fld_name,ite_name) VALUES ('Ite 1',NULL);"
  DoCmd.SetWarnings True
End Sub
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now