Compacting a ms access 2007 database by vba code

[odrap]

On a site i found the following vba code to compact a ms access database .
This code is written for Access 97 = 4 and Access 2000 = 5 .
My question now: is it possible to use the same code for compacting a ms access 2007 database and what number is to be used as enginenumber?
Public Sub CompactDB()
'Microsoft Jet and Replication objects
Dim objJE As New JRO.JetEngine, strSource As String, strTarget As String
DoEvents
Busy True
strSource = " "
strTarget = " "
objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=4;"
Busy False
'Engine type:
'Access 97 = 4
'Access 2000 = 5
End Sub

 

[Uncle Gizmo]

>>>use the same code for compacting a ms access 2007<<<

Doubtful because MS access 2007 uses a different database engine. (Not jet)

 

[boblarson]

>>>use the same code for compacting a ms access 2007<<<

Doubtful because MS access 2007 uses a different database engine. (Not jet)

Actually, that's not really true. It uses an "updated" version of the Jet Engine but has newer parts that are specific to Access, now that the Access team is working on it directly. Most DAO code is backwards compatible.

Could also try this code to see if it works. I haven't tried it with 2007 yet.

Dim strDbNameOld As String
Dim strDBNameNew As String

strDbNameOld = "YourPathAndNameToTheFileYouWantToCompact"

        DBEngine.CompactDatabase strDBNameOld, strDBNameNew
        Kill strDBNameOld
        Name strDBNameNew As strDBNameOld

 

[Uncle Gizmo]

>>> Not really true<<<
I see what you mean, I read the following which is a little bit misleading:

To support these new features and, of course, integration with SharePoint, two major changes have taken place in Access 2007—a new data engine (ACE) and extensions to Data Access Objects (DAO) have been added.

Found in this downloadable ebook here: http://books.google.co.uk/books?id=61Ps8-AU12IC&pg=PA10&lpg=PA10&dq="new+data+engine+(ACE)"&source=bl&ots=gTAcxgQOPd&sig=S_DsMgQozKYgdjvfDOw_w8T0jR4&hl=en&ei=6UzVSbjAB6SUjAf_quSNDw&sa=X&oi=book_result&ct=result&resnum=1#PPA9,M1

On further reading I also find this paragraph in the book:
The biggest changes in Access 2007 are those that have been made to the JET database engine, which has been referred to as ACE during the beta process by Microsoft. The new engine is a private copy of JET used by the Office 2007 team and has been refined and added to for Access 2007.

 

[odrap]

Thanks for the help

 

[dfenton]

The code you posted would not have run reliably in Access 97, as JRO postdates the release of A97, and would not necessarily be installed on a machine with A97 unless that machine had a later version of Access installed.

Secondly, there is truly no valid reason for JRO to exist. It is one of the ugly step-children of Microsoft's misguided promotion of ADO over DAO for use in Access. ADO lacked support for certain features that were part of the Jet database engine, and JRO was created to fix that lack (that should have clued in somebody at MS, but it apparently didn't). But it's woefully underfeatured, offering nothing at all that DAO doesn't already offer (except for one thing, i.e., the ability to initiate an indirect synch in code in a replicated environment).

DAO is the only library you should ever use for compacting, and the code that works in A97 will work in all versions of Access since then, *including* Access 2007.

 

[Uncle Gizmo]

Nice website David...

From halfway down this page makes particularly interesting reading!

Thanks for the info.