I have a batch of separate databases that all have a vb proceedure which updates some tables within that particular database. Basically they are copying data from a very slow ODBC source to tables within that database so general use of the database is faster. These update proceedures currently have to be manually started.
Is there a way that I can set up another database which will automatically run the update proceedures on all the other databases?
I know one way is to link all the tables in the databases and copy the relevant queries and code to this new database, however this would prove confusing if I had to modify 1 single database. It will also mean I will have to duplicate data as I still want each indivdual database to have the option to update manually.
The idea is that I will schedule this new database to run every evening which will automatically update the data in all the other databases.
NB: All the databases open specific forms on startup, which may cause problems if the database has to be started to do the update. Also, A number of databases have the same table names and structure, however the stored records may not be the same (depends on the filters used on the underlying query).
Yes you can
you don't need to open the 'other' / target database, just link the tables as required, and delink afterwards.
The update process primarily read from text delimited files, imported into local tables, did some local processing (reformatting etc) then exported to the client databases as required.
The process was kicked off using an autoexec macro called from windows scheduler with appropriate switched in the command line
I can't remember the code, took a copy but it got lost in a system re-imaging.
you can connect to as many databases as required
the one thing I ddin't like about it was the lack of control on what actually happened. The system did nothing in terms of reporting what it had doen, so finding if anything, or what, had gone wrong was a pig. The key problem was if it fell over it could be difficutl to identify in time what or where the problem was.
It used a mix of sql update delete & append & maketable queries.