Compacting Access Database

Hi,

I have an application that uses an access database on the client side, I am trying to compact the database but always get errors.

My Code is :

  Try
                objJRO = New JRO.JetEngine
                strDBPath = Me.strDatabasePath
                strSysDBPath = Me.strSystemDBPath
                strConnectString = Me.cnLocalWrite.ConnectionString

Do Until (Me.cnLocal.State = ConnectionState.Closed And Me.cnLocalWrite.State = ConnectionState.Closed) Or (intLoop >= intMaxTries)
                    Me.cnLocal.Close()
                    Me.cnLocalWrite.Close()
                    Application.DoEvents()
                    intLoop += 1
                Loop

                If intLoop >= intMaxTries Then
                    MessageBox.Show("The connection to the database could not be closed, so compaction will not take place.  Please try again later.", "Compact Database", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                    Exit Sub
                End If

                strTempFile = System.IO.Path.GetTempFileName
                System.IO.File.Delete(strTempFile)
                strConnectString = strConnectString.Replace("DataEntryName", "AdminName")
                strConnectString = String.Format("{0};Password={1}", strConnectString, Utils.DecryptData("blaablaa"))
                strTempConnectionString = strConnectString.Replace(strDBPath, strTempFile)
                objJRO.CompactDatabase(strConnectString, strTempConnectionString)
                Try
                    System.IO.File.Delete(strDBPath & ".bak")
                Catch exFileNotFound As System.IO.FileNotFoundException
                    'Ignore file not found exception here.
                End Try
                System.IO.File.Move(strDBPath, strDBPath & ".bak")
                System.IO.File.Move(strTempFile, strDBPath)
                System.IO.File.Delete(strTempFile)
                Me.cnLocalWrite.Open()
                Me.cnLocal = cnLocalWrite

            Finally
                If Me.cnLocalWrite.State = ConnectionState.Closed Then
                    Me.cnLocalWrite.Open()
                End If
                If Me.cnLocal.State = ConnectionState.Closed Then
                    Me.cnLocal = Me.cnLocalWrite
                End If

            End Try

The Error is : You attempted to open a database that is already opened exclusively by user 'DataEntry' on machine 'TIFFANY'.  Try again when the database is available.

Is it possible that there are other instances of the connections that are open, is there a way from .NET to Kill all connections to the access database or get a list of them to close them

Has anybody got any ideas for me

Regards,

Matt

Answer this question

Compacting Access Database

MUGHRA
It never fails. I work on a problem for a while, then post a question and shortly after that, figure it out.

Two things I did to resolve the problem:

1) Removed "Persist Secruity Info=False" from the connection string. That works when opening the database for updates, but not when you want to compact it.

2) To ensure that the database connection was closed so that the compactdatabase function has exclusive use, I added gc.collect after the code to close the connection to the database.
Sumek
Does anyone have a solution to this

I am also trying to use JRO to compact an access database. I got a code sample from a team member also using VB.NET. His code was VB6 style imported into a .NET project and I modified it to make it more the .NET style (removing ON ERROR GOTO etc.).

I get the following error: System.Runtime.InteropServices.COMException (0x80040E21); Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

The code to do the compact seems pretty straightforward. I included a reference in my project to JRO (Microsoft Jet and Replication Objects 2.6 Library).

I also don't know how to check each OLE DB status value suggested by the error message.

The first part of my connection string is: Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=

If I remove the "Persist Security Info=False;" part of the connection string, I get the same error as in the original post, even when I do not have the database open and I have not opened a connection to it in my code.

Any help is much appreciated. Thanks.
puy0
Here's the solution:

http://support.microsoft.com/default.aspx scid=kb;en-us;306287
Compacting Access Database

Answer this question