Unanswered: Using a Macro to update other databases?
I have a bit of a unique setup with my database where I have 5 large tables housed in their own databases and linked up to a main one (this is because all 5 tables will not fit in the database as it will exceed 2 gigs). I basically want the central database to be able to essentially "call" the other 5 database files with the tables to run an update query on them. Problem is, I can't figure out how to get this macro to run it properly. I tried using the RunCommand->OpenDatabase command but it doesn't seem to do much of another. Any of the other open query commands will only launch queries on that one particular database and not the others. Any suggestions as to how I can create a macro to reach the other databases to automatically run the MakeTable query to update its data?
you can create a vb function to programtiacally link to separate tables
so you could disconnect / delink all tables
link to the first table, make your changes, delink table1
then process through all remaining 4 tables
I'll have to look up the details but I think its throigh the connection object.
However if you are compromising your data because it exceeds JET's intrinsic limits then you probably should consider another means of data storage. on the face of it MSDE (a compromised version of SQL server) may be of use, failing that MySQL and if you have the budget SQL Server itself