Update local table from remote db

[Jeffr.Lipton]

I have 2 databases that I want to have run the same module, and update local tables.

db1: contains universal module
db2: calls module in db1 to update local tables
db3: calls module in db1 to update local tables

This way, if I need to make a change to the module, I can just make the change in db1, rather than hope to keep db2 and db3 in sync.

When I call db1 from db2, it updates the tables in db1. How do I get it to update the tables in db2?

 

[pbaldy]

You haven't shown the code, but it sounds like you could add an input parameter to the public function for which db to update.

 

[Jeffr.Lipton]

I figured it out:

Dim appAccess As Access.Application
dim sql_Clr_Table, sql_Insert as String
 
'clear local table 
sql_Clr_Table = "DELETE * FROM tblNew_Action_Items;"
DoCmd.RunSQL (sql_Clr_Table)

'Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")
 
'Open Test Database in Microsoft Access window.
appAccess.OpenCurrentDatabase "\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb", False
 
'Run Sub procedure.
appAccess.Run "Action_Item_List"

'Insert extrnal table into local
sql_Insert = "Insert INTO tblNew_Action_Items  SELECT * FROM tblNew_Action_Items IN '\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb'"
DoCmd.RunSQL sql_Insert

Set appAccess = Nothing

 

[pbaldy]

Glad you got it working, though I don't see where that will operate against the database that called it.

 

[Jeffr.Lipton]

It worked once, then stopped working. GRRRR!

I'm running this in db2, and "\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb" is db1.

After a bit of experimentation, this seems to work:

DoCmd.DeleteObject acTable, "tblNew_Action_Items"

'Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")
 
'Open Test Database in Microsoft Access window.
appAccess.OpenCurrentDatabase "\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb", False
 
'Run Sub procedure.
appAccess.Run "Action_Item_List"

'Insert extrnal table into local
sql_Insert = "Insert INTO tblNew_Action_Items  SELECT * FROM tblNew_Action_Items IN '\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb'"
DoCmd.RunSQL sql_Insert

Set appAccess = Nothing


DoCmd.TransferDatabase acImport, "Microsoft Access", "\\sce\workgroup\TDBU2\Grid Contracts\IT Applications\Production\True-Ups\FrontEnd\Create Action Items.accdb", acTable, "tblNew_Action_Items", "tblNew_Action_Items", False