Iterate Array And Delete If Exists

[chalupabatman]

I am attempting to set an array of table and or query names and pass to a function to delete. This is my syntax - but it is not functioning properly - how would I change this to work right?

Function CheckIfExist()
   tableArray = Array("ExportForWorksheet", "ExportForWorksheetNonInventory", "_LocalTable")
   
   'Iterating the array to pass each one individually to the Delete function
   For amx = LBound(tableArray) To UBound(tableArray)
   Debug.Print CStr(tableArray)
        If TableExists(CStr(tableArray)) Then
            With db.TableDefs
                .Delete CStr(tableArray)
                .Refresh
            End With
        End If
   Next
End Function
  
Public Function TableExists(strName As String) As Boolean


   On Error GoTo HandleErr


   Dim db As DAO.Database, tDef As DAO.TableDef
   
   Set db = CurrentDb
   
   TableExists = False
   
   For Each tDef In db.TableDefs
      If tDef.Name = strName Then
         TableExists = True
         Exit For
      End If
   Next tDef


   For Each qDef In db.QueryDefs
    If qDef.Name = strName Then
        TableExists = True
        Exit For
    End If
   Next qDef


ExitFunction:
   db.Close
   Set db = Nothing
   Exit Function
   
HandleErr:
   TableExists = False
   Resume ExitFunction
   
End Function

[ranman256]

there's no need for arrays in access... everything is a table or collection.
loop thru the table and delete

for i = 0 to lstTbls.listcount-1
or
while not rst.eof
or
set colTbls = new collection
for each tbl in colTbls

[Micron]

there's no need for arrays in access

Can't say I agree with that. Can't disagree that the goal might be simpler (as suggested) either.
Chalupabatman: "it is not functioning properly" is of little help to anyone here. Often, the major issue in such an exercise using arrays is that after a delete the array size has been altered thus the loop counter is reduced (For amx =...). In some cases, the array as to be declared as Static so as to maintain the count.

In your case, I suspect the array approach is because you don't have a list or recordset of the table values you want to delete. You could build one so that you can iterate over the tables collection as suggested, or try making the array static. To prove or disprove that this is the cause, check the array size as you step through using either a debug or msgbox statement. I think in your case it probably isn't since I don't see anything definite that would affect the array size, but I'm not sure - you don't provide much that explains the failure.

[chalupabatman]

Can't say I agree with that. Can't disagree that the goal might be simpler (as suggested) either.
Chalupabatman: "it is not functioning properly" is of little help to anyone here. Often, the major issue in such an exercise using arrays is that after a delete the array size has been altered thus the loop counter is reduced (For amx =...). In some cases, the array as to be declared as Static so as to maintain the count.

In your case, I suspect the array approach is because you don't have a list or recordset of the table values you want to delete. You could build one so that you can iterate over the tables collection as suggested, or try making the array static. To prove or disprove that this is the cause, check the array size as you step through using either a debug or msgbox statement. I think in your case it probably isn't since I don't see anything definite that would affect the array size, but I'm not sure - you don't provide much that explains the failure.

So sorry I thought I included the error I was receiving. I get a type mismatch error on this line

If TableExists(CStr(tableArray)) Then

[chalupabatman]

there's no need for arrays in access... everything is a table or collection.
loop thru the table and delete

for i = 0 to lstTbls.listcount-1
or
while not rst.eof
or
set colTbls = new collection
for each tbl in colTbls

I do not follow....out of all the table names in the database, I only want to delete 3. How would I specify those 3 table names w/ no array?

[Micron]

You are probably trying to pass an invalid type to the function. Likely Null. Put a break on the line and when it stops there, check in the immediate window:
?tablearray and hit return. What do you get?

[chalupabatman]

You are probably trying to pass an invalid type to the function. Likely Null. Put a break on the line and when it stops there, check in the immediate window:
?tablearray and hit return. What do you get?

If I type in the Immediate window ?tableArray - I get a Run-time error '13': Type mismatch

[June7]

Why are you using code to delete tables?

Can't display array object in immediate window. Your code is referencing the array object, not its elements. Reference array elements by index.

If TableExists(tableArray(amx)) Then
...
.Delete tableArray(amx)