Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: Code that updates external databases

    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).

  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    Have a read here.

    Pay particular attention to the Run method.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts