compacting database

[Jason]

Someone posted about compacting a backend file from within the front end. I
have had at least a 99% failure rate doing it this way. However, using
Tools - compact works but is a manual task.

 

[Arvin Meyer [MVP]]

Someone posted about compacting a backend file from within the front end.
I have had at least a 99% failure rate doing it this way. However, using
Tools - compact works but is a manual task.

Have you tried:

DBEngine.CompactDatabase "F:\yourdir\oldname.mdb", "F:\yourdir\newname.mdb",

or to the same name:

DBEngine.CompactDatabase "F:\yourdir\oldname.mdb", "F:\yourdir\oldname.mdb"

 

[Jason]

Can not companct database to same name - Access comes up with an error.
However, I'll try it when I get time to.

 

[Jason]

How do I delete the existing mdb file and rename the compacted file within
access?

Thanks,
J.

 

[Arvin Meyer [MVP]]

The Kill statement will delete the old file.

Kill "F:\yourdir\oldname.mdb",

The Name statement will rename a file:

Name "F:\yourdir\newname.mdb", "F:\yourdir\oldname.mdb"
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jason]

Thanks,
Was using the kill and filecopy to temp directory - therefore didn't need
name but I might actually use name in the current directory that would mean
the process would be faster.

 

[Jason]

are you sure it is name as this is usually Form.name:
did f1 on name and found that it is supposed to be as instead of comma/

Is there facility to move also?
Thanks,

 

[Jason]

Also found taht you have oldname and newname back to front.

Thanks for your help.

 

[Arvin Meyer [MVP]]

There is the MoveFile Method.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

No, not if you are renaming the new file that you've just created:

Name "F:\yourdir\newname.mdb", "F:\yourdir\oldname.mdb"

The above names the newly created filename back to the old (original) name.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[JohnC]

I think I read that if the back-end has the option Compact On Close, then
the last front end to exit will cause the back end to compact. Never tried
this and perhaps could be overkill.

John

 

[Jason]

This would compact everytime the form is closed multiplied by several times
per day.

 

[Jason]

There's no help for FileMove, nor MoveFile.

There is the MoveFile Method.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

You have to look in the VBA IDE help for help on VBA code:

Description:
Moves one or more files from one location to another.

Syntax:
object.MoveFile source, destination

The MoveFile method syntax has these parts:

Part Description
object Required. Always the name of a FileSystemObject.

source Required. The path to the file or files to be moved. The source
argument string can contain wildcard characters in the last path component
only.

destination Required. The path where the file or files are to be
moved. The destination argument can't contain wildcard characters.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Jason]

Please explain IDE help. Would this work like explorers move where when
moved on the same partition it takes no where as long as copying?
Thanks,

 

[Arvin Meyer [MVP]]

The IDE is the code window. It works like any other MS Office help. Just
open any code window and from the menu, choose help.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com