display warning message

[Guest]

I have a macro in an Excel workbook that imports data into an Access
database. Once the data is imported, the following sub is automatically run
to compact the database for the user.

Several warning messages are displayed as the databases compacts. The
problem is, depending on the number of applications the user has open, the
warning messages do not always display. After a while, another message,
"waiting for OLE automation" error is displayed because the user hasn't
clicked "open" on the warning message.

The reason for visible = false is that we don't want the user to see the
screen change. We want the database compact to be virtually invisible to the
user.

Is there a way to ensure the original warning message will always be
displayed on top of any applications that may be opened? Thanks for the
help........

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Sub CompactDB()

vDB = ActiveWorkbook.Sheets("Information").Range("C5")

Set AppAcc = New Access.Application
AppAcc.Visible = False
AppAcc.OpenCurrentDatabase vDB

DoCmd.SetWarnings False

AppAcc.CommandBars("Menu Bar").Controls("Tools").Controls("Database
utilities"). _Controls("Compact and repair database...").accDoDefaultAction

DoCmd.SetWarnings True
AppAcc.Visible = True
AppAcc.Quit acQuitSaveNone

Set AppAcc = Nothing

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[Bill Manville]

DoCmd.SetWarnings False

I would think it should be AppAcc.DoCmd.SetWarnings False

Alternatively you could use the CompactDatabase method of the DBEngine
object in the DAO library (which admittedly is less convenient as you
have to compact to a different file, then Kill the original file and
rename the new one).

Bill Manville
MVP - Microsoft Excel, Oxford, England