Table Exist

[Dominic Vella]

How do I check if table 'Company Details' exists before I start using it?

Thanks

 

[John W. Vinson]

How do I check if table 'Company Details' exists before I start using it?

Thanks

Using it in what context? VBA code? writing a query? building a form?

 

[Dominic Vella]

In my Switchboard form. I want it generic. In Microsoft templates some
switchboards check that there is already company information before the
Switchboard will display itself. Well I don't want the switchboard
checking the 'Company Details' table if it doesn't exist.

Private Sub Form_Open(Cancel As Integer)
Dim dbs as Object
Dim rst as Object
If TableExist("Company Details") Then
' DoCmd.Hourglass False
' Set dbs = CurrentDb()
' Set rst = dbs.OpenRecordset("Company Details")
' If rst.RecordCount = 0 Then
' rst.AddNew
' rst![Address] = Null
' rst.Update
' MsgBox "Before using this application, you need to enter your
company name, address and related information."
' DoCmd.OpenForm "Company Details", , , , , acDialog
' End If
' rst.Close
' dbs.Close
MsgBox "Company Data Found"
Else
MsgBox "Company Data Not Found"
End If
' . . . Other Blah Blah Blah goes in here
End Sub

I hoped this would work, but didn't

Function TableExist(strTableName As String) As Boolean
Dim tbl As Table
TableExist = False
For Each tbl In Tables
If tbl.Name = strTableName Then
TableExist = True
End If
Next
End Function

Funnily enough, I used to use the ADOX.Catalogue and the ADOX.Table list
(very antiquainted now), but ADOX has become somewhat of a disasterous
topic, so I was hoping for a more generic version that will work across
2000, 2003 and 2007 platforms without corruption.


Dom

 

[Dominic Vella]

Oh, you are good!!

Dom

If DCount("*","msysobjects","name='Company Details' and type in (1,6)")>0
Then
MsgBox "table already exists"
Else
MsgBox "table doesn't exist"
End If


Chris
Microsoft MVP