if a table exists delete (Access 2000)

Topic

New Reply

If a table exists delete it

I have a function that makes a table called TblCars with a make table query. But when i carry out the fucntion, i receive the mesage
that the table already exists.I want to delete the table if this table exists and to skip the deletion if the table does not exists.
Can somebody help me with a code avoiding this problem ?

Reply | Quote

Replies

How about:

    On Error Resume Next
    DoCmd.DeleteObject acTable, "MyTable"
    On Error GoTo 0

Reply | Quote

I found this somewhere, it checks for a table and whether it is stored or linked:-

Sub Checklink()

Dim Db As Database
Dim Rst As Recordset
Dim Newrst As Recordset
Dim MySQL As String
Dim NewSQL As String

Set Db = CurrentDb

MySQL = “SELECT MSysObjects.Name FROM MsysObjects ”
MySQL = MySQL & “WHERE ((MsysObjects.Name)=” & CHR(34) & “Products” & CHR(34) & “) ”
MySQL = MySQL & “AND (Left$([Name],1) ” & CHR(34) & “~” & CHR(34) & ” ) ”
MySQL = MySQL & “AND (Left$([Name],4) ” & CHR(34) & “Msys” & CHR(34) & “) ”
MySQL = MySQL & ” AND (MSysObjects.Type)=1 ORDER BY MSysObjects.Name;”

‘Linked Table Products wont appear but saved table would appear
Set Rst = Db.OpenRecordset(MySQL)

NewSQL = “Select * from Products”

If Rst.Recordcount = 0 Then
MsgBox “Products is not a stored table”
On Error GoTo Nofile
Set Newrst = Db.OpenRecordset(NewSQL)
If Newrst.Recordcount 0 Then
MsgBox “Products is a linked table”
End If
Else
MsgBox “Products is a stored table”

Nofile:
If Err.Number = 3078 Then
MsgBox “Products is not a Linked table”
End If

End If

End Sub

Reply | Quote