DTS Export to Access

[nero]

Hi all

I have set up a DTS to export nine SQL7.0 tables and their contents to an Access 97 database.

I have set it up to clear the contents of the existing tables and repopulate them with the SQL records. This is then scheduled to run every twenty four hours.

This way I provide a copy of the SQL database in Access format for download by my client.

My problem is that I have noticed a bloat in Access mdb file size each time the package is executed. If I get into Access and compact the db it returns to its orignal size, but this impractical as the server this will be deployed on does not have Access on it.

Has anyone come across this issue? Any ideas on how to solve this?

 

[foxdev]

The way I got around this problem was to have an "empty" Access DB that had the table structures. When I was ready to run the export, I would copy the empty Access DB to a different name, and have the export write into that one. The base Access DB was never written to itself, so the bloat was not an issue.

This can all be accomplished either within DTS, or by a pre-running batch job. Robert Bradley

http://www.agrainofhope.org

http://www.lowcostprints.com

http://www.foxdev.com