Option Explicit
Public Enum ObjectType
ot_Table = 1
ot_AttachedTable = 6
ot_Form = -32768
ot_Query = 5
ot_Report = -32764
ot_Module = -32761
ot_Macro = -32766
ot_Relationship = 8
End Enum
Public Function ObjectExists(ByVal parmName, parmType As ObjectType) As Boolean
If IsNull(DLookup("Name", "Msysobjects", "Name='" & Replace(parmName, "'", "''") & "' And Type=" & parmType)) Then
ObjectExists = False
Else
ObjectExists = True
End If
End Function
Notes:
?ObjectExists("BigTable", ot_Table)
True
?ObjectExists("Big'Table", ot_Table)
False
?ObjectExists("BigTable", ot_Report)
False
Option Explicit
Public Sub AutoFormAllTables()
Dim tdf As TableDef
Dim frm As Form
Dim strFormname As String
Dim strNewname As String
Dim lngLoop As Long
On Error Resume Next
For Each tdf In DBEngine(0)(0).TableDefs
If (Len(tdf.Connect) <> 0) Or (tdf.Name Like "Msys*") Then
Else
'Debug.Print tdf.Name
DoCmd.SelectObject acTable, tdf.Name, True
DoCmd.RunCommand acCmdNewObjectAutoForm
DoCmd.RunCommand acCmdDesignView
Set frm = Application.Forms(0)
strFormname = frm.Name
frm.DefaultView = 2 'datasheet
DoCmd.Save acForm, strFormname
DoCmd.Close acForm, strFormname
If ObjectExists("frm" & tdf.Name, ot_Form) Then
lngLoop = 0
Do
lngLoop = lngLoop + 1
Loop While ObjectExists("frm" & tdf.Name & "_" & lngLoop, ot_Form)
DoCmd.Rename "frm" & tdf.Name & "_" & lngLoop, acForm, strFormname
Else
DoCmd.Rename "frm" & tdf.Name, acForm, strFormname
End If
Select Case Err
Case 0
Case 2501
strNewname = InputBox("New name for form or cancel for no save", "New name prompt", "frm" & tdf.Name & "_" & Timer)
Err.Clear
If Len(strNewname) = 0 Then
Else
DoCmd.Rename strNewname, acForm, strFormname
If Err <> 0 Then
Err.Clear 'only one mulligan
End If
End If
Case 7791
MsgBox "Error (" & Err & ") " & Err.Description, vbCritical, "frm" & tdf.Name & "Creation problem"
Err.Clear
Case Else
MsgBox "Error (" & Err & ") " & Err.Description, vbCritical, "frm" & tdf.Name & "Unknown problem"
Err.Clear
End Select
End If
Next
End Sub
Option Explicit
Public Enum ContainerType
ct_DataAccessPages = 0
ct_Databases = 1
ct_Forms = 2
ct_Modules = 3
ct_Relationships = 4
ct_Reports = 5
ct_Scripts = 6
ct_SysRel = 7
ct_Tables = 8
End Enum
Public Function NameExistsInContainer(ByVal parmName, parmContainer As ContainerType) As Boolean
Dim varItem As Variant
For Each varItem In DBEngine(0)(0).Containers(parmContainer).Documents
If varItem.Name = parmName Then
NameExistsInContainer = True
Exit Function
End If
Next
NameExistsInContainer = False
End Function
Option Explicit
Public Enum ContainerType
ct_DataAccessPages = 0
ct_Databases = 1
ct_Forms = 2
ct_Modules = 3
ct_Relationships = 4
ct_Reports = 5
ct_Scripts = 6
ct_SysRel = 7
ct_Tables = 8
End Enum
Private dicContainers As Object 'Scripting.Dictionary
Public Function NameExistsInContainer_Fast(ByVal parmName, parmContainer As ContainerType, Optional parmReset As Boolean = False) As Boolean
If dicContainers Is Nothing Then
PopulateDicContainers
End If
NameExistsInContainer_Fast = dicContainers(parmContainer).Exists(parmName)
If parmReset Then
dicContainers.RemoveAll
Set dicContainers = Nothing
End If
End Function
Private Sub PopulateDicContainers()
Dim dicNames As Object 'New Scripting.Dictionary
Dim vContainer As Variant
Dim vDocument As Variant
Dim lngLoop As Long
Set dicContainers = CreateObject("Scripting.Dictionary")
For lngLoop = 0 To DBEngine(0)(0).Containers.Count - 1
Set dicNames = CreateObject("Scripting.Dictionary")
For Each vDocument In DBEngine(0)(0).Containers(lngLoop).Documents
dicNames.Add vDocument.Name, 1
Next
dicContainers.Add lngLoop, dicNames
Next
End Sub
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (3)
Commented:
Commented:
I didn't grasp this sentence in your article the first two or three times I looked it over.
(That apostrophe is invisible unless you are looking for it, or know it is there. The eye just misses it)
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] table object does not exist.>>
Perhaps
<<In the following trials, I know that a [BigTable] table object exists, but a [Big'Table] (with an apostrophe between Big and Table) table object does not exist.>>
Would be a useful edit.
Also, with the Enum's you show in the code, you don't explicitly state where those values come from, which make them a bit like magic.
Having looked in MsysObjects myself, I know that in the first code sample you made MsysObjects visible and then figured out what the numbers in [MsysObjects].[Type] mean.
In the container code, how did you derive the Enum values you show there?
Author
Commented:Thanks for the feedback. Maybe I should have used an Irish name so that readers would expect to see an apostrophe.
I found a partial list of MsysObjects type values during a web search. I deduced the others by creating objects, such as a macro, in my test database and then seeing what appeared in the MsysObjects table.
I iterated the Container collections to determine the Enum values for Containers.