Compact Access Problem

Posted on 2003-03-08
Medium Priority
Last Modified: 2010-05-01
I have this piece of code

Public Sub CompactDatabase()
    Dim JetEngine As JRO.JetEngine
    Dim strSourceConnect As String
    Dim strDestConnect As String
    Dim gbl_strDBFile As String
    Dim dbArchive As String
    Dim objFso As FileSystemObject
    Set JetEngine = New JRO.JetEngine

    gbl_strDBFile = "C:\db_ee.mdb"    
    dbArchive = Left(gbl_strDBFile, InStrRev(gbl_strDBFile, "\", Len(gbl_strDBFile))) & Split(Right(gbl_strDBFile, Len(gbl_strDBFile) - InStrRev(gbl_strDBFile, "\", Len(gbl_strDBFile))), ".")(0) & "1" & ".mdb"
    MsgBox dbArchive
    strSourceConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & gbl_strDBFile & ";" & _
                       "User Id=sa;" & _
    strDestConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=" & dbArchive & ";" & _
                     "User Id=sa;" & _
    JetEngine.CompactDatabase strSourceConnect, strDestConnect
    Set objFso = New FileSystemObject
    objFso.CopyFile dbArchive, gbl_strDBFile
    objFso.DeleteFile dbArchive 'delete the copy
    Set objFso = Nothing
    Set JetEngine = Nothing
End Sub

I got this error at this line
    JetEngine.CompactDatabase strSourceConnect, strDestConnect

Error is
"Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

Note that the original file, gbl_strDBFile, has got a password and that is mypwd.
Question by:ansi_c
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 28

Accepted Solution

vinnyd79 earned 400 total points
ID: 8097476
try changing yuor connection strings to:

   strSourceConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                      "Data Source=" & gbl_strDBFile & ";Jet OLEDB:Database Password=mypwd"
   strDestConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & dbArchive & ";Jet OLEDB:Database Password=mypwd;Jet OLEDB:Engine Type=5"

LVL 28

Expert Comment

ID: 8097478
I wouldn't add the overhead of FSO just to copy and delete a file. I would use:

FileCopy dbArchive, gbl_strDBFile
Kill dbArchive

LVL 11

Expert Comment

ID: 8097512
Why don't you change your code slightly to below.

   JetEngine.CompactDatabase strSourceConnect, strDestConnect
   ' Compact successful
   If (DIR(strDestConnect) <> "") then
       Kill strSourceConnec 'delete souce
       Name strDestConnect AS strSourceConnec
   End if
End Sub

Make sure that in your code, before you compact the database you close all connections to the database and after compacting the database you reopen these connections.

Good Luck!

Expert Comment

ID: 8098785

Try adding this to your connectionstrings:
Jet OLEDB:System database=<full_path_to_system.mdw>

Usually system.mdw exists in x:\Program Files\Microsoft Office\Office (office 2000) or
in your <WINSYSDIR>.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month10 days, 9 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question