compact access 2007 database

Question

Does anyone knows how to compact an access 2007 database with .net?

The previous code with JRO.JetEngine is not working for 2007 format. We used that code for previouse db but not working for the latest one.

Answer 1

I haven't tried using JRO to do this myself, but does your connection string specify the new database engine for Access 2007 (not Jet OLEDB)?

Answer 2

hi,

yes, it does. This is the connection string we use:

sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        sSource & ";User IDPassword=test;"

we use Microsoft.ACE.OLEDB.12.0 for connecting to .accdb

Answer 3

What about the Microsoft Access 2007 Database Engine objects?

Answer 4

Does anyone knows how to compact an access 2007 database with .net?

Answer 5

I believe that you have to use the Access 2007 Application object:

How to: Compact and Repair a Database

 

 

Answer 6

Paul P Clement IV

 

Thanks for the post and yes that will work. However if i distribute my app over the internet the above method will only work if people have Access 2007 installed locally on there computers right? If this is the case it will not work for me i need a way to compact and access 2007 *.accdb file completely independent of what the user has loaded on their system. I am writing it in VS team system 2008 in VB.NET. 

Answer 7

Unfortunately the only method I'm aware of, which will compact an Access 2007 database through code, requires use of the Access Application object model.

 

Answer 8

I also have an application that needs to CompactDatabase and run on any machine regardless of their Office version (if any).

 

Can the files be distibuted to users (MSACC.olb and ACEDAO.dll) with my application?

 

Thanks,

 

Ron

Answer 9

I don't think ACEDAO.DLL will help you since CompactDatabase doesn't appear to support 2007 file format. And no you cannot legally distribute Microsoft Access application files. You're limited to installing the Access Database Engine package. 

 

Answer 10

 

I'm using JRO with Access 2007 without issue.

 

 

Dim JROEng As Object

JROEng = CreateObject("JRO.JetEngine")

JROEng.CompactDatabase("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalFile & ";Jet OLEDB:Engine Type=5", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalOutFile & ";Jet OLEDB:Engine Type=5")

 

Wth sLocalFile being the absolute path and filename to the db to compact, and sLocalOutfile being the same for the desired resulting compacted database.

 

Answer 11

I think the OP was looking to compact an Access 2007 database and not a 2002-2003 database (which is what your code does).

 

Answer 12

 

My code also compacts the Microsoft Access 2007 database im currently working with - from 70Mb to under 3Mb - which is useful for me, and is the reason I posted that I was successfully doing so with Access 2007.

 

Answer 13

Using JRO? Can you post that code so we can see how you're doing it?

 

Answer 14

Yes of course Paul,

 

This is a function from my AccessHelper class that I use with the Access2007 database that ships with another app I wrote for a client. It's not complete in terms of rollback, and error handling, but it compacts the database without issue.  The JRO specific code is identical to that which I posted previously.

Code Snippet

 

Public Function CompactDb(ByVal sDB As String) As Long

Try

'SET THE FILE WE ARE GOING TO STORE AND COMPACT LOCALLY

Dim sLocalFile As String = System.AppDomain.CurrentDomain.BaseDirectory().ToString & ParsePath(sDB, vbNormal, False)

'THEN BUILD AN OUTPUT FILE STRING

Dim sLocalOutFile As String = System.AppDomain.CurrentDomain.BaseDirectory().ToString & "NAMEHERE_Compacted.accdb"

'COPY THE DATABASE TO THE APP FOLDER - WE DONT WANT TO COMPACT OVER THE NETWORK

RaiseEvent CopyStart("Backing Up " & sDB)

Application.DoEvents()

CopyFileEx(sDB, Replace(sDB, "accdb", "bak"), cpr, 0, 0, 0)

Application.DoEvents()

RaiseEvent CopyEnd("Backup Successful")

Application.DoEvents()

RaiseEvent CopyStart("Copying " & sDB & " to " & sLocalFile)

CopyFileEx(sDB, sLocalFile, cpr, 0, 0, 0)

RaiseEvent CopyEnd("Copy Successful")

'THEN COMPACT THE DB

If File.Exists(sLocalOutFile) Then File.Delete(sLocalOutFile)

Dim JROEng As Object

JROEng = CreateObject("JRO.JetEngine")

RaiseEvent CompactStart("Compacting " & sLocalFile)

Application.DoEvents()

JROEng.CompactDatabase("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalFile & ";Jet OLEDB:Engine Type=5", "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalOutFile & ";Jet OLEDB:Engine Type=5")

RaiseEvent CompactEnd("Compact Successful")

Application.DoEvents()

If DBOK(sLocalOutFile) Then

'NOW TRY AND CONNECT TO IT - IF WE CAN

'THEN PUT IT BACK

Application.DoEvents()

RaiseEvent CopyStart("Replacing " & sDB & " with compacted version")

CopyFileEx(sLocalOutFile, sDB, cpr, 0, 0, 0)

Application.DoEvents()

RaiseEvent CopyEnd("Replace Successful")

File.Delete(sLocalFile)

File.Delete(sLocalOutFile)

Return 0

Else

File.Delete(sDB)

Application.DoEvents()

Rename(Replace(sDB, "accdb", "bak"), sDB)

RaiseEvent RollBack("Compact Failure - Database rolled back")

Application.DoEvents()

Return -1

End If

Catch ex As Exception

File.Delete(sDB)

Application.DoEvents()

Rename(Replace(sDB, "accdb", "bak"), sDB)

Application.DoEvents()

RaiseEvent RollBack("Compact Failure - Database rolled back")

Return -1

End Try

End Function

 

 

 

 

Answer 15

Thanks for posting your code. From what I can determine the JRO code you have compacts to an Access 2002-2003 database and not an Access 2007 database. That would be consistent with the connection string you are using which specifies Jet OLEDB version 5.0 (a Jet version 4.0 database).

 

Answer 16

You're right - i'll be binning that code asap.

Answer 17

Crispinw,

 

THANK YOU!!!! I've been pounding my head against a brick wall all day over this. After reading your post, it took me five minutes to get my app fully functioning.

Seriously, how on earth did you figure out what format the strings needed to be for the CompactDatabase method? Is that documented somewhere.

 

Thanks again. I greatly appreciate your sharing this tidbit of knowledge with the rest of us! 

Answer 18

OK, I'm confused whether this resolved.  My DB is a Access 2003 MDB file that I upgraded to Access 2007 ACCDB file.  The application within works fine, but the VB.NET application I wrote for the MDB file has stopped working.  I am using the following connection string:

Here is what I am using to connect:

Dim jroDB As JRO.JetEngine = Nothing

sJroSrc = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\Summit615.accdb";Jet OLEDB:Engine Type=5;Persist Security Info=False;"
sJroDst = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\Summit615_01.accdb";Jet OLEDB:Engine Type=5;Persist Security Info=False;"

jroDB = New JRO.JetEngine
jroDB.CompactDatabase(sJroSrc, sJroDst)
System.Runtime.InteropServices.Marshal.ReleaseComObject(jroDB)
jroDB = Nothing

Here's the error:

ErrorCode:    -2147217887 [0x80040E21]
Message: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

I have also used it without the "Jet OLEDB:Engine Type=5;" parameter, as well as with "Jet OLEDB:Engine Type=6;", with the same error.  Any insight into this would be greatly appreciated, as I have not seen a solution to this ... yet!

Thanks in advance for your time ... Sunny

Answer 19

The JRO CompactDatabase method cannot compact to an Access 2007 file (ACCDB). It can only compact to a 2003 (2000) or earlier database.

 

The only way I am aware of to compact to a 2007 version database is to use Access 2007.

Answer 20

Yikes, there's gotta be a way to do this programmatically from another application.  From Access Help, this can be done using the following command line.  I can probably use this,but it seems rather inelegant!

"C:\My Folder\My Database.accdb" /compact

I have written an easy to use app that can run through an interface or a command line (including through Task Scheduler) to make backups and zips of DBs, so they are always in tiptop shape.  It works well with Access 2003 and earlier, but I'm stumped here.  I'll see if i can integrate this, as I would like to upgrade my DB to Access 2007 and inherit all its bugs!  Just kidding.

Thanks for your response and help ... Sunny

Answer 21

OK, I did a little experimenting and came up with the following results.  My VB.NET RC app automatically determines the DBType of the DB being RC'ed, so it can use it in the RC method call.  I do this using the following:

Dim adoDB As ADODB.Connection
Dim sConn as String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB.accdb";Jet OLEDB:Engine Type=5;"
Dim sProvider as String = "Microsoft.ACE.OLEDB.12.0"     ' Would not work without this; got some sort funky mulit-level OLE DB error!

adoDB = New ADODB.Connection
adoDB.Provider = sProvider
adoDB.Open(sConn)
nDBType = CInt(adoDB.Properties.Item("Jet OLEDB:Engine Type").Value)
adoDB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(adoDB)
adoDB = Nothing

On an Access 2007 DB, it returns, DBType=6, and querying the DB using this is not a problem, but repairing the DB using this value is a problem.  I had to use DBType=5, after which that same repaired DB returns DBType=5 on consequent queries.

Subsequently, I used the following:

Dim jroDB As JRO.JetEngine = Nothing
Dim SrcFilename as String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB.accdb";Jet OLEDB:Engine Type=5;"
Dim DstFilename as String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB_01.accdb";Jet OLEDB:Engine Type=5;"

jroDB = New JRO.JetEngine
jroDB.CompactDatabase(SrcFilename , DstFilename)
System.Runtime.InteropServices.Marshal.ReleaseComObject(jroDB)
jroDB = Nothing

and it worked!  To repair a DB with a DB password assigned, you can use the following connection strings:

sConn ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB.accdb";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Password="access";"
sProvider = "Microsoft.ACE.OLEDB.12.0"

and

SrcFilename = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB.accdb";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Password="access";"
DstFilename = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="C:\My Documents\My DB_01.accdb";Jet OLEDB:Engine Type=5;Jet OLEDBatabase Password="access";"

Note: Ignore the smiley's above, if they appear.  Should say "Jet OLEDB : Database Password" without the spaces between the OLEDB : and Database in the lines above.  Also, I apologize if there are mistakes, as my code was a little more involved and so I edited by hand!

Personally, I think this is some sort of a bug!  Maybe, one of the MVPs can answer this.

Thanks for your time & help ... Sunny

Answer 22

That looks like the same code that Crispinw posted a while back. From what I was able to determine the resulting file is in Access 2002-2003 format.

Answer 23

You are correct.  I opened the DB and it said Access 2003.  I have reverted back to your suggestion of using the MSACCESS.EXE command line with /compact.  Do you have any ideas how to pass a DB password if the DB is encrypted? The help does not have anything in it for Access 2007 (/pwd and /user are only supposed to be used for Access 2003).  So, for a DB that has been encrypted, if I do the following:

...\msaccess.exe "...\MyDB.accdb" /compact "...\MyDB_1.accdb"  --> Prompts for just password without a username.
...\msaccess.exe "...\MyDB.accdb" /compact "...\MyDB_1.accdb" /pwd access123  --> Prompts for username/password with current Windows username as default.
...\msaccess.exe "...\MyDB.accdb" /compact "...\MyDB_1.accdb" /user admin /pwd access123  --> Prompts for username/password with admin as default username.

Any ideas would be appreicated.

Also, is there a way to prevent the MS Access splash screen from apprearing?

Thanks for your time ... Sunny

Answer 24

After looking through this thread, none of the information seems to completely answer the question. It appears to me that the real question was how to Use .net to compact an Access 2007 Database on computers without Access 2007 installed.

To do this you will need to use the Office 2007 Primary Interop Assembly package (http://www.microsoft.com/downloads/details.aspx?familyid=59DAEBAA-BED4-4282-A28C-B864D8BFA513&displaylang=en). And- make a reference to Microsoft.Office.Interop.Access.Dao.

It's simple, using vb.net (Database must be closed!):

Private Sub CompactDB()

Dim dbEngine As New Microsoft.Office.Interop.Access.Dao.DBEngine

Dim tmpPath As String = Application.CommonAppDataPath & "\TempDB.accdb"

Dim dbPath As String = Application.StartupPath & "\Notes.accdb"

Try 'Just in case...

If IO.File.Exists(tmpPath) Then IO.File.Delete(tmpPath)

IO.File.Move(dbPath, tmpPath)

dbEngine.CompactDatabase(tmpPath, dbPath)

Catch

MsgBox(Err.Description, MsgBoxStyle.Exclamation, Err.Number & "-Unable to compact database.")

End Try

End Sub

 Hope that helps someone.

Answer 25

Using Office Primary Interop actually requires installing Office. It does nor require full version, but it automates office in background.

---

Val Mazur (MVP) http://www.xporttools.net

Answer 26

Actually the PIA isn't required, but as Val mentioned Access must be installed. If you add a project reference to Microsoft Access a generic interop assembly is created and you can use that as well.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 27

Does anybody know, how to do this in Visual Basic 6 ?

The following code does not work.

Dim JROEng As Object

JROEng = CreateObject( "JRO.JetEngine" )

JROEng.CompactDatabase( "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalFile & ";Jet OLEDB:Engine Type=5" , "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sLocalOutFile & ";Jet OLEDB:Engine Type=5" )

The output should have the Access2007 format.

Answer 28

It's in the prior link I posted. Microsoft Access is required:

http://msdn.microsoft.com/en-us/library/bb258198.aspx

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 29

Thanks,

but how/where can i add a password ?

Answer 30

If you are referring to a database password (as opposed to user level passwords) you would have to use DAO. CompactRepair does not support this functionality.

http://www.freevbcode.com/ShowCode.Asp?ID=354

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 31

I want to compact a password protected database, which was created in Access2007. (*.accdb)

Answer 32

I've got it...

Install "Microsoft Office Access database Engine 2007" (if required)

Set reference to "Microsoft Office 12.0 Access database Engine".
Now you can use the following code to compress your ACCDB File.

Public Sub TestCompact()
    Dim acc As New DAO.DBEngine
   
    acc.CompactDatabase "C:\Source.accdb", "C:\Destination.accdb", , , "pwd=..."
   
End Sub

Answer 33

You are right Philipp B., the access database 2007 can be compacted by the above said method, It is just that you have to add ";" before the pwd.
Refer to the following link:- http://msdn.microsoft.com/en-us/library/bb220986.aspx

Refer http://techieyogi.blogspot.com/2009/11/how-to-compact-access-2007-database.html for detailed solution.