Solved

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

Posted on 2006-11-01
1
162 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
[X]
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
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

696 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