Inner join on IN query

[Lars Brownie]

I have the following query:

SELECT ID_entity, Description
FROM tblEntity IN 'C:\MyTestFiles\Test.mdb';

Is it possible to add another table from the external database and make an
inner join? I don't see how this can be done in the way as shown above as
all SELECT fields must come from the same table.

Thanks, Lars

 

[Paul Shapiro]

I don't use the IN clause, so this is just a guess, but did you try
something like:
Select A.ID_entity, A.Description
From
tblEntity IN 'C:\MyTestFiles\Test.mdb' As A
Inner Join tblB IN 'C:\MyTestFiles\Test.mdb' As B
On A.someField=B.someField

What would work for sure is that you can either permanently or temporarily
(with VBA) create links to the tables you want to work with in the 2nd db.

 

[Douglas J. Steele]

I prefer using

Select A.ID_entity, A.Description
From
[;DATABASE=C:\MyTestFiles\Test.mdb].tblEntity As A
Inner Join [;DATABASE=C:\MyTestFiles\Test.mdb].tblB As B
On A.someField=B.someField

No advantage to one over the other. I just prefer not using the IN keyword
in that context.

 

[Guest]

Is this for read only? You may have trouble making an updateable
query. Try it an let us know.

(david)

 

[Lars Brownie]

Thanks all. It works.
Lars

I prefer using

Select A.ID_entity, A.Description
From
[;DATABASE=C:\MyTestFiles\Test.mdb].tblEntity As A
Inner Join [;DATABASE=C:\MyTestFiles\Test.mdb].tblB As B
On A.someField=B.someField

No advantage to one over the other. I just prefer not using the IN keyword
in that context.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I don't use the IN clause, so this is just a guess, but did you try
something like:
Select A.ID_entity, A.Description
From
tblEntity IN 'C:\MyTestFiles\Test.mdb' As A
Inner Join tblB IN 'C:\MyTestFiles\Test.mdb' As B
On A.someField=B.someField

What would work for sure is that you can either permanently or
temporarily (with VBA) create links to the tables you want to work with
in the 2nd db.