How do I compact one closed DB from another DB using automation

[Phred Bear]

I want to compact a closed db from another db in vba but not using the
CompactDatabase dao command because you can only do this if you copy the
result to a new db, which I really do not want to do.

dbOne is the closed db and dbTwo is the controling db

Code in dbOne

Sub CompactTables()

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

End Sub


Code in dbTwo

Sub RunAuto(AutoSub As String)
Dim RemoteDB As Object
Set RemoteDB = GetObject("d:\Remote.mdb")
RemoteDB.Run AutoSub
RemoteDB.Quit
Set RemoteDB = Nothing
End Sub



Sub DoIt() ' the procedure attached to the button on the form to run
the sequence
'RunAuto ("RunEmptyTables")
'RunAuto ("CompactTables")
'RunAuto ("RunRepopulateTables")
End Sub


Many thanks

 

[Chris2]

I want to compact a closed db from another db in vba but not using the
CompactDatabase dao command because you can only do this if you copy the
result to a new db, which I really do not want to do.

dbOne is the closed db and dbTwo is the controling db

Many thanks

Phred Bear,

Isn't there a method of the DAO.DBEngine object, .CompactDatabase?


Public Sub CompactRemoteDB()
Dim dbe As DAO.DBEngine

Set dbe = DBEngine

dbe.CompactDatabase srcname:="c:\Northwind.mdb" _
, dstname:="c:\Northwind1.mdb"
Kill "c:\Northwind.mdb"
Name "c:\Northwind1.mdb" As "c:\Northwind.mdb"

End Sub


The strange part about this Method is that it creates a new file,
instead of operating on a single file.

The target DB must be closed, or an error will be generated.


Sincerely,

Chris O.

 

[Phred Bear]

<< Public Sub CompactRemoteDB()
Dim dbe As DAO.DBEngine
Set dbe = DBEngine
dbe.CompactDatabase srcname:="c:\Northwind.mdb" _
, dstname:="c:\Northwind1.mdb"
Kill "c:\Northwind.mdb"
Name "c:\Northwind1.mdb" As "c:\Northwind.mdb"
End Sub

The strange part about this Method is that it creates a new file,
instead of operating on a single file.

The target DB must be closed, or an error will be generated.>>

Ahaaaa,

I never thought of doing it that way,

Many thanks.

 

[Andi Mayer]

The strange part about this Method is that it creates a new file,
instead of operating on a single file.

this is not strange, imagine that the compact fails, you would be left
with a half compact (or worse damaged Database)

therfore you should check if the compact has run smootly

and whats happend if your newdb exists?

 

[Andi Mayer]

I thought it was strange because the Jet Compact utility (whatever it
does internally), does not do the same thing. It compacts a .mdb
without making me delete the original and renaming the new. Exactly
why .CompactDatabase doesn't do the same thing, that's what I'm not
sure of.

trust me it does the same, if it fails you have a db1.mdb in this
directory

 

[Chris2]

this is not strange, imagine that the compact fails, you would be left
with a half compact (or worse damaged Database)

therfore you should check if the compact has run smootly

and whats happend if your newdb exists?

to the first 10 lines in the message

MW

Andi Meyer,

I thought it was strange because the Jet Compact utility (whatever it
does internally), does not do the same thing. It compacts a .mdb
without making me delete the original and renaming the new. Exactly
why .CompactDatabase doesn't do the same thing, that's what I'm not
sure of.


As for error validation, the example was just meant to get things
going. Error validation, of course, needs to be added in.

Also, there are ways of constructing a temp db name at runtime that
are quite unlikely to run into a duplicate.



One could also use the Windows Script Host via VBA to run the Jet
Compact utility.


Sincerely,

Chris O.

 

[Phred Bear]

trust me it does the same, if it fails you have a db1.mdb in this
directory

So, what's the considered recommendation. Do I do it that way or not.

 

[Phred Bear]

<<I thought it was strange because the Jet Compact utility (whatever it
does internally), does not do the same thing. It compacts a .mdb
without making me delete the original and renaming the new. Exactly
why .CompactDatabase doesn't do the same thing, that's what I'm not
sure of.

trust me it does the same, if it fails you have a db1.mdb in this
directory>>

2nd thoughts, I didn't want to use CompactDatabase to start with for the
reasons you mention.

Would this work OK if I opened the target db in automation, emptied the
tables and closed the db but with it set to compact on closure. Then opened
it again with a different procedure and re-populated the tables then closed
it again.

Many thanks

 

[Andi Mayer]

Would this work OK if I opened the target db in automation, emptied the
tables and closed the db but with it set to compact on closure. Then opened
it again with a different procedure and re-populated the tables then closed
it again.

here you have the same problem:

what do do if the compact fails?

BTW: you can empty the tables, fill it again and make the compact- on-
close then

BTW2: I am writing at the moment on a class which is doing this
(besides other programms, I only have to fine tune it) including close
and reopen the database

 

[Phred Bear]

<< BTW: you can empty the tables, fill it again and make the compact- on-
close then

BTW2: I am writing at the moment on a class which is doing this
(besides other programms, I only have to fine tune it) including close
and reopen the database>>

BTW - I want to empty and compact before filling the tables so as to re-seed
the auto key to zero, so this wouldn't do.

I would be grateful to hear how you get on with your BTW2

Thanks,

Ian Millward
Edinburgh