Compact a password protected backend

[PercyN]

Hi,
I'm trying to provide a code for compacting by BE. So the idea is to doe the following:
1. make a copy of the current DB just as a dummy
2. connect to the dummy in order to free the current DB
3. Compact the now freed current DB
4. Then reconnect back to the now compacted DB and then kill the dummy one.

Theoretically this seems all logical but for some reason after I create the dummy and connect to it the current DB is still not free (the .laccdb file still exits hence the compact is done not work). Even when I put a break just before compacting and go to the folder to try deleting the .laccdb file, it says file is in use which is where I'm lost.

Could someone please tell me what I'm doing wrong. I have tried searching the forums but nothing seems to quite answer what I need. The code is below:

'The idea is to create a temp DB and connect to it so as to free the current DB

'First Disconect from current DB
Call DeleteTableLinks

'then make a copy of the current DB
Dim FSO As Scripting.FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile currentDB, tempDB

'then connect to it
Call RemakeTableLinks(tempDB)

'this should free the current DB for compact
DBEngine.CompactDatabase currentDB, newDB, , , ";pwd=" & Password

Do While FileExists(newDB) = False
'Keep waiting
Loop

Kill (currentDB)
FileCopy newDB, currentDB

Do While FileExists(currentDB) = False
'Keep waiting
Loop

Kill (newDB)
Kill (tempDB)

'Connnect back to DB
Call RemakeTableLinks(currentDB)

 

[lameid]

CurrentDB is a function that returns the open Access file (FrontEnd not BackEnd) and is not even a path to a database file but a database object.... Unless, I guess you could not have a reference to the DAO library and your own function for currentDb or it could be a variable. Also you don't need to relink to compact... Just ensure all the data sources are closed.

If that does not help, please consider posting the entire function / variable declarations and values so we can spot the issue.

 

[PercyN]

Thanks lameid.
The currentDB variable is a declaration in my function. It is not referring to the in-build CurruntDB function. Or did you mean because there is already an in-build function called CurrentDB, me declaring in in my function, causes a conflict?

See my full code below:

Public Function MyCompacter(currentDB As String, newDB, tempDB As String, Password As String)
'On Error GoTo Doc

'The idea is to create a temp DB and connect to it so as to free the current DB

'First Disconect from current DB
Call DeleteTableLinks

'then make a copy of the current DB
Dim FSO As Scripting.FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile currentDB, tempDB

'then connect to it
Call RemakeTableLinks(tempDB)

'this should free the current DB for compact
DBEngine.CompactDatabase currentDB, newDB, , , ";pwd=" & Password

Do While FileExists(newDB) = False
'Keep waiting
Loop

Kill (currentDB)
FileCopy newDB, currentDB

Do While FileExists(currentDB) = False
'Keep waiting
Loop

Kill (newDB)
Kill (tempDB)

'Connnect back to DB
Call RemakeTableLinks(currentDB)

AllOk = "Yes"


Exiter:
    Exit Function

Doc:
MsgBox Err.Description, vbExclamation, "Error: " & Err.Number

End Function

 

[lameid]

I don't know if your getting a coflict over currentdb or not... but I would replace currentDB with something like existingBEPath to be sure. I'd set a breakpoint where you use it and see what the value returned is. VBA is forgivng sometimes and overlooks things like missing parenthesis... I use stuff like currentdb.queryDefs("QueryName").SQL in the immediate window or if I am only using one obect in the hierarchy in my code.

And I still think connecting to a dummy database is a wasted effort... certainly back it up but you should be out of the file before copying for that purpose to avoid a corrupt backup.

 

[PercyN]

Hi lameid,
I tried that but I still get that error "the database is open by another user..."
And it happens right when it tries to execute the actual compact which is what baffles me because I would have thought that by creating a new DB and connecting to it, the existing DB should now be free and can be compacted. When I pause it just before the code below, I can see my tables are now linked to the new DB so why the existing one still remains 'occupied' is where I'm lost.

 DBEngine.CompactDatabase existingDBPath, newDB, , , ";pwd=" & Password

 

[lameid]

You might try making a file that has a startup form with an ontimer event to wait 10 seconds that does the compact, open it and then close the FE application... The compacting file could even reopen the FE when it is done. Just don't forget to close the compacting application.