Compact and repair Back End From Front End at Runtime

#1 (**permalink**) 28-Jul-2006, 08:03 AM

Hi Everybody

For Microsoft Access 2000 and above
Windows XP

I am trying to mimic the action of this command that at the moment runs
from the start>>all programs at the bottom left of the screen.

"C:\Program Files\Microsoft Office\ART\Office\MSACCESS.EXE" _
/excl /runtime /profile "MYDB" /wrkgrp _
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/9515-compact-repair-back-end-front-end.html
"C:\Program Files\Stortvalley\MYDB2\system.mdw" _
/repair "C:\Program Files\Stortvalley\MYDB2\MYDB2_Be.mdb" /compact

The idea is to be able to compact and repair the Backend database from
a command button on the Front End mde on a machine that has not got
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9515
full version Microsoft Access on it.....just the ART (Access Run Time)

Thanks for any help

Bob

#2 (**permalink**) 28-Jul-2006, 08:03 AM

Use the CompactDatabase method of the DBEngine.

What I typically do is rename the existing backend, putting in today's date
and changing the extension to .bak. I then compact that to the "correct"
database. That way, I have a backup as well.

Dim strCurrentBackend As String
Dim strRenamedBackend As String

strCurrentBackend = .....
strRenamedBackend = Left$(strCurrentBackend, Len(strCurrentBackend) - 4)
& _
Format$(Date(), "yyyymmdd") & ".bak"

Name strCurrentBackend As strRenamedBackend

DBEngine.CompactDatabase strRenamedBackend, strCurrentBackend

I've left out how to determine where the current backend is, but it's pretty
straightforward. One way is to check the Connect roperty of a linked table,
and strip off the ";DATABASE=" from the front of it:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9515

strCurrentBackend = Mid$(CurrentDb().TableDefs("MyLinkedTable").Connect,
11)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9515
(no private e-mails, please)

"bobdydd" wrote in message
news:1147433910.887517.32110@i39g2000cwa.googlegro ups.com...
> Hi Everybody
>
> For Microsoft Access 2000 and above
> Windows XP
>
> I am trying to mimic the action of this command that at the moment runs
> from the start>>all programs at the bottom left of the screen.
>
> "C:\Program Files\Microsoft Office\ART\Office\MSACCESS.EXE" _
> /excl /runtime /profile "MYDB" /wrkgrp _
> "C:\Program Files\Stortvalley\MYDB2\system.mdw" _
> /repair "C:\Program Files\Stortvalley\MYDB2\MYDB2_Be.mdb" /compact
>
> The idea is to be able to compact and repair the Backend database from
> a command button on the Front End mde on a machine that has not got
> full version Microsoft Access on it.....just the ART (Access Run Time)
>
> Thanks for any help
>
> Bob
>

#3 (**permalink**) 28-Jul-2006, 08:03 AM

Hi Doug

As always you get me out of trouble ...................Thanks
I have not tried it on a machine without Full version Access installed
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=9515
(just the ART) but I reckon it will work.
I altered it slightly to put the Backups it a different directory

Once again Thanks a lot

Bob