How to Iterate Through and delete Specific Tables Using Code

[catbeasy]

I would like to delete tables using code where part of the table names have a certain value.

So the table have as their first 13 characters the following string

tbl_req_tmp_a..

I would like code to iterate through the tables collection and delete all tables that have this as the first 13 characters of their name..not sure of the exact syntax (e.g. is it tabledef or tabledefs), but something like:

dim tbl as tabledef(s)

for each tbl in tabledef(s)
where left(tblname,13) = 'tbl_req_tmp_a"
docmd.deleteobject actTable, tbl
next tbl

Thanks for any assistance..

 

[HiTechCoach]

Try changing this:

where left(tblname,13) = 'tbl_req_tmp_a"
docmd.deleteobject actTable, tbl

to

If Left(tbl.name,13) = "tbl_req_tmp_a" Then
   docmd.deleteobject actTable, tbl.Name
End if

 

[HiTechCoach]

A better method woudl be this:

Dim dbCurr As DAO.Database
Dim intLoop As Integer
  
  Set dbCurr = CurrentDb()
  For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
    If Left$(dbCurr.TableDefs(intLoop).Name, 13) = "tbl_req_tmp_a" Then
      dbCurr.TableDefs.Delete dbCurr.TableDefs(intLoop).Name
    End If
  Next intLoop
  Set dbCurr = Nothing

The problem with using For Each tdf In .TableDefs is that you're pointing to a specific table, then moving to the next table in the collection. If you delete, say, table 3, the pointer to the table then automatically moves to the next table (table 4). When you then hit the Next statement, it moves the pointer to the next table again (table 5), so that you don't actually check table 4.

 

[catbeasy]

A better method woudl be this:

Dim dbCurr As DAO.Database
Dim intLoop As Integer
 
  Set dbCurr = CurrentDb()
  For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1
    If Left$(dbCurr.TableDefs(intLoop).Name, 13) = "tbl_req_tmp_a" Then
      dbCurr.TableDefs.Delete dbCurr.TableDefs(intLoop).Name
    End If
  Next intLoop
  Set dbCurr = Nothing

ok, I'll try that. Have a question though. Why count backwards (via the "Step -1" code)? Why not "0 to dbCurr.TableDefs.Count -1" and count forwards?

Oh, and why the $ sign after the left? Does that force a string?

Thanks..

 

[HiTechCoach]

ok, I'll try that. Have a question though. Why count backwards (via the "Step -1" code)? Why not "0 to dbCurr.TableDefs.Count -1" and count forwards?

Oh, and why the $ sign after the left? Does that force a string?

Thanks..

To know why it counts backwards, read the "Quote" in my previous post. It has to do with position after the delete.

The Left$() is how it used to be done in previous versions. You can kinow use just Left().

 

[catbeasy]

To know why it counts backwards, read the "Quote" in my previous post. It has to do with position after the delete.

The Left$() is how it used to be done in previous versions. You can kinow use just Left().

ok, thanks..