Deleting tables in VBA

G

Guest

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
 
D

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
 
G

Guest

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

Ian on Cowan

Douglas J. Steele said:
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)


Ian on Cowan said:
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
Click to expand...
Click to expand...
 
D

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)


Ian on Cowan said:
Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

Douglas J. Steele said:
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)


Ian on Cowan said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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.



Douglas J. Steele said:
What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian on Cowan said:
Doug,
Many thanks, but it always goes to HandleErr. Am I missing something?

Ian on Cowan

Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 
D

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!)


Ian on Cowan said:
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.



Douglas J. Steele said:
What error do you get? What line of code is causing the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ian on Cowan said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Douglas J. Steele said:
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!)


Ian on Cowan said:
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.



Douglas J. Steele said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting rid of dbo_ in tables names of linked tables 2
Creating a table with all the "object dependancies" 4
Removing OrderBy from Tables and Queries in VBA 2
Code to delete/unlink Linked tables 2
Iterating through tables 1
Delete Table Function Not Working 1
Error 3251 using ADO & VBA to drop a table! 13
Linked tables using ADO 2

Top