How to compact the db?

polina

Registered User.
Local time
Today, 00:45
Joined
Aug 21, 2002
Messages
100
Hi

Is there a way to code the process of compacting the database?

Thanks
 
Function CompactDB()
'The following will compact the current Database
SendKeys "{F10}"
SendKeys "TDC"
End Function
 
Here are some links that might help:

"Compact A List" is a free MS Access database that allows you to backup, repair, and compact a saved list of Access database files. It also saves the results of these actions in a table for later viewing, making it suitable for batch or scheduled execution.

http://www.peterssoftware.com/cal.htm

Also, there's a nice utility for scheduling compacting operations from Calvin Smith called "CompactIt" at:

http://home.sprintmail.com/~calvinsmith/compact.htm

You can also use the TSI SOON utility to close a database, compact it, and then open it again (it also copies NT file permissions to the new file):

http://www.trigeminal.com/lang/1033/utility.asp?ItemID=8#8

Another utility is Compact Current MDB From Code:

http://briefcase.yahoo.com/lylefair

FMS, Inc has a product called Total Visual Agent 2000 that handles scheduled compacting and much more:

http://www.fmsinc.com/products/Agent/index.html

Here's some more information about reducing database size:

http://www.peterssoftware.com/t_dbsize.htm

Hope this helps,
 
I need to be able to compact an Access MDE file on exit. This MDE file would need to be distributed to multiple users so I would want to avoid having an additional configuration on each target machine.

Looking at the options in this thread:

TSI SOON seemed suitable but I think requires configuration on each machine.

The other possibility was :

"Another utility is Compact Current MDB From Code"

but there are no files when I follow the link

I also thought about using

Function CompactDB()
'The following will compact the current Database
SendKeys "{F10}"
SendKeys "TDC"
End Function

and writing some code to set a flag when closing the database which immediately shuts it down on the next open.

Any advice please?
 
Are m the users using the same db simultaneously or individual copies of the same db? If they are using the same db simultaneously you could setup Tools - Options - General - Compact On Close so that when the db usage is doen to one user and they exit it will compact. It will not compact if it is being accessed by more than one user though.

Additionaly, if this is a split db it will not compact the back end which is what needs the compaction on a regular basis. There is a good example of how to compact a back end via code on www.rogersaccesslibrary.com
 
I should have said this is in Access 97. These will be individual copies with one user at any time. It is in fact a front end for an SQL Server backend.
 
More clarification please. Each user will have their own front end but all will use the same SQL backend?
 
Yes exactly.

The front end will be an Access 97 MDE file on the users PC only containing temporary tables, code, forms, reports etc.

The backend is a single SQL Server instance on the WAN.
 
In that case I would make a sceduled task using a batch file to compact the backend once per day.
 
SQL Server backends don't need compacting. I need to somehow to replicate the Access 2000 functionality so that Access 97 compacts on exit.
 
Why would the front ends need compacting if they only hold forms and queries. Compact the FE after completing your work on it and it shouldn't need it after that unless you change something, unless I'm missing something.
 
Correct me if I am wrong, but I am fairly sure that running queries in an Access front end inflates the size over time. Presumably Access creates temporary works space under the bonnet which it does not recover.
 
I'm not sure on that. Perhaps someone with more knowledge than I would care to chime in on whether running queries (and possibly reports) inflates the FE which could benefit from compacting.
 
Peter D - I downloaded your software for the "compact a list". (Thanks very much) It seems to work fine for databases on the C drive but I can't get it to work on databases that have mapped drives to a server......

Any clues? - Am I doing / not doing something?

I'm using Access97

Col
 
Do you mean if an .mdb file resides on a drive that is mapped to "F:" on your computer, you can't specifiy "F:\MyDb.mdb"?
 
Yes Peter thats right-

I have databases on a server with mapped drives on my PC

e.g

O:\Bone BankV2.mdb

when I run the compact programme is comes up with this error-

"Starting; Error during database compact = 3356; Compact Failed; There was a problem with at least one step; "

I have tried all combinations of the path
O:Bone BankV2.mdb
O:\Stort\BoneBank\Bone BankV2.mdb
O:\Bone BankV2.mdb
etc

There are Access general passwords (not workgroups) on the databases, would that make a difference.?

I appreciate your time, and your Db is excellent, thank you.

Col
 
The description for error 3356 is "You attempted to open a
database that is already opened exclusively by user <name> on machine <machine>. Try again when the database is available."

Does this make sense for your situation? Do you have exclusive access to the database?

If you need to enter a password, you'll have to edit the Compact A List subroutine that does the compact and hardcode the password there.

Hope this helps,
 
ColinEssex said:
"Starting; Error during database compact = 3356; Compact Failed; There was a problem with at least one step; "

...

There are Access general passwords (not workgroups) on the databases, would that make a difference.?

That what I've run into as well with database files that are password protected (General not workgroup).

Originally posted by Perter D
If you need to enter a password, you'll have to edit the Compact A List subroutine that does the compact and hardcode the password there.

Do you have a piece of code I could use to hard code the password in? If so, where would the code go?

I'd probably add the password in the table and have an input mask where MyPassword would look like this ********** (is that even possible?) and have the code refer to that field for the password, or prompt the user to enter it for each database file it is compacting. Maybe not all, but have a Yes/No fields in the table that stores the db file path to mark it as having a pasword or not. If YES, then promt for password.
(This is assuming that I have the skill to make that work, or the tenacity to work by trial and error!)
:p
 
The 3356 error is telling you that you dont' have exclusive access to the database. If you want to supply a passoword, that's fine, but at some point, you're going to have to address the 3356 error.


There's code in the ng_Step2 routine that looks like this:

If strFileName = "C:\MyFolder\MyFileName.mdb" Then
DBEngine.CompactDatabase strFileName, strFolderPart & strTempFileName, , ";pwd=MyPassword"
Else
DBEngine.CompactDatabase strFileName, strFolderPart & strTempFileName
End If


You can modify this code to supply a password for a specific database.

hope this helps,
 

Users who are viewing this thread

Back
Top Bottom