Update QRY - VBA Function in an update qry

[MVP - WannaB]

Hello,
I am trying to modify an update query to use a function rather then a hard
coded string for the table to update from.
The old query;
UPDATE [D:\active.mdb].t_transactions AS t_transactions_1 LEFT JOIN
t_transactions ON t_transactions_1.[Request#] = t_transactions.[Request#]
SET t_transactions.[date] = t_transactions_1.date, t_transactions.[foreign
bank] = t_transactions_1.[foreign bank], t_transactions.amount =
t_transactions_1.amount, t_transactions.[l/c #] = t_transactions_1.[l/c #],
t_transactions.[obligation number] = t_transactions_1.[obligation number],
t_transactions.[sight/usance] = t_transactions_1.[sight/usance];
The Function;
Public Function ActiveDB() As String

Dim dbs As DAO.Database

Set dbs = CurrentDb()
ActiveDB = Application.CurrentProject.Path & "/Active.mdb"
Set dbs = Nothing
End Function
My last attempt that fails;
UPDATE ActiveDB().t_transactions AS t_transactions_1 LEFT JOIN
t_transactions ON t_transactions_1.[Request#] = t_transactions.[Request#]
SET t_transactions.[date] = t_transactions_1.date, t_transactions.[foreign
bank] = t_transactions_1.[foreign bank], t_transactions.amount =
t_transactions_1.amount, t_transactions.[l/c #] = t_transactions_1.[l/c #],
t_transactions.[obligation number] = t_transactions_1.[obligation number],
t_transactions.[sight/usance] = t_transactions_1.[sight/usance];

I appreciate any help, Thank you.

 

[Clifford Bass]

Hello,

Do I understand correctly that you are attempting to update a table in
one database with the data from another? If so, how about linking to the
external table from the current database. Then you can just reference the
external table as if it were in the current database. Search your help for
"linked table", including the quotes, if you do not know how to do that.

Hope that helps,

Clifford Bass

 

[MVP - WannaB]

Hello Clifford, Yes I am attempting to update a table in one DB
with data from the same table in another DB. I am looking for a
way that I can do this without creating Linked tables.
By using the function I can then import that data to a development
copy of the database which might have a modified version of the same
table in it and I can do this from any folder that I might be currently
working
in and not need to change a link or the code inside the query.
I've used functions in queries before, in order to maybe strip a string
from a field, so I am assuming it is possible to use a function to select
the location of a DB, but there must be some special syntax that I can not
figure out.
Thanks for your time Clifford.
============================================

Hello,

Do I understand correctly that you are attempting to update a table in
one database with the data from another? If so, how about linking to the
external table from the current database. Then you can just reference the
external table as if it were in the current database. Search your help for
"linked table", including the quotes, if you do not know how to do that.

Hope that helps,

Clifford Bass

Hello,
I am trying to modify an update query to use a function rather then a hard
coded string for the table to update from.
The old query;
UPDATE [D:\active.mdb].t_transactions AS t_transactions_1 LEFT JOIN
t_transactions ON t_transactions_1.[Request#] = t_transactions.[Request#]
SET t_transactions.[date] = t_transactions_1.date, t_transactions.[foreign
bank] = t_transactions_1.[foreign bank], t_transactions.amount =
t_transactions_1.amount, t_transactions.[l/c #] = t_transactions_1.[l/c #],
t_transactions.[obligation number] = t_transactions_1.[obligation number],
t_transactions.[sight/usance] = t_transactions_1.[sight/usance];
The Function;
Public Function ActiveDB() As String

Dim dbs As DAO.Database

Set dbs = CurrentDb()
ActiveDB = Application.CurrentProject.Path & "/Active.mdb"
Set dbs = Nothing
End Function
My last attempt that fails;
UPDATE ActiveDB().t_transactions AS t_transactions_1 LEFT JOIN
t_transactions ON t_transactions_1.[Request#] = t_transactions.[Request#]
SET t_transactions.[date] = t_transactions_1.date, t_transactions.[foreign
bank] = t_transactions_1.[foreign bank], t_transactions.amount =
t_transactions_1.amount, t_transactions.[l/c #] = t_transactions_1.[l/c #],
t_transactions.[obligation number] = t_transactions_1.[obligation number],
t_transactions.[sight/usance] = t_transactions_1.[sight/usance];

I appreciate any help, Thank you.

 

[Clifford Bass]

Hi,

You are welcome. You are attempting to parameterize, so to speak, the
table name. To my knowledge that is not allowed within a query definition.
Which means that you will need to use some other method, such as doing the
update completely in code. Or yield to the need to modify the query
definition (QueryDef.SQL) or use linked tables. You can use code to do the
relinking. Essentially call the open file dialog, get the chosen file and
then update the TableDef.Connect property. Or do a DoCmd.DeleteObject
acTable, ... followed by a DoCmd.TransferDatabase acLink, ....

Of course, someone else may know different.

Clifford Bass