Select Query using tables from different databases (Access 2003)

Topic

New Reply

I have a table called T_Parsed located on my hard drive that contain fields Item_ID, Rev, MFR, and a few others. I also have a table called T_MFRAlias on our network drive that contains fields MFR, MFRAlias, and a few others. Is their a way that I could do an unmatched Select query against these two tables (fields: T_Parsed.MFR and T_MFRAlias.MFRAlias) without using the ‘Link Table Manager…’?

Note that I have tried to use the IN clause, however I can’t get it to work.

Thanks in advance for any help,
Drew

Reply | Quote

Replies

What is your objection to importing or linking one of the tables into the other database?

Reply | Quote

Hans,

I guess I’m trying to keep the local version of this database as small and stripped as possible and I was thinking that bringing more data from the network version to the local version may not be needed for what I’m trying to accomplish at this time (???). In addition, I don’t know a lot about SQL’s and I’m trying to learn more about them to determine what I can do when running into certain situations.

As of today, I copy the network Alias table to the local database and then do the unmatched query to find all MFR’s that are not currently in the Alias table. I guess after I do the unmatched query, I could delete the local table to keep the local database as small in size as possible? I’m not sure what would be most efficent?

Regards,
Drew

Reply | Quote

If you create a link to the network Alias table in your local database, it’ll take up little space, since the data aren’t stored in your local database. You can run a query based on a local table and a linked table without problems.

If you import the network table, then delete it, you should compact the database (Tools | Database Utilities | Compact and Repair Database), for Access will not automatically reduce the size of the database after deleting objects or data. You can also set the database to Compact on Close in the General tab of Tools | Options… (this is a per-database setting)

Reply | Quote

If you really want to avoid linking or importing, you can do it like this:

SELECT T_Parsed.* FROM T_Parsed LEFT JOIN [H:FolderDatabase.mdb].T_MFRAlias ON T_Parsed.MFR = T_MFRAlias.MFRAlias WHERE T_MFRAlias.MFRAlias Is Null;

(Are you sure you want to join on MFR vs MFRAlias?)

Reply | Quote