SELECT FROM IN - how to concatenate string specifying external DB name?

[SLARRA]

Access uber-noob here. Got no love on an earlier post, so I'll try a more specific inquiry here.

In SQL for Access, how can I concatenate a string that specifies the path (fixed) and filename (corresponding to a user selection of Unit IDs from a list) for an external database that I want to extract records from using a SELECT FROM IN command?

Example:

External database is named xxyyy.mdb, where xxyyy is a Unit ID code
The path to xxyyy.mdb is C:\MyDatafiles\
Via a combobox selection in an objected named cbox0 on Form1, user had selected xxyyy from a table listing Unit IDs
I want to extract all records from xxyyy.mdb from a table named Locations in that external database
I want to put all those extracted records into a table named LocationsTemp in the active database

So, the hard-wired SQL statement, which works, looks like this:
INSERT INTO LocationsTemp
SELECT *
FROM Locations IN 'C:\MyDataFiles\xxyyy.mdb'[;];

But, I want that xxyyy part to be inserted into the SQL statement dynamically, so I want is something that looks like this:
INSERT INTO LocationsTemp
SELECT *
FROM Locations IN "'C:\MyDataFiles\" & [forms]![Form1]![cbox0] & ".mdb'[;];"

But, of course, that doesn't work, hence my inquiry here. I think there are 2 problems. First, I don't know how to tell Access that the stuff following IN is provided in the form of an expression, with concatenated bits composing the whole path and file name. Second, I'm not quite sure I'm "calling" the contents of the combobox selection with the proper syntax.

Thanks in advance!

SDL

 

[ChrisM]

I am not an Access expert, but I don't think you can do this (but certainly could be wrong). If you are going to try to run a normal SQL query then the tables must be present in the database (ie already present as linked tables from your other database).

I would think you either need to link all the possible tables,

or

Use ADO/DAO to create a recordset based on your dynamic data. (this is what I would do in your case)

I've also never seen "IN" used like that, I don't think that is a valid use. IN is used for multiple criteria expressions, like "WHERE Name IN (Jim, Bob, Joe)" as opposed to "WHERE Name = Jim OR Name = Bob OR Name = Joe"

 

[SLARRA]

Like I said, I'm a total newbie, so I wouldn't be at all surprised if what I'm trying to do and how I'm trying to do it would make an experienced user cringe.

That bit about the IN, it does work to reference an external database file, and my first example SQL statement does, in fact, fetch those records.
For anyone who might be interested, there's more on the MS Access IN clause here: IN Clause (Microsoft Access SQL) [Access 2007 Developer Reference]

I don't know what ADO/DAO is, but I'll start Googling. Thanks for your input! Any other suggestions are most welcome!

SDL