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]

@jimatqsi
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]

@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

[ADezii]

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

[dowlingm815]

@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

[ADezii]

@dowlingm815
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!