Q
Problem solve Get help with specific problems with your technologies, process and projects.

Selecting from two Access databases

I have an Access 97 database that contains over 50,000 records. At times I need to download and make changes to...

the MDB. I created two databases. One database called DB1 and a second database named DB2. DB1 will contain all the records for the year 2001 and the DB2 will contain all of this years data 2002. The table name is Records (in both databases). I want to select the lastname, firstname and entry fields between two dates. My variables are named Date1 and Date2. The date field in my database is DateWorked. I want to know how to access information from two databases using one SQL statement. For example,

SELECT LastName FROM the two databases 
 WHERE Date1 >= 'Date1' 
   AND Date2 <= 'Date2'

I searched the Web and had no luck! Can you please help me out?


You mentioned that you download the MDB file, so presumably you have them somewhere on your local drive and can get at both databases easily. The best way is to Link the table from one database into the other.

Open one of the databases, and under the File menu item, select Get External Data and then select Link Tables. In the next dialog window, select the other MDB file as the database to link to, and another window opens which lets you select the table(s) to link. Back in your first database, the Tables tab will now show the linked table with a little arrow icon beside it, indicating that it is a linked table, not actually present in this database. Access will automatically give it a unique name if, as in your case, it has the same name as a table that already exists. So if the table Records exists, the linked table will be called Records1. (You can rename them easily enough if you wish.) Now you can build whatever queries you like, as though both tables actually were in the same database.

To do the type of query you described, you have to think of the tables not joined row to row, but rather "stacked one on top of the other" or "combined" into one larger table containing rows from both tables. This is the mental image that should suggest a UNION.

  select LastName, FirstName, DateWorked
    from Records 
   where DateWorked between Date1 and Date2
union all
  select LastName, FirstName, DateWorked
    from Records1
   where DateWorked between Date1 and Date2

Note that UNION ALL does not eliminate duplicates which might come from the two tables. This seems safe enough, assuming that the same DateWorked is not going to exist in both the 2001 and 2002 tables.

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was last published in April 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close