The 'IN' clause

  • Thread starter Thread starter JonWayn
  • Start date Start date
J

JonWayn

can the 'IN' clause be used to select records from one database and insert
into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb' ([Citation_#],
[Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name the the
field names, aliasing, etc. Nothing works.
 
JonWayn said:
can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb'
([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...

I don't think you can have two IN clauses in one SQL statement.
However, you can probably open a Database object on the source database
and use that object to execute a query that names the target database in
an IN clause. Something like this air code:

Dim db As DAO.Database

Set db = DBEngine.OpenDatabase("c:\working\access\bay\bay2.mdb");

db.Execute _
"INSERT INTO Bay Traffic1 ([Citation_#], [Name]) " & _
"IN 'c:\working\access\bay\bay.mdb'
"SELECT [citation_#], [Name] FROM [Bay Traffic1]"

db.Close
Set db = Nothing
 
CSmith said:
Hi,

Close! :-) Check out the [old] "Tip #12" on my site, which is similar.


JonWayn said:
can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb'
([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...
Click to expand...

I stand corrected. That does seem to work. I'd guess then that this
should work

insert into [Bay Traffic1] ([Citation_#], [Name])
IN 'c:\working\access\bay\bay.mdb'
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'
 
Dirk Goldgar said:
JonWayn said:
can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb'
([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...

I don't think you can have two IN clauses in one SQL statement.
Click to expand...

Apparently I'm mistaken. I just tested a SQL statement with two IN
clauses and it worked. The Jet SQL help file is misleading.
 
Yep! I remember writing those Jet-twisters way back in 1994 with Jet 1 or 2.
<g>

--
Calvin Smith
http://www.CalvinSmithSoftware.com - Automation Code
http://www.SpanglesNY.com - Fendi, Prada, etc - 60% off


Dirk Goldgar said:
CSmith said:
Hi,

Close! :-) Check out the [old] "Tip #12" on my site, which is similar.


JonWayn said:
can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb'
([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...
Click to expand...

I stand corrected. That does seem to work. I'd guess then that this
should work

insert into [Bay Traffic1] ([Citation_#], [Name])
IN 'c:\working\access\bay\bay.mdb'
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'
Click to expand...
 
That works. But I must tell you, that is not the syntax given in Access help
for the IN Clause.


Dirk Goldgar said:
CSmith said:
Hi,

Close! :-) Check out the [old] "Tip #12" on my site, which is similar.


JonWayn said:
can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO statement
insert into [Bay Traffic1] in 'c:\working\access\bay\bay.mdb'
([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...
Click to expand...

I stand corrected. That does seem to work. I'd guess then that this
should work

insert into [Bay Traffic1] ([Citation_#], [Name])
IN 'c:\working\access\bay\bay.mdb'
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
JonWayn said:
That works. But I must tell you, that is not the syntax given in
Access help for the IN Clause.


Dirk Goldgar said:
CSmith said:
Hi,

Close! :-) Check out the [old] "Tip #12" on my site, which is
similar.


can the 'IN' clause be used to select records from one database and
insert into another? If so, what is the syntax?

THe following gives an error: 'Syntax error in INSERT INTO
statement insert into [Bay Traffic1] in
'c:\working\access\bay\bay.mdb' ([Citation_#], [Name])
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'

I have tried multiple other variants like appending the table name
the the field names, aliasing, etc. Nothing works.
Click to expand...

I stand corrected. That does seem to work. I'd guess then that this
should work

insert into [Bay Traffic1] ([Citation_#], [Name])
IN 'c:\working\access\bay\bay.mdb'
select [citation_#], Name
from [Bay Traffic1] IN 'c:\working\access\bay\bay2.mdb'
Click to expand...
Click to expand...

I agree that the help topic is rather misleading, but a thorough reading
seems to me to confirm that the syntax

IN 'Path To\OtherDB.mdb'

is among the valid formats for the IN clause. Looking at the syntax
given for the INSERT statement, though, that help topic appears to say
that you can't have an IN clause in both the target and source
table-specifications. That is plainly untrue, as Calvin Smith pointed
out.
 
Back
Top