I am trying to restore an old database our group was using. The original database was not using “Option Explicit” but was working. I want to use “Option Explicit” but I can not get the darn “Dim” correct. Can you please help me and look at the code and determine what the correct type of Dim I should be using for the four Dim at the top of the code? I took a guess with the first four Dim’s and I am getting the error message “object variable or with block variable not set” when I run the code but debug and compile does not catch the error. Thanks in advance for your help!
Private Sub bRepair_Click()
On Error GoTo errbRepair_Click
Dim x As Integer ‘is this ok?
Dim DatabasePath As String ‘is this ok?
Dim RepairDB As Database ‘this might be the problem
Dim ret As Reference ‘is this ok?
Dim i As Integer ‘I think this is ok?
Dim Repaired As Recordset ‘I think this is ok?
Dim MySet As Recordset ‘I think this is ok?
Dim MyDb As Database ‘I think this is ok?
Set MyDb = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDb.OpenRecordset(“tDatabases”, DB_OPEN_DYNASET)
Set Repaired = MyDb.OpenRecordset(“tRepairDate”, DB_OPEN_TABLE)
i = 1
MySet.MoveLast
x = MySet.RecordCount
MySet.MoveFirst
Do Until i > x
If Right(MySet!Path, 1) “” Then
DatabasePath = MySet!Path & “”
Else
DatabasePath = MySet!Path
End If
ret = RepairDB(MySet!DriveLetter & DatabasePath & MySet!Database)
If ret Then
ret = RepairDB(MySet!DriveLetter & DatabasePath & MySet!Database)
If ret Then
MsgBox MySet!DriveLetter & DatabasePath & MySet!Database & ” has been Repaired.”
End If
End If
MySet.MoveNext
i = i + 1
Loop
Repaired.MoveFirst
Repaired.Delete
Repaired.AddNew ‘ Create new record.
Repaired(“Date”) = Now ‘ Set Date.
Repaired.Update ‘ Save changes.
Repaired.Close
Me.Requery
MySet.Close
Exit Sub
errbRepair_Click:
MsgBox Err.Description & “An error occurred while attempting to repair ” & MySet!DriveLetter & DatabasePath & MySet!Database & “.” & Chr(13) & Chr(13) & ” Please ensure the Drive Letter, Path, and Database Name are correct in the Database Listing.”
Exit Sub
End Sub
