Web Development

Ask something
Discussion Locked

VB6 - Verifying if MS Access Table exists in database

0 helpful
4 replies

by ashley.rowbottom 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am in vb and have an sql string created to drop/create/insert data into a table. Is there someway I could check to see if the tables exists before I try to drop it.

4 replies

by john.a.wills

This question belongs in Technical Q & A. I will look for it there, because I am interested in the subject.

by steven_v_brown

In 2nd example
Remove Prens around "cnn.execute("DROP TABLE [YOUR TABLE NAME]")"

In 3rd example code lines wrapped (Update ConnectionString property with your DB name)

by steven_v_brown

1st: (Most likely the fastest)

Run a query of the SysObjects Table

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Name="Your Table Name") AND (MSysObjects.Type=1)

2nd: (sneaky but works)

On error resume next
err.number = 0
'Asssume your ADO data connection is cnn...
cnn.execute("DROP TABLE [YOUR TABLE NAME]")
'Discard error...
err.number = 0
'Create your table...

3rd: (Snaziest)
'Walk the Jet table collection...

Reference: (Use Late Binding if you do not want to reference)
Microsoft ActiveX Data Objects 2.x Library (This is ADODB)
Microsoft ADO Ext. 2.8 for DDL and Security (This is ADOX)

Call the fuction: msgbox TableExists("ABC")

Past the following code:

Option Explicit
'----------------------------------------------------------------
Public Function TableExists(TableName As String) As Boolean
On Error GoTo ErrorHandle:
Dim cnn As ADODB.Connection
'Dim cnn
Dim cat As ADOX.Catalog
'Dim cat
Dim tbl As ADOX.Table
'Dim tbl
'--------------------------------------------------------------
'Connect to the Database...
'--------------------------------------------------------------
Set cnn = CreateObject("ADODB.Connection")
'cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourDB.mdb;Persist Security Info=False"
cnn.Open
'--------------------------------------------------------------
'Set cat = New ADOX.Catalog
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = cnn
'--------------------------------------------------------------
For Each tbl In cat.Tables
If tbl.Type = "TABLE" And tbl.Name = TableName Then
TableExists = True
End If
Next
'--------------------------------------------------------------
ExitProc:
On Error Resume Next
Set tbl = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
Exit Function
ErrorHandle:
TableExists = False
GoTo ExitProc:
End Function
'----------------------------------------------------------------

by steven_v_brown

1st: (Most likely the fastest)

Run a query of the SysObjects Table

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (MSysObjects.Name="Your Table Name") AND (MSysObjects.Type=1)

2nd: (sneaky but works)

On error resume next
err.number = 0
'Asssume your ADO data connection is cnn...
cnn.execute("DROP TABLE [YOUR TABLE NAME]")
'Discard error...
err.number = 0
'Create your table...

3rd: (Snaziest)
'Walk the Jet table collection...

Reference: (Use Late Binding if you do not want to reference)
Microsoft ActiveX Data Objects 2.x Library (This is ADODB)
Microsoft ADO Ext. 2.8 for DDL and Security (This is ADOX)

Call the fuction: msgbox TableExists("ABC")

Past the following code:

Option Explicit
'----------------------------------------------------------------
Public Function TableExists(TableName As String) As Boolean
On Error GoTo ErrorHandle:
Dim cnn As ADODB.Connection
'Dim cnn
Dim cat As ADOX.Catalog
'Dim cat
Dim tbl As ADOX.Table
'Dim tbl
'--------------------------------------------------------------
'Connect to the Database...
'--------------------------------------------------------------
Set cnn = CreateObject("ADODB.Connection")
'cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\YourDB.mdb;Persist Security Info=False"
cnn.Open
'--------------------------------------------------------------
'Set cat = New ADOX.Catalog
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = cnn
'--------------------------------------------------------------
For Each tbl In cat.Tables
If tbl.Type = "TABLE" And tbl.Name = TableName Then
TableExists = True
End If
Next
'--------------------------------------------------------------
ExitProc:
On Error Resume Next
Set tbl = Nothing
Set cat = Nothing
cnn.Close
Set cnn = Nothing
Exit Function
ErrorHandle:
TableExists = False
GoTo ExitProc:
End Function
'----------------------------------------------------------------

This discussion is closed to new replies.