Prev: Adding a tax % to currency value from another textbox in form.
Next: お世話になりました
From: teddysnips on 2 Jul 2008 08:15
My client has asked if it's possible to Compact and Repair his Front
End database programmatically. I remember doing this from a VB
application about 10 years ago, so I wondered if it was possible in
Access.

The application is Access 2003 in 2002-2003 File Format.

I've added the following function to the Front End in a global module:

Public Function CandRDB()

CommandBars("Menu Bar"). _
Controls("Tools"). _
Controls("Database utilities"). _
Controls("Compact and Repair Database..."). _
accDoDefaultAction

End Function

and created an AutoExec macro that only calls this function. It
appears to be working (something happens, there's an hourglass cursor
for example) but then barfs with the following:

"-2147467259 Method 'accDoDefaultAction' of object
'_CommandBarButton' failed"

From what I've read, this can happen if the Menu Bar is not visible,
but it is.

Any thoughts? I know that the database can be set to compact on exit,
but that's not what the client has asked for.

Thanks

Edward
From: Stuart McCall on 2 Jul 2008 09:07
<teddysnips(a)hotmail.com> wrote in message
news:3f682b03-63d9-49a5-a95f-093f742b8074(a)k37g2000hsf.googlegroups.com...
> My client has asked if it's possible to Compact and Repair his Front
> End database programmatically. I remember doing this from a VB
> application about 10 years ago, so I wondered if it was possible in
> Access.
>
> The application is Access 2003 in 2002-2003 File Format.
>
> I've added the following function to the Front End in a global module:
>
> Public Function CandRDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and Repair Database..."). _
> accDoDefaultAction
>
> End Function
>
> and created an AutoExec macro that only calls this function. It
> appears to be working (something happens, there's an hourglass cursor
> for example) but then barfs with the following:
>
> "-2147467259 Method 'accDoDefaultAction' of object
> '_CommandBarButton' failed"
>
> From what I've read, this can happen if the Menu Bar is not visible,
> but it is.
>
> Any thoughts? I know that the database can be set to compact on exit,
> but that's not what the client has asked for.
>
> Thanks
>
> Edward

You need to perform the compact from a separate database, otherwise you're
attempting to compact a file with running code, which obviously cannot be
allowed.

Create a new database file and create a public function that reads:

SourceFile = "c:\PathToExistingDatabase\MyFile.mdb"
OutputFile = "c:\PathToExistingDatabase\MyFile1.mdb"
Application.CompactRepair SourceFile, OutputFile

Kill SourceFile
Name OutputFile As SourceFile

With Application
.FollowHyperlink SourceFile
.Quit
End With

That will compact your app, then run the app and shut itself down.



From: a a r o n _ k e m p f on 2 Jul 2008 10:09
if your database is too stupid to be stable-- then move to something
that is.

SQL Server works perfect for me-- I never have to compact and repair.

I believe that 'Auto-Shrink' is enabled by default on smaller editions
of SQL Server

-Aaron

On Jul 2, 5:15 am, teddysn...(a)hotmail.com wrote:
> My client has asked if it's possible to Compact and Repair his Front
> End database programmatically.  I remember doing this from a VB
> application about 10 years ago, so I wondered if it was possible in
> Access.
>
> The application is Access 2003 in 2002-2003 File Format.
>
> I've added the following function to the Front End in a global module:
>
> Public Function CandRDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and Repair Database..."). _
> accDoDefaultAction
>
> End Function
>
> and created an AutoExec macro that only calls this function.  It
> appears to be working (something happens, there's an hourglass cursor
> for example) but then barfs with the following:
>
> "-2147467259  Method 'accDoDefaultAction' of object
> '_CommandBarButton' failed"
>
> From what I've read, this can happen if the Menu Bar is not visible,
> but it is.
>
> Any thoughts?  I know that the database can be set to compact on exit,
> but that's not what the client has asked for.
>
> Thanks
>
> Edward

From: Michiel Rapati-Kekkonen on 2 Jul 2008 10:26
Ask him what is the difference between on Close and on Start-up?
Nothing can happen in between...
And no code required.


Michiel


<teddysnips(a)hotmail.com> wrote in message
news:3f682b03-63d9-49a5-a95f-093f742b8074(a)k37g2000hsf.googlegroups.com...
> My client has asked if it's possible to Compact and Repair his Front
> End database programmatically. I remember doing this from a VB
> application about 10 years ago, so I wondered if it was possible in
> Access.
>
> The application is Access 2003 in 2002-2003 File Format.
>
> I've added the following function to the Front End in a global module:
>
> Public Function CandRDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and Repair Database..."). _
> accDoDefaultAction
>
> End Function
>
> and created an AutoExec macro that only calls this function. It
> appears to be working (something happens, there's an hourglass cursor
> for example) but then barfs with the following:
>
> "-2147467259 Method 'accDoDefaultAction' of object
> '_CommandBarButton' failed"
>
> From what I've read, this can happen if the Menu Bar is not visible,
> but it is.
>
> Any thoughts? I know that the database can be set to compact on exit,
> but that's not what the client has asked for.
>
> Thanks
>
> Edward
>

From: Klatuu on 2 Jul 2008 10:49
To do it autmatically, Use the Compact On Close option from Tools, Options,
General tab. I don't recommend it, but it is the best automatic options.

BTW, ignore Aaron Kempf. He is a known Troll who will post false
information and denegrate Access at every opportunity for no apparent reason.
--
Dave Hargis, Microsoft Access MVP


"teddysnips(a)hotmail.com" wrote:

> My client has asked if it's possible to Compact and Repair his Front
> End database programmatically. I remember doing this from a VB
> application about 10 years ago, so I wondered if it was possible in
> Access.
>
> The application is Access 2003 in 2002-2003 File Format.
>
> I've added the following function to the Front End in a global module:
>
> Public Function CandRDB()
>
> CommandBars("Menu Bar"). _
> Controls("Tools"). _
> Controls("Database utilities"). _
> Controls("Compact and Repair Database..."). _
> accDoDefaultAction
>
> End Function
>
> and created an AutoExec macro that only calls this function. It
> appears to be working (something happens, there's an hourglass cursor
> for example) but then barfs with the following:
>
> "-2147467259 Method 'accDoDefaultAction' of object
> '_CommandBarButton' failed"
>
> From what I've read, this can happen if the Menu Bar is not visible,
> but it is.
>
> Any thoughts? I know that the database can be set to compact on exit,
> but that's not what the client has asked for.
>
> Thanks
>
> Edward
>
 |  Next  |  Last
Pages: 1 2
Prev: Adding a tax % to currency value from another textbox in form.
Next: お世話になりました