Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    exit if table doesnt exist (Access 2000)

    exit if table doesnt exist

    I have a function called updatewarehouse that opens an append query somethjing like that
    Public Function UpdateWarehouse()
    DoCmd.OpenQuery "AppendOrder", acNormal, acEdit
    end function

    I want at first to have a checking, if the table order1 exists,and if it does not exist, to stop the function, add a message "order1 not existing" However the function should continue if the table order1 exists.
    How can i do it?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: exit if table doesnt exist (Access 2000)

    The simplest way is to try to set On Error Resume Next and then try to set a reference to the table. In the next line check to see if the Err=0. If it does, then close the reference and set it to nothing because the table exists and you can go ahead and open the query. If the Err<>0 then the table doesn't exist and you can just skip opening the query.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exit if table doesnt exist (Access 2000)

    Dear Charlotte


    Thank you so muh for your reply.Yes i eant to do my code with the on RError Resume next, however i cannot set the referene to my table. I am trying it on the following lines
    Dim rs As Recordset
    Set rs = CurrentDb().OpenRecordset("TableName")
    However i receive the message "Variable not defined",pointing to the table name.
    Could you possibly help?
    Would you mind suggesting me the entire code in order for me to avoid further obstacles?I hope the code will not be too time consuming for you
    Best regards

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: exit if table doesnt exist (Access 2000)

    Do you have a table called "tablename"? If you're using an actual table name, you put it in quotes. If you're using a string variable, you assign the tablename to the variable and put the name of the variable in the code without any quotes.

    You are setting a recordset varable, not a table variable. Try this (BTW, using CurrentDb like that is not recommended by those as emminent as Ken Getz, Stan Litwin, et al):

    Public Function UpdateWarehouse()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName as string

    strTableName = "Order1"
    On Error Resume Next

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    If Err=0 Then
    DoCmd.OpenQuery "AppendOrder", acNormal, acEdit
    Else
    MsgBox strTableName & " does not exist"
    End If
    End Function
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: exit if table doesnt exist (Access 2000)

    Dear Charlotte,

    Just to let you know that it works!!!!!! I have received and excellent and elegant solution.Thank you
    very much!

    May i wish you all the best

Posting Permissions

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