VBA for a "Please Wait..." message while db C + R on close

[nick404]

My current database C&R on close and takes a bit since it is a large db. Whenever the user closes the database I would like a simple "Please wait... the database is performing closing operations" message to pop up while the database performs the C + R. I also need it to run a quick append query as well. I have tried using a form that on open runs the query and then runs .closedatabase, but this did not work. The form flashes open for a second and then the close events occur.

I also tried a message box, but I can't figure out how to get the message box to remain open while the compact and repair is occurring, I have to click OK first..

Any suggestions appreciated.

[ranman256]

you cant use message box,
use a form, with ONLY a big label on it with your message.
when the db closes, so will the form.

[nick404]

When i tried to use a form the form opened and closed immediately (too fast to decipher any text) and then the compact and repair began

[ItsMe]

I guess you could create a separate app that executes a CandR on the original app. Your new app could check for an mdb file in the directory. Although, I am having a hard time imagining a need for a User to be aware of a CandR taking place. Why not automate on the weekend on something?

[ssanfu]

Well, first off, a C & R CANNOT have ANY form, message box, dialog box....... any object open, Think about it....how would you compact if an object was open???

Second, doing a C & R every time the dB is closed can result in a corrupted dB. It happens rarely nowadays, but in the days prior to A2000, C & R on close was not recommended.

Is you dB split?

Out of curiosity, why are you doing a C & R every time you close the dB?

[nick404]

Well, first off, a C & R CANNOT have ANY form, message box, dialog box....... any object open, Think about it....how would you compact if an object was open???

Makes sense now that I think about it.

Yes split database.

I will be leaving for school next week, and the place of business I am currently employed will be using the database frequently. Because of this I wanted the compact/repair to be automated so the db wouldn't bloat exponentially between now and winter break when I am back.

Why not automate on the weekend on something?

As ItsMe stated this would be ideal. Thank you guys!