I have a back-end / multiple front-end database set up. I need to regularly update the BE with data from an external source. The tables in the external source are linked through SQL server.
Updating directly from the external tables doesn’t really work as it is extremely slow and the tables don’t have natural primary keys I link to. So,
I’ve found the quickest way is to create temp tables and update from them.
I’ve created an Auto Update DB that runs over night however the problem is that it bloats in file size very quickly due to the temp tables. So, I must repair / compact each morning, or the update DB fails due to file size.
As a work around I set the database repair / compact on close and was going to use a third DB application to open the update DB each night, let the update process run, then close it and compact.
However, I’ve read this isn’t the right way to deal with this issue, as I should be able to do all this without the bloat / temp tables.
Can anyone help with a way to do my updates without the bloat and regular compact /repair? Then I can just set it to update each night and forget about it!
Updating directly from the external tables doesn’t really work as it is extremely slow and the tables don’t have natural primary keys I link to. So,
I’ve found the quickest way is to create temp tables and update from them.
I’ve created an Auto Update DB that runs over night however the problem is that it bloats in file size very quickly due to the temp tables. So, I must repair / compact each morning, or the update DB fails due to file size.
As a work around I set the database repair / compact on close and was going to use a third DB application to open the update DB each night, let the update process run, then close it and compact.
However, I’ve read this isn’t the right way to deal with this issue, as I should be able to do all this without the bloat / temp tables.
Can anyone help with a way to do my updates without the bloat and regular compact /repair? Then I can just set it to update each night and forget about it!