Check if table exists on front end and if not make it

[munkee]

Hi all,

Pretty simple as stated in the topic, I want to check the fron t end of my datyabase to see if a temporary table exists, if it does then I will carry on executing myc ode if it does not I will create the table.

This is primarily so when editing my MDB file whilst carrying out updates I dont have to try and leave any temporary tables that are not stored on my back end behind. I can simply create it when needed on my front end.

Thanks for any help

Chris

[dsatino]

You could assign the table to an object variable and if the table doesn't exist you'll get a trappable error. When the error occurs you can call your make table procedure. Or you could loop through the tabledefs collection looking for the name and if you don't find it, call your make table procedure.

[NeoPa]

Why not simply have the table there as part of the design of the Front End. Clear it down whenever required. Add records to it when required. Never delete it.

Would this not suit your purposes?

[ADezii]

If you still wish to go through the trouble, you can use Code such as the following, with your own variations of course:

Dim MyDB As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Const conTEMP_TABLE As String = "tblTEMP"
Dim blnTableExists As Boolean

blnTableExists = False

For Each tdf In CurrentDb.TableDefs
  If tdf.Name = conTEMP_TABLE Then
    blnTableExists = True
      Exit For
  End If
Next

If Not blnTableExists Then      'TEMP Table does not exist, Create it
  'Initialize the Temporary Table.
  Set MyDB = CurrentDb()
  Set tdf = MyDB.CreateTableDef("tblTEMP")
    
  'Specify the Fields.
  With tdf
    'AutoNumber: Long with the Attribute set.
    Set fld = .CreateField("ID", dbLong)
      fld.Attributes = dbAutoIncrField + dbFixedField
        .Fields.Append fld
        
    'Text field: Maximum 30 characters, and Required.
    Set fld = .CreateField("Field1", dbText, 30)
      fld.Required = True
        .Fields.Append fld
        
    'Text field: Maximum 20 characters.
    .Fields.Append .CreateField("Field2", dbText, 20)
      
    'Yes/No field.
    .Fields.Append .CreateField("YesNo_Field", dbBoolean)
  End With
    
  'Save the TEMP Table and Clean Up!
  MyDB.TableDefs.Append tdf
  Set fld = Nothing
  Set tdf = Nothing
Else
  'Table exists, your Code here...
End If

[dsatino]

I would go with NeoPa on this one.

[munkee]

I cracked this one earlier sorry for not posting.

I just got a function to check whether the table exists by looking through the current db objects. if it doesnt I make it.

If it does exist I clear out the data and input new data.

This is all purely incase the table didnt exist I wanted to ensure it would.