Check whether table exists 1

[vinodi]

Hi,

I have an ADODB Connection object having a connection string pointing to an Access 2000 database. Before fetching a set of records I want to check whether a particular table exists or not. How do I accomplish this using ADO. Because it is quite simple to use Tabledef objects using DAO.

Please help me with your inputs.

Regards,
Vinodi

~~The only place where SUCCESS comes before WORK is in the dictionary~~

 

[koala15]

Try this:

Open the recordset of this table, there will be a unique Err.Number if the table is missing.

"Life is full of learning, and then there is wisdom"

 

[koala15]

Try this:

Open the recordset using the table (in question) as the source. There will be a unique Err.number if the table does not exist.

"Life is full of learning, and then there is wisdom"

 

[Swi]

Try somthing like this:

'=============================================
' Add a reference to the following
' Microsoft ActiveX Data Objects X.X Library
' Microsoft ADO Ext. X.X for DLL and Security
'=============================================
Private Sub Command1_Click()
MsgBox TableExist("F:\Supplier Tracking Interface\Supplier.mdb", "Master")
End Sub

Private Function TableExist(sDatabaseFullPath As String, sTableName As String) As Boolean
Dim conn As New ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim Exists As Boolean

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

' Connects to the specified Access database
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDatabaseFullPath & ";Persist Security Info=False"
Set cat.ActiveConnection = conn

' Searches for specified table name
For Each tbl In cat.Tables
If UCase(tbl.Name) = UCase(sTableName) Then
Exists = True
Exit For
End If
Next tbl

conn.Close
Set conn = Nothing
Set cat = Nothing
Set tbl = Nothing

TableExist = Exists

End Function

Swi

 

[manishjoisar]

By Using AdoCon Schema You Can take this in Record Set & Then Find Rs.Find 'Table Name = TABLE_NAME '