Rebuilding/Compacting Access Database in Code

Hallo

I want to activate the rebuilding and compacting of my database after a certain period elapsed. The problem starts with the Compact-method. I have to specify a new db name (the compacted one), therefore I have to delete the old one, rename the new one so that it has the same name as the old db. For some reason the DELETE (of file) and RENAME methods are not accepted at Design-time. Is there some other way of doing this?

I need help!

Thank you

[Mark Sreeves]

shouldn't be a problem

Are you closing the database before trying to delete it?

When I compile/Run code, the methods are not recognized by VB. So I did not even get to the point where I might get an error that database is open. Do you maybe know why?

Thanx

[Mark Sreeves]

Lets have a look at your code

[BruceG]

Try something like the following:

Private Sub CompactTheDB()

    Dim strOldDBName As String
    Dim strNewDBName As String

    strOldDBName = "C:\Whatever\TheDBName.mdb"
    strNewDBName = "C:\Whatever\TheDBName.bkp"

    DBEngine.CompactDatabase strOldDBName, strNewDBName
    Kill strOldDBName
    Name strNewDBName As strOldDBName

End Sub

[PetergUK]

Hi Guys

First, i should explain i am new to VB programming although i have been building Access97 apps. for about two years now.

I have a module in an otherwise empty database that compacts and backs up another database everynight which is the one all the guys in my office log into each day.

I'd like to build it in VB, and i'm doing ok in terms of the databse name, location etc, but here is my problem:

Would i have to open some instance of Access to use DBEngine.CompactDatabase, or will it run from within VB as it is?

Sorry if this is a basic question, but we all have to start somewhere.....

I only ask as i have to do a similar thing when creating Word docs. for mail merge from within Access.

Cheers

Peter Gidden

[BruceG]

Note: The info below assumes that DAO is being used.

PeterGUK:
The answer to your question is no, you do not have to open an instance of Access to compact the DB from VB. In your VB project, go to the Project menu, then References, and check "Microsoft DAO 3.6 Object Library" (or DAO 3.51, or whatever latest version shows up there). Once this reference is set, you can access any object of the DBEngine. Thus, the code I posted earlier should work for you.

hermanvd:
I got your e-mail regarding your use of the ADODC, which I'm sorry to say I have not used, except for once doing a tutorial. I'm afraid I can't help you with that one.

[PetergUK]

Thanks Bruce, i'm off to give it a go.....