How to check if Table already exists

[riaarora]

Hi All,



Is there any way to check if a particular table already exist in MS Access Database using ACCESS VBA?

Actually I'm creating temp table on the file but I want to check if temp table already exits then need to delete before creating.

e.g. I can check if query defniniton already exists using below code:

        If .DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = '" & strTempQueryName & "' ") <> 0 Then
            .DoCmd.DeleteObject acQuery, strTempQueryName
            .CurrentDb.QueryDefs.Refresh
        End If

[orange]

I have a routine where I want to create a new table. Similar to your request, I want to delete the table if it exists.

If the table exists, I use the DROP Table sql command.
If the table doesn't exist, ACCESS will give an error 3376, so in my error routine
I program for 3376 and simply Resume Next and ignore the fact that the Table doesn't exist.

I use the code snippets below
A:

10        On Error GoTo Error_DocumentTables

          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim tdf As DAO.TableDef
          Dim fld As DAO.Field
          Dim strSQL_DROP As String
          Dim strSQL_CREATE As String
          '==
          Dim idxLoop As index


          '===
          'Delete existing copy of this table
20       strSQL_DROP = "DROP TABLE data_dictionary;"

30        DoCmd.RunSQL strSQL_DROP

40        strSQL_CREATE = "CREATE TABLE data_dictionary" & _
                          "(table_name varchar(250),table_description varchar(255), field_name varchar(250),field_description varchar(255)," & _
                          "ordinal_position NUMBER, data_type varchar(15)," & _
                          "length varchar(5), default varchar(30));"


50        DoCmd.RunSQL strSQL_CREATE

60        Set db = CurrentDb()

B:

Error_DocumentTables:

330       Select Case Err.number

          Case 3376

340           Resume Next    'Ignore error if table not found
350       Case 3270    'Property Not Found

360           Resume Next
370       Case Else

380           MsgBox Err.number & ": " & Err.Description
390           Resume Exit_Error_DocumentTables

400       End Select

End Sub