Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    if a table exists delete (Access 2000)

    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 ?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if a table exists delete (Access 2000)

    How about:

    <pre> On Error Resume Next
    DoCmd.DeleteObject acTable, "MyTable"
    On Error GoTo 0
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Wirral, Merseyside, Merseyside, England
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: if a table exists delete (Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •