Database as query parameter - is it possible? (1 Viewer)

[Michiel]

I use a query to update an external database with most recent data, as follows:

INSERT INTO [;DATABASE=C:\Users\michi\TParchive\Archive.accdb].Archive
SELECT * FROM Archive
WHERE OrderId IN (
    SELECT Src.OrderId FROM Archive Src
    LEFT JOIN [;DATABASE=C:\Users\michi\TParchive\Archive.accdb].Archive Dest
    ON Src.OrderId = Dest.OrderId
    WHERE Dest.OrderId IS null
)

Which works perfect.
But I would like to parameterize the database name. Is that possible using a syntax like:

PARAMETERS [MyDB] char;
INSERT INTO [;MyDB].Archive
SELECT * FROM Archive
WHERE OrderId IN (
    SELECT Src.OrderId FROM Archive Src
    LEFT JOIN [;MyDB].Archive Dest
    ON Src.OrderId = Dest.OrderId
    WHERE Dest.OrderId IS null
)

I have tried all sorts of things but found no way to get that to work., Anyone tips : is this possible at all ?

Tx !

 

[Gasman]

I think you would need to amend the qdf?
I would likely copy a template query, replace MYDB with the dbname, and run that query, then delete it when no longer needed.

 

[ebs17]

Parameterization is not possible here. However, you can compose an SQL statement appropriately before executing it.

sMyDB = "C:\Users\michi\TParchive\Archive.accdb"
sSQL = "INSERT INTO [" & sMyDB & "].Archive ([FieldList])" & _
       " SELECT S.[FieldList] FROM Archive AS S LEFT JOIN [" & sMyDB & "].Archive AS D" & _
       " ON S.OrderId = D.OrderId WHERE D.OrderId IS NULL"
CurrentDb.Execute sSQL, dbFailOnError

or with an external-internal exchange

exDb = OpenDatabase("C:\Users\michi\TParchive\Archive.accdb")
sMyDB = CurrentDb.Name
sSQL = "INSERT INTO Archive ([FieldList])" & _
       " SELECT S.[FieldList] FROM [" & sMyDB & "].Archive AS S LEFT JOIN Archive AS D" & _
       " ON S.OrderId = D.OrderId WHERE D.OrderId IS NULL"
exDb.Execute sSQL, dbFailOnError
exDb.Close