Query Unlinked Tables Using SQL 1

[OrWolf]

I want to compare a table in a primary database to a like table in another database without adding it as a linked table using a sql statement. Can anyone help me with this?

Additional explanation:
I'm creating SQL statements to archive data into four individual databases based on regions. Up to this point it's been smooth sailing using a variable location based on the users region to determine the correct database, then 'INSERT INTO' sql statements to add the necessary records prior to deleting them. However I have a master department table that I want to archive without appending duplicate records, and without deleting records. This is why I need to create the comparison and I'd rather do it without creating four linked tables for both my department and facility tables. This very well may be wishful thinking.

Any suggestions would be greatly appreciated.

 

[RickSpr]

You can open an additional database using the OpenDatabase method. It returns a reference to a Database object. You can assign that object to a variable, then use the variable to open a recordset on a table in the database.
Sample:

    Set db2 = OpenDatabase("C:\OtherDb.mdb")
    Set rst = db2.OpenRecordset("ForeignTable")
    (code to use the recordset)
    rst.Close
    Set rst = Nothing
    db2.Close
    Set db2 = Nothing

Is that what you were looking for? Rick Sprague

 

[OrWolf]

Will Access allow 'rst' as a table name in a SQL statement as shown below if it's set just prior to the do command?

DoCmd.RunSQL "UPDATE rst INNER JOIN DeptMain " _
& "ON (DeptMain.ID = rst.ID) " _
& "AND (rst.ID = DeptMain.ID) SET DeptMain.Archived = -1 " _
& "WHERE (((rst.Facility)=[Forms]![ArchiveMenu]![OrganizationLookup]) " _
& "AND ((DeptMain.SurveyKey)=[Forms]![ArchiveMenu]![SurveyLookup]));"

 

[RickSpr]

No, because rst isn't a table name, it's the name of a recordset variable. You'll have to use the actual table or query name, or you can use a string variable containing the table or query name:
DoCmd.RunSQL "UPDATE " & strTableName & " INNER JOIN..." Rick Sprague

 

[OrWolf]

However that goes back to my original question. I'm looking to reference an external table without creating a linked table in the current database, and using the reference in a SQL statement.

I appreciate the help.

 

[RickSpr]

Ah, now I see my example was just a little inappropriate. Here's how:

    Set db2 = OpenDatabase("C:\OtherDb.mdb")
    db2.Execute("UPDATE table SET ...")
    db2.Close
    Set db2 = Nothing

Rick Sprague

 

[OrWolf]

That was exactly what the doctor ordered!

Now I only have to create a single linked table in my back-end archive databases to the live database table, rather than create a linked table in my live for each region (this would have been many tables). It's basically the same, but really helps to reduce the clutter in my live front-end database.

Thanks you very much for the help!