need help with “Dim”

Topic

New Reply

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

Reply | Quote

Replies

Do you have the option to Debug when you get the error message? If so, choose to and see what line of code has been highlighted as the offending bit. Post back…

Reply | Quote

You declared this [indent]

---

Dim RepairDB As Database ‘this might be the problem

---

[/indent]… but then you tried this:

ret = RepairDB(MySet!DriveLetter & DatabasePath & MySet!Database)

Neither Access nor I know what you’re trying to do here, since you haven’t opened RepairDB as a database at all and since References are objects and have to be instantiated with the Set keyword, so ret can’t be a reference object either.

In fact, I suspect that RepairDB is a function, not a database object, and that ret should be declared as whatever data type is returned by RepairDB.

Reply | Quote