Solved

Follow up a SQL query with a DB compact/repair???

Posted on 2006-11-01
1
159 Views
Last Modified: 2010-04-30
I am trying to write a small .exe that will run a query on a DB, then run a compact and repair on that DB plus the other DBs in the same folder.

I get the error:
MS Jet engine stopped the process becuase you and another user are attempting to change the same data at the same time.

CODE:

Option Explicit
Public DBList As String
Public strDBName As String
Public strNewDBName As String

Private Sub ListTables(ByVal db_name As String)
Dim statement As String
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim lRecords
    Set conn = New ADODB.Connection
   
    conn.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Persist Security Info = False;" & _
        "Data Source=C:\Program Files\Helios11\Data\Link.mdb"
    conn.Open
   
    'On Error Resume Next
 
    Set rs = conn.OpenSchema(adSchemaTables, _
        Array(Empty, Empty, Empty, "Table"))
        conn.Execute "delete * from transactions where client_no not in (select client_no from client_profile)", lRecords
    rs.Close
         
    conn.Close
     
    cmdRun.Visible = False
    lblRun.Caption = "Cleanup Complete!"
    Label1.Caption = lRecords
    cmdClose.Visible = True
     
    Call DBRepair
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdRun_Click()
cmdRun.Enabled = False
Call ListTables("Link.mdb")

End Sub

Private Sub Form_Load()
Dim Index

cmdClose.Visible = False

DBList = Dir("C:\Program Files\Helios11\Data\*.mdb") ' this will list all the files in current path.

ListBox.Enabled = False

Do Until DBList = ""

ListBox.AddItem DBList

DBList = Dir

Loop

lbl1.Caption = ListBox.List(0)
lbl2.Caption = ListBox.ListCount
End Sub

Private Sub DBRepair()
Dim JRO As JRO.JetEngine
Dim FSO As New FileSystemObject
Dim Index As Integer
Dim Count As Integer


On Error Resume Next
Kill "C:\Program Files\Helios11\*.mdb"
If Err = 53 Then
End If

Index = 0
Count = ListBox.ListCount

For Index = 0 To ListBox.ListCount - 1

lbl1.Caption = ListBox.List(Index)
lbl2.Caption = Count

frmUpdate.Refresh

Set JRO = New JRO.JetEngine

strDBName = "C:\Program Files\Helios11\Data\" & ListBox.List(Index)
strNewDBName = "C:\Program Files\Helios11\" & ListBox.List(Index)
                           
        JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBName & "", _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strNewDBName & ";Jet OLEDB:Engine Type=5"

        Set JRO = Nothing
               
        Count = Count - 1
Next
               
        lbl1.Caption = "Done"
        lbl2.Caption = "Done"
       
        FSO.CopyFile "C:\Program Files\Helios11\*.mdb", "C:\Program Files\Helios11\Data", True

        Set FSO = Nothing
       
        Kill "C:\Program Files\Helios11\*.mdb"
       
        MsgBox "All Databases Successfully Compacted and Repaired!", vbOKOnly, "Operation Complete"
       
        Unload Me
               
End Sub

TIA,
Andrew
0
Comment
Question by:Ahelbling
1 Comment
 
LVL 3

Accepted Solution

by:
fullcontact earned 500 total points
ID: 17866511
Check for .ldb files in your dbrepair routine.

May need to delay until the .ldb files have cleared before trying to repair.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now