Run Time Error 3112 - unable to open Msysobjects 1

[Knicks]

I have designed this piece of code to use in a utility database. It essentially renames linked SQL tables to strip away the DBO_

I can use the code slightly tweaked from within the database but if I want to use it from another database I get a Run Time Error 3112 on the Msysobjects table. It seems to work once and then fail or intermittantly.

The code is as follows. I use a form and button to pass the string variable for opening up another database. I am not sure if it is leaving something open or getting confused with its own Msysobjects table. Any insight would be appreciated


Public Sub ChangeTableNames(dbname As String)
Dim db As DAO.Database, rst As DAO.Recordset, tdf As DAO.TableDef


Set db = OpenDatabase(dbname)
Set rst = db.OpenRecordset("MsysObjects")

With rst
.MoveFirst
Do While Not .EOF
If rst!Type = 4 Then 'ODBC connection
If InStr(rst!Name, "dbo_") > 0 Then 'Contains a underscore and dbo in the name
Set tdf = db.TableDefs(rst!Name)
tdf.Name = Mid(tdf.Name, InStr(rst!Name, "dbo_") + 4)

End If
End If
.MoveNext
Loop
Set db = Nothing
Set rst = Nothing
Set tdf = Nothing
End With
MsgBox "done"
End Sub

 

[PHV]

What about this ?

Set db = OpenDatabase(dbname)
For Each tdf In db.TableDefs
  If Left(tdf.Name, 4) = "dbo_" Then
    tdf.Name = Mid(tdf.Name, 5)
  End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Knicks]

Works like a charm!

Thank you PHV