compact BE Access 2002 runtime?

[Mattias]

Hi

I would like to have help with code so I will be able to compact the
backend-database. I am using Access 2002 and would like to be woriking in
the runtime environment.

Is it possible to have the code to find out where the backend is located in
the network automaticly ...not having to put the whole path to the backend
in the code.

Mattias

 

[Joshua A. Booker]

Mattias,

You can use the connect property of a linked table in VBA code to return the
BE path like this:

dim stPath as string
stPath = mid(currentdb.tabledefs("LinkedTableName").connect,11)

Where 'LinkedTableName' is the name of any linked table.

HTH,
Josh

 

[Mattias]

hi and thanks for the reply,

can you please assist me in implementing the compact line as well....getting
syntax error.....

Dim stPath As String
stPath = Mid(CurrentDb.TableDefs("Anställda").Connect, 11)
C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
"stPath\MbaseMuseumServer.mdb" /compact

Mattias

 

[Mattias]

Thank you for your help!

Mattias

Mattias,

'declare variables
Dim stPath As String, stNewPath as String, stBUPath as string

'get path to linked table
stPath = Mid(CurrentDb.TableDefs("Anställda").Connect, 11)

'add "_COMP" to file name For Compacted File
stNewPath = Left(stPath, Len(stPath) - 4) & "_COMP.mdb"

'add "_BU" to file name for Back Up File
stBUPath = Left(stPath, Len(stPath) - 4) & "_BU.mdb"

'back up file in case of hardware failure while compacting
FileCopy stPath, stBUPath

'compact database into same directory with "_Comp" in file name
DBEngine.CompactDatabase stPath, stNewPath

'delete orig file (BE CAREFUL!!!!!)
Kill stPath

'rename compacted file as original file
Name stNewPath As stPath

'Done

'Notes: I usually don't like to give out code that includes the kill
statement as it's dangerous to delete files. Please manually backup your
data file and test this thoroughly before running the code. Also, make sure
you have enough disk space to copy the entire backend data file 3 times.
One for the existing file, two for the backup file and three for the
compacted file. THis will only work if no one has any linked tables open.
You can run it from the front end db as long as you don't have any linked
tables, queries, or bound forms open.

HTH,
Josh