Search for the tables names

[Lally3]

I am trying to compare a Text1.value (New table name) to see whether that name already exists. If it does, then to produce an error message.

 

[cmmrfrds]

Here is a function that uses the adox library to manipulate/read table information. Maybe you can use this example to setup what you need.


Function catalogTC()
'-- set reference to ADOX library
'- Microsoft ADO Ext. 2.6 for DDL and Security
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cg As New ADOX.Catalog
Dim tb As New ADOX.Table
Dim cn As ADODB.Connection
Dim cl As Column
Dim pp As Property

Set cg.ActiveConnection = CurrentProject.Connection

For Each tb In cg.Tables
Debug.Print "table name = "; "-------"; tb.Name; "--------"; tb.Type

' If tb.Type = "TABLE" Then
' If tb.Type = "LINK" Then
' For Each pp In tb.Properties
' Debug.Print "property name = "; pp.Name
' Debug.Print "property value = "; pp.Value
' Next

If (tb.Type = "TABLE" And tb.Name = "Categorys&quot Then
For Each cl In tb.Columns
Debug.Print "name = "; cl.Name
For Each pp In cl.Properties
Debug.Print "property name = "; pp.Name
Debug.Print "property value = "; pp.Value
Next
Next
End If
Next

End Function

 

[chrissie1]

Dim dbs_temp As DAO.database
Dim rst_temp As DAO.Recordset

Set dbs_temp = CurrentDb
Set rst_temp = dbs_temp.OpenRecordset("SELECT MSysObjects.Type, MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Name)='" & Text1 & "'))&quot
If rst_temp.RecordCount = 0 Then
MsgBox "Table does not exists"
End If
Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[beetee]

Just for fun, yet another method...

Function TableExists(TableName As String) As Boolean
Dim MyDb As Database
Set MyDb = CurrentDb

Dim MyTdf As TableDef
Set MyTdf = Nothing
On Error Resume Next
Set MyTdf = MyDb.TableDefs(TableName)
TableExists = Not (MyTdf Is Nothing)

Set MyTdf = Nothing

MyDb.Close
Set MyDb = Nothing
End Function

 

[raskew]

Yet another...

Function DoesTableExist(pTableName As String) As Boolean
'*******************************************
'Name:      DoesTableExist (Function)
'Purpose:   Determine if table name exists
'Inputs:    from debug window ? DoesTableExist("test1")
'Output:    True or False
'*******************************************

Dim db As Database
Dim td As TableDef

Set db = CurrentDb
For Each td In db.TableDefs
   DoesTableExist = IIf(td.Name = pTableName, True, False)
   If DoesTableExist = True Then GoTo Exit_Function
Next td

DoesTableExist = False

Exit_Function:
   db.Close
   Set db = Nothing
End Function