How to compact current database (1 Viewer)

[Jacino]

Hi everybody,

How could I compact the current database from code within an Access 2000 database. The purpose is to import a large text file, and then the code has to directly compact the current database in order to take the major changes into account.

Any ideas.

Thanks in advance

 

[simongallop]

In code you cannot compact the database that you are in (or at least that was the rule in 97) This means that the code needs to sit in another databse. The code needed is:

Function Compact_DB()
'SET PATH
Path = "C:\MyFiles\dev\"

'COMPACT CHOSEN DATABASE, TO TEMPORARY DATABASE NAME
DBEngine.CompactDatabase Path & "MyDatabase.mdb", Path & "Spare1.mdb"

'DELETE OLD DATABASE
Kill Path & "MyDatabase.mdb"

'RENAME TEMPORARY DATABASE TO ORIGINAL NAME
Name Path & "Spare1.mdb" As Path & "MyDatabase.mdb"

End Function


HTH

 

[VBAhole22]

I have had some luck with the following code.

Private Sub compact_Click()
SendKeys "{f10}"
SendKeys "TDC"
End Sub

I think it compacts the database from which it was called.

 

[Cosmos75]

Great Idea!

Cool!! Will try this one out!

 

[VBAhole22]

If it works, I'd really like to know why!

 

[simongallop]

It only works when launched from a form rather than a module as {F10} calls up the toolbar (the same as pressing the ALT key) and TDC is Tools, Database utilites, Compact database. Whereas in a module the compact database option is not part of the toolbar.

Thanks for the code!

 

[Rich]

SendKeys "%(TDC)", False
works fine from a form

 

[antomack]

Try the following link, the downloadable file is very useful in compacting the open database.

http://www.mvps.org/access/modules/mdl0030.htm

 

[John.Woody]

I always find sendkeys a pain, it never seems very stable and confuses my computer as to whether CAPS Lock is on or off!!

With Access 2K you can set the db to compact on exit, you could close and re-open the db after importing your data.

HTH
John

 

[Rich]

I've never had a problem with this one, others yes and like yourself I scrapped them, but for unreliability other than any other reason