The 'IN' clause

[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.

 

[CSmith]

Hi,

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

 

[Dirk Goldgar]

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.

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

 

[Dirk Goldgar]

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.

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]

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.

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

Apparently I'm mistaken. I just tested a SQL statement with two IN
clauses and it worked. The Jet SQL help file is misleading.

 

[CSmith]

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

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.

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'

 

[JonWayn]

That works. But I must tell you, that is not the syntax given in Access help
for the IN Clause.

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.

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)

 

[Dirk Goldgar]

That works. But I must tell you, that is not the syntax given in
Access help for the IN Clause.

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.

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'

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.