Deleting tables in VBA

[Ian on Cowan]

Can anyone help with this one? I have a subroutine, shown below. It should
delete the linkes to tables in a back end Access DB, leaving one table (not
linked) "version" in the front end Access program. It does this BUT will not
delete any tables with names beginning with O or further above in the
alphabet. It behaves the same way without the If... then clause, and the same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub

 

[Douglas J. Steele]

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name

 

[Ian on Cowan]

Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can anyone help with this one? I have a subroutine, shown below. It should
delete the linkes to tables in a back end Access DB, leaving one table
(not
linked) "version" in the front end Access program. It does this BUT will
not
delete any tables with names beginning with O or further above in the
alphabet. It behaves the same way without the If... then clause, and the
same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub

 

[Douglas J. Steele]

What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can anyone help with this one? I have a subroutine, shown below. It
should
delete the linkes to tables in a back end Access DB, leaving one table
(not
linked) "version" in the front end Access program. It does this BUT
will
not
delete any tables with names beginning with O or further above in the
alphabet. It behaves the same way without the If... then clause, and
the
same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub

 

[Ian on Cowan]

It correctly skips over the table "version", and on the next loop goes to the
error from the delete line, whichever of db.Execute "Drop Table [" & Tbl.Name
& "]" or db.TableDefs.Delete Tbl.Name is used. I notice too that stepping
through, the value of lngLoop starts at 15, although there are only 8 tables
linked.

What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Can anyone help with this one? I have a subroutine, shown below. It
should
delete the linkes to tables in a back end Access DB, leaving one table
(not
linked) "version" in the front end Access program. It does this BUT
will
not
delete any tables with names beginning with O or further above in the
alphabet. It behaves the same way without the If... then clause, and
the
same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub

 

[Douglas J. Steele]

Ooops. My fault. While you may think that there are only 8 tables, Access
always creates a number of System tables. (To see them, select to show
System Objects on the View tab under Tools | Options)

Since you're dealing with linked tables, try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" And _
Len(Tbl.Connect) > 0 Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative that will work even with local tables is

If Tbl.Name <> "version" And _
(Tbl.Attributes And dbSystemObject) = 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It correctly skips over the table "version", and on the next loop goes to
the
error from the delete line, whichever of db.Execute "Drop Table [" &
Tbl.Name
& "]" or db.TableDefs.Delete Tbl.Name is used. I notice too that stepping
through, the value of lngLoop starts at 15, although there are only 8
tables
linked.

What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

:

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Can anyone help with this one? I have a subroutine, shown below. It
should
delete the linkes to tables in a back end Access DB, leaving one
table
(not
linked) "version" in the front end Access program. It does this BUT
will
not
delete any tables with names beginning with O or further above in
the
alphabet. It behaves the same way without the If... then clause, and
the
same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub

 

[Ian on Cowan]

Many thanks, that last one does the job. I should have realised it would
count the system tables too. Once again, many thanks
Ian

Ooops. My fault. While you may think that there are only 8 tables, Access
always creates a number of System tables. (To see them, select to show
System Objects on the View tab under Tools | Options)

Since you're dealing with linked tables, try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" And _
Len(Tbl.Connect) > 0 Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative that will work even with local tables is

If Tbl.Name <> "version" And _
(Tbl.Attributes And dbSystemObject) = 0 Then

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It correctly skips over the table "version", and on the next loop goes to
the
error from the delete line, whichever of db.Execute "Drop Table [" &
Tbl.Name
& "]" or db.TableDefs.Delete Tbl.Name is used. I notice too that stepping
through, the value of lngLoop starts at 15, although there are only 8
tables
linked.

What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

:

Try:

Private Sub Form_Close()
On Error GoTo HandleErr

Dim db As Database
Dim Tbl As DAO.TableDef
Dim lngLoop As Long

Set db = CurrentDb()
For lngLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set Tbl = db.TableDefs(lngLoop)
With Tbl
If Tbl.Name <> "version" Then
db.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl

ExitHere:
Set Tbl = Nothing
Set db = Nothing
Exit Sub

HandleErr:
MsgBox Err & ":Err.Description", , _
"Err in Deleting Links & [" & Tbl.Name & "]"
Resume ExitHere

End Sub

An alternative to db.Execute "Drop Table [" & Tbl.Name & "]" would be

db.TableDefs.Delete Tbl.Name


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Can anyone help with this one? I have a subroutine, shown below. It
should
delete the linkes to tables in a back end Access DB, leaving one
table
(not
linked) "version" in the front end Access program. It does this BUT
will
not
delete any tables with names beginning with O or further above in
the
alphabet. It behaves the same way without the If... then clause, and
the
same
way if I run it stand alone in a module.

I am completely puzzled by this bizarre behaviour; any ideas?

Private Sub Form_Close()
Dim db As Database
Set db = CurrentDb()
Dim Tbl As DAO.TableDef
On Error GoTo HandleErr
For Each Tbl In CurrentDb.TableDefs
With Tbl
If Tbl.Name <> "version" Then
CurrentDb.Execute "Drop Table [" & Tbl.Name & "]"
End If
End With
Next Tbl
ExitHere:
Set Tbl = Nothing
Exit Sub
HandleErr:
MsgBox Err & ":Err.Description", , "Err in Deleting Links & [" &
Tbl.Name & "]"
Resume ExitHere
Resume
End Sub