June 3, 2004 at 2:36 am
I have a DTS package that truncates all tables in an MS Access2000 database then repopulates them.
This is done on an hourly basis and it works fine.
The problem is that the Access database inflates, regardless of whether or not any additional data is added.
Within Access I can run the compact and repair option, but is there a way to tell SQL to compact the access database?
I cannot install Access on the server, but
a) Is there a command line utility that would do the same job?
b) Is there anything built into SQL Server to do the same job?
June 3, 2004 at 11:20 am
I am pretty sure you will have to have MS Access installed on the server to do the compact and repair function.
What about this:
Create an Access database with the empty table objects -- it should be at the minimum size. Each night, copy over your production Access database with the empty table copy. Then populate the empty database from SQL.
Hope this helps,
Kathi
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 4, 2004 at 1:01 am
Thanks Kathi,
That's what I did in the end and it seems to have worked OK.
I had a bit of a struggle getting DTS to set the precedence of the copy new task, but got this sorted in the end. I'm still not sure how DTS decides which is step 1 and which is step 2 when you join the tasks.
June 4, 2004 at 7:44 am
If all of the tasks are connected in one workflow, the precedence should flow according to the workflow arrows. If you have multiple workflows, then I guess it is up to SQL to determine.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 4, 2004 at 8:53 am
No, what I meant was that I started with two tasks.
1. Run the SQL to Access download DTS.
2. Run the copy of the downloaded file from the DB Server to the Web Server.
And now I have a third task that precedes them, which is our "copy an empty table version of the access database".
The DTS arrow defaults to running from "Run the SQL to Access download" to "Copy an empty table version of the access database", when in reality I want it to go the other way.
Each task should only trigger the next on successful completion.
I haven't worked out what it is that allows me to determine the precedence.
June 4, 2004 at 9:01 am
This is how I do it and I haven't have any trouble with precedence:
1. Select the first task that I want to run
2. Hold the shift key and select the second task
3. Click Workflow-> On Success (or whichever one I want to use)
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
June 8, 2004 at 8:17 am
Here are two possible methods for compacting the MS Access database.
(1) Set the MS Access database option "Compact on Close". If users open and close the mdb from MS Access, this can be an easy code-less solution.
(2) Use MS Access command line option
<path...>MSACCESS.EXE <path>MyAccessFile.MDB /compact
The command line option opens the MDB in the path, compacts it and closes it.
dxl
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply