Check to see if an Access table is in a database

[Jimgarry]

Hi, thanks in advance, I am working on a vb6 project using ado, I have connection into a access 2000 database I would like to know the commands to check to see if a table exists in the mdb.

I have the I have the stuff to delete the table but I get an error if the table is not there. and what I would like to do is just continue on the next record. I want to stay away from "on error resume next" if I can.
any suggestions

here is the section of code

cn.Execute "DROP TABLE TempData", dbFailOnError

Yes I know that I have dbFailOnError but not sure what else to put in there?

Thanks again


Jim

 

[MarkSweetland]

You can do it a couple of ways--namely using the On Error Resume Next which would mask any error encountered and continue to the next statement or by referencing ADOX in your project and deleting the table directly.

On Error Resume Next
cn.Execute "DROP TABLE TempData", dbFailOnError
[green]' if the table was not present, no error message shows [/green]
<This statement executed>

or

Private Sub DropTable(tblName As String)
[green]' Project reference Microsoft ADO Ext. 2.x for DDL & Security[/green]
Dim oCat As New ADOX.Catalog
Dim oTable As ADOX.Table
oCat.ActiveConnection = &quot;provider=Microsoft.jet.oledb.4.0;&quot; & _
&quot;data source = c:\db2.mdb&quot;
For Each oTable In oCat.Tables
If LCase(oTable.Name) = LCase(tblName) Then
oCat.Tables.Delete (tblName)
End If
Next
Set oCat = Nothing
End Sub

Sub cmdSomeButton_Click()
DropTable &quot;TempData&quot;
End Sub


Mark

&quot;You guys pair up in groups of three, then line up in a circle.&quot;
- Bill Peterson, a Florida State football coach

 

[vidru]

Or you could query the database's MSysObjects table to find out if it exists.

-dave

 

[Jimgarry]

I see that I never got back to you. Thank you for your assistance. I am using the second suggestion and it is working fine. Now I can remove the dao 3.6 from my ref. thanks again