exit if table doesnt exist (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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 Err0 then the table doesn’t exist and you can just skip opening the query.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote