PercyN
Programmer
- May 16, 2007
- 86
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:
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:
Code:
'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)