Obtaining data from a different access database

[Roberto]

Hello,

Because of the 2GB size limit, I have divided a single database into several
access databases. However, when I show the tables to build a query, it only
displays the tables availabe on that specific database. Is it possible to
create a query in database (a) and pull data from tables that are in database
(b). If so, how?

Thanks!

Robert

 

[Beetle]

Depends on what you mean by divided. You can link a single Front End
to multiple Back Ends in a split application.

 

[John Spencer]

You can set up links to the tables in the other databases and then treat them
as if they were in the database.

Alternative is to use the In syntax

SELECT *
FROM SomeTable In 'H:\My Other Database.mdb'

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Roberto]

John,

Thank you for responding.

How can I setup links?

 

[John Spencer]

In Versions prior to Access 2007
File: Get External Data : Link Tables

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Hirji]

John,

In Access 2007 when getting data from tables in a different Access database
I have run into a bizarre problem. The syntax that used to work till the
previous version of Access was: “Select * from [DB2.MDB].Tab1â€. Type, save
and run this query in Access 2007 and it works perfectly, but open the saved
query for editing and you’ll find Access has reformatted it to: “Select *
from (DB2.MDB) Tab1â€. Try saving this query and Access reports a syntax
error. What can be more strange: Access reports a syntax error on a syntax
that it created on its own?

I found your post that gives an alternative syntax: “Select * from Tab1 in
DB2.MDBâ€. This works perfectly for Select and Insert Into queries, but fails
in an update query, for instance: “Update Tab2 in DB2.MDB Inner Join Tab1 in
DB1.MDB On DB2.Fld1=DB1.Fld1 Set DB2.Fld2=DB1.Fld2â€. This gives a syntax
error.

Can you help me please?

Hirji E. Nagarwalla

 

[John Spencer (MVP)]

Sorry, I can't help you. Suggest you start a new thread with your question.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John,

In Access 2007 when getting data from tables in a different Access database
I have run into a bizarre problem. The syntax that used to work till the
previous version of Access was: “Select * from [DB2.MDB].Tab1â€. Type, save
and run this query in Access 2007 and it works perfectly, but open the saved
query for editing and you’ll find Access has reformatted it to: “Select *
from (DB2.MDB) Tab1â€. Try saving this query and Access reports a syntax
error. What can be more strange: Access reports a syntax error on a syntax
that it created on its own?

I found your post that gives an alternative syntax: “Select * from Tab1 in
DB2.MDBâ€. This works perfectly for Select and Insert Into queries, but fails
in an update query, for instance: “Update Tab2 in DB2.MDB Inner Join Tab1 in
DB1.MDB On DB2.Fld1=DB1.Fld1 Set DB2.Fld2=DB1.Fld2â€. This gives a syntax
error.

Can you help me please?

Hirji E. Nagarwalla

You can set up links to the tables in the other databases and then treat them
as if they were in the database.

Alternative is to use the In syntax

SELECT *
FROM SomeTable In 'H:\My Other Database.mdb'

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County