Runtime Error 3265 in my TableExists function

[mls via AccessMonster.com]

Below code ran fine in my A2003 but after moving to 2007 it is failing at the
following line..
strTableNameCheck = CurrentDb.TableDefs(TableName)
Do I need to add any references to make this run successfully?

Thanks
*****************************************

Function TableExists(TableName As String) As Boolean
Dim strTableNameCheck

On Error GoTo ErrorCode
'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)
'If no error and we get to this line, true
TableExists = True

ExitCode:
On Error Resume Next
Exit Function

ErrorCode:
Select Case Err.Number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical,
"hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description &
"hlfUtils.TableExists"
Resume ExitCode
End Select

End Function

 

[DaveT]

One way is:

Public Function IsTableInDefs(y) As Boolean
Dim db As DAO.Database
Dim zLng As Long
Dim i As Long

On Error Resume Next

IsTableInDefs = False

If Len(Nz(y)) = 0 Then
Exit Function
End If

Set db = CurrentDb

With db
zLng = .TableDefs.Count - 1 'tabledefs is base zero
For i = 0 To zLng
If .TableDefs(i).Name = y Then
IsTableInDefs = True
Set db = Nothing
Exit Function
End If

Next
End With
Set db = Nothing

End Function

 

[Brendan Reynolds]

Below code ran fine in my A2003 but after moving to 2007 it is failing at
the
following line..
strTableNameCheck = CurrentDb.TableDefs(TableName)
Do I need to add any references to make this run successfully?

Thanks
*****************************************

Function TableExists(TableName As String) As Boolean
Dim strTableNameCheck

On Error GoTo ErrorCode
'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)
'If no error and we get to this line, true
TableExists = True

ExitCode:
On Error Resume Next
Exit Function

ErrorCode:
Select Case Err.Number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical,
"hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description &
"hlfUtils.TableExists"
Resume ExitCode
End Select

End Function

Your code works for me in Access 2007. Maybe you have the VBA error trapping
option set to break on all errors? (In the VBA editor, select Tools from the
menu, then Options, then select the General tab).

 

[Stuart McCall]

Below code ran fine in my A2003 but after moving to 2007 it is failing at
the
following line..
strTableNameCheck = CurrentDb.TableDefs(TableName)
Do I need to add any references to make this run successfully?

Thanks
*****************************************

Function TableExists(TableName As String) As Boolean
Dim strTableNameCheck

On Error GoTo ErrorCode
'try to assign tablename value
strTableNameCheck = CurrentDb.TableDefs(TableName)
'If no error and we get to this line, true
TableExists = True

ExitCode:
On Error Resume Next
Exit Function

ErrorCode:
Select Case Err.Number
Case 3265 'Item not found in this collection
TableExists = False
Resume ExitCode
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description,
vbCritical,
"hlfUtils.TableExists"
'Debug.Print "Error " & Err.number & ": " & Err.Description &
"hlfUtils.TableExists"
Resume ExitCode
End Select

End Function

I don't have A2007 so I'm unable to check for you, but I can tell you you're
making the task unnecessarily complicated. All you really need is:

Public Function IsTable(TableName As String) As Boolean
Dim tdf As DAO.TableDef

For Each tdf In DBEngine(0)(0).TableDefs
If tdf.Name = TableName Then
IsTable = True
Exit For
End If
Next
End Function

You need to set a reference to DAO 3.6 for this to work.

 

[mls via AccessMonster.com]

Thank you all. Simple code worked effectively.

Below code ran fine in my A2003 but after moving to 2007 it is failing at
the

[quoted text clipped - 33 lines]

End Function

I don't have A2007 so I'm unable to check for you, but I can tell you you're
making the task unnecessarily complicated. All you really need is:

Public Function IsTable(TableName As String) As Boolean
Dim tdf As DAO.TableDef

For Each tdf In DBEngine(0)(0).TableDefs
If tdf.Name = TableName Then
IsTable = True
Exit For
End If
Next
End Function

You need to set a reference to DAO 3.6 for this to work.

 

[mls via AccessMonster.com]

Can you please explain more on these 3 option. When I selected "Break in
class Module" rather than "Break on All error" one of my old duplicates
issues resolved; before it was giving me 3022 error but after changing this
option it is showing all my duplicate ID. So I would like to know more about
these options and their purpose.

One more quetion, Do all the users have to check this option ex: turning off
Action queries have to be done on each and evry user machine. Same way do the
users have to change this option on induvidually or can I set it in the
database?

Thank you

Below code ran fine in my A2003 but after moving to 2007 it is failing at
the

[quoted text clipped - 33 lines]

End Function

Your code works for me in Access 2007. Maybe you have the VBA error trapping
option set to break on all errors? (In the VBA editor, select Tools from the
menu, then Options, then select the General tab).