Checking to see if a table is empty

[dowlingm815]

A procedure is calling a function to see if a table is empty through a Boolean value, however, i am receiving a compiler error. any assistance, would be appreciated.

the code is:

    If TableExists = False Then
        DoCmd.DeleteObject acTable, "ContractImport"
        DoCmd.DeleteObject acTable, "ContractImport$_ImportErrors"
    End If

Public Function TableExists(strTable As String) As Boolean
    Dim strName As String
  
    On Error Resume Next
    'If table exists already then strName will be > ""
    strName = CurrentDb.TableDefs(strTable).Name
    TableExists = Not (strName = "")

End Function

[jimatqsi]

dowlingm815,
Your function declaration

Function TableExists(strTable As String)

indicates the function requires an input parameter, yet your call to the function

If TableExists = False Then

is not supplying the needed parameter.

You need to add the name of the table to the call to the function so it knows what table name to check.

Jim

[ADezii]

dowlingm815,
Your function declaration

Function TableExists(strTable As String)

indicates the function requires an input parameter, yet your call to the function

If TableExists = False Then

is not supplying the needed parameter.

You need to add the name of the table to the call to the function so it knows what table name to check.

Jim

As previously pointed ou by jamitqsi:

If TableExists("Employees") Then
  MsgBox "Exists"
Else
  MsgBox "No exists"
End If

[dowlingm815]

thanks appreciate the correction. here is the working code:

    If TableExists("ContractImport") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "ContractImport"
    End If
    If TableExists("ContractImport$_ImportErrors") Then
    ' delete the table if it exists
        DoCmd.DeleteObject acTable, "ContractImport$_ImportErrors"
    End If

Public Function TableExists(strTable As String) As Boolean
   On Error GoTo Err_Hndlr
   
   Dim strName As String
  
'    On Error Resume Next
    TableExists = True
    'If table exists already then strName will be > ""
    strName = CurrentDb.TableDefs(strTable).Name
    TableExists = Not (strName = "")
    Exit Function
    
Err_Hndlr:
    TableExists = False
    
End Function

[ADezii]

Given your circumstances, another option to consider is (In-Line Code):

Dim tdf As DAO.TableDef
Const conTABLE_NAME As String = "ContractImport"
  
For Each tdf In CurrentDb.TableDefs
  If Left$(tdf.Name, Len(conTABLE_NAME)) = conTABLE_NAME Then
    CurrentDb.TableDefs.Delete tdf.Name
  End If
Next

[dowlingm815]

Given your circumstances, another option to consider is (In-Line Code):

Dim tdf As DAO.TableDef
Const conTABLE_NAME As String = "ContractImport"
  
For Each tdf In CurrentDb.TableDefs
  If Left$(tdf.Name, Len(conTABLE_NAME)) = conTABLE_NAME Then
    CurrentDb.TableDefs.Delete tdf.Name
  End If
Next

I'm not sure if i follow the instructions, please keep me honest. it looks like it calculating the length of the table correct?

mary

[ADezii]

I'm not sure if i follow the instructions, please keep me honest. it looks like it calculating the length of the table correct?

mary

In a Nutshell, any Table beginning with ContractImport will be DELETED!

[dowlingm815]

In a Nutshell, any Table beginning with ContractImport will be DELETED!

that's awesome, but i still don't understand the code.

the following code, reads each table NAME in the db, correct?

For Each tdf In CurrentDb.TableDefs

the next statement, calculates the length of the string, but what does the rest do?

  If Left$(tdf.Name, Len(conTABLE_NAME)) = conTABLE_NAME Then

i appreciate all your guidance.

mary

[ADezii]

that's awesome, but i still don't understand the code.

the following code, reads each table NAME in the db, correct?

For Each tdf In CurrentDb.TableDefs

the next statement, calculates the length of the string, but what does the rest do?

  If Left$(tdf.Name, Len(conTABLE_NAME)) = conTABLE_NAME Then

i appreciate all your guidance.

mary

The code loops through every Table in the Database and if the Table Name begins with the String defined in the Constant (conTABLE_NAME ), it is then DELETED! The code is compact and efficient, but the only drawback would be if you had Tables named:

ContractImport_2
ContractImport_New
ContractImport_06182010
ContractImportFK
ContractImportYaDaYaDa
ContractImport Error

They would all be DELETED!