Does a table exist? 2

[aliendan]

I need to write code the checks to see if a table exists before I either delete the table or create the table through code. What do I write to check for the existance of a table?
Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[nickjar2]

the way i do it is:
Dim tbl As TableDef
Dim db As DAO.Database

Set db = CurrentDb

For Each tbl In db.TableDefs
If tbl.Name = "Table" Then
docmd.delete etc etc
End If

Next tbl

Hope this is ok,

Nick

 

[DougP]

Well you can trap the error when it occurs.
I grabbed this from some code I have.

Error 3010 is generated when the table already exists
So if you put this Error routine behind a Temporary button and change the names to your table names and run it, it will give you the error number which you then trap out similar to what I did.

Public Sub MakePartMaster()
On Error GoTo Err_MakePartMaster

Dim dbs As Database, MyTableII As Recordset, SQL As String, SQL2 As String

DoCmd.Hourglass True

Set dbs = CurrentDb
Set MyTableII = dbs.OpenRecordset("Copy-PartMaster&quot

' Delete the table when done
DoCmd.DeleteObject acTable, "Copy-PartMaster-NEWEST"
'

Exit_MakePartMaster:
DoCmd.Hourglass False
Exit Sub

Err_MakePartMaster:
Select Case Err.Number
Case 3010
'Table already exists
DoCmd.DeleteObject acTable, "Copy-PartMaster-NEWEST"
Case 3021
'no current record
Resume NoRecs
Case Else
MsgBox "Error # " & Err.Number & " " & Err.Description, vbInformation, "In sub MakePartMaster"
End Select

Resume Exit_MakePartMaster
End Sub


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[aliendan]

Thank you very much nickjar2 and DougP. You both solved this problem. Hope I can return the favor some time.
Dan Rogotzke
Dan_Rogotzke@oxy.com