checking for already existing tables in Access 1

[shahina]

Hi..

i am creating new tables with code and i want to check that if the table of that name is already existing in the database..i want to show an error msg if it exists..am using Microsoft Access as the back end tool...
thanx in advance..

with regards,
shahina.

 

[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

 

[shahina]

Hi Swi....

thank u so much..i got it.