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

[slarra]

Access uber-noob here. Seriously - I'm just getting started with Access (this is my first database project, but I'm the most "qualified" guy in my office to take on this task). I can barely spell SQL, and yet I'm now trying to put together a series of queries to pull about a dozen tables' worth of data from 100+ little databases into one big database - and I'm really close to pulling this off! This is a one-shot deal, so I don't need an elegant or even particularly efficient solution here.

To make those records unique in the big database, I need to add a Unit (i.e. Office) Identifier to each record, since they will otherwise have duplicate values. I've got that part figured out, and I've even managed to set-up a combobox where I select the Unit ID from a list (and I'll repeat that process 100+ times as I connect to those external, individual databases and extract their records to put into the big database). And, I have written a teeny VBA module to execute a series of SQL queries to extract those records, add and populate the Unit ID field, and then push those records to the corresponding tables in the big database. But I need help to get one of those queries tuned-up so that it will insert the selected Unit ID into the filename when I refer to its external (individual) database.

So... In SQL for Access, how can I concatenate a string that specifies the path (fixed) and filename (corresponding to my selection of Unit ID via combobox from a list) for the 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, I have 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 currently 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

[rpeare]

you can't really do that sort of dynamic SQL command without VBA

so let's say your form has a button and in that button you want to generate your vba code (SQL statement) and run it you'd generate your code like this:

dim db as database
dim sSQL as string

ssql = "INSERT INTO LocationsTemp SELECT * FROM Locations IN 'C:\MyDataFiles\" & [forms]![Form1]![cbox0] & ".mdb'"

db.execute (ssql)

You may have to play with the syntax but if you put in a
debug.print ssql

BEFORE you try the db.execute command so you can see the SQL command that's going to run then copy and paste that into a query and see if it does what you want. If it does the ON CLICK event of your button should work as well and you can then put in the db.execute command.

[slarra]

Did I mention I'm a super newbie?

The example you suggested above worked great, but only once. When I tried to re-run it, I get the following Microsoft Visual Basic error: "Run-time error '91'" Object variable or With block variable not set"

Do I need to clear the variables before trying to re-run the script?

Here's what my VBA looks like:

Option Compare Database

Private Sub Fetch_Click()

  MsgBox "Extracting Data for: " & cbox0
    
    ' Run first query
    Dim db As Database
    Dim sSQL As String
    sSQL = "INSERT INTO LocationsTemp SELECT * FROM Locations IN 'C:\MyDataFiles\" & [Forms]![Form1]![cbox0] & ".mdb'"
    Debug.Print sSQL
    'db.Execute (sSQL)
    

End Sub

I have no idea what I'm doing, so I really appreciate your help!

Thanks!

SDL

[June7]

Think I do something like that. I don't declare database object variable. Example:

CurrentDb.Execute "INSERT INTO destinationTable SELECT * FROM [" & strSourcePath & "].sourceTable/Query"

In your case, try:

CurrentDb.Execute "INSERT INTO LocationsTemp SELECT * FROM [C:\MyDataFiles\" & Me.cbox0 & ".mdb].Locations"

[slarra]

Yes! That works! Thank you!

Here's the VBA:

Option Compare Database


Private Sub Fetch_Click()


  MsgBox "Extracting Data for: " & cbox0


  CurrentDb.Execute "INSERT INTO LocationsTemp SELECT * FROM [C:\MyDataFiles\" & Me.cbox0 & ".mdb].Locations"
    
End Sub

Thanks!

SDL