Problem deleteing linked tables

[b b]

I created the following code to delete all linked tables in my database
(Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and
deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked
tables.

Thus, in order to delete all linked tables I have to execute this code
several times.

My question is, since I'm using the connection string to determine if
the table is a linked table, then why does it return true some times and
false on other times for the same table? I'm assuming this is what is
happening as it is the only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jeremy B. Shapiro]

b b,

When you delete the table, it removes it from the collection, so when you go
to the next table, you skip one.

Let's say you have four tables:
Table 1
Table 2
Table 3
Table 4

You delete table 1, and then move to table 2, but since you deleted table 2,
table 3 is now table 2, and table 2 is now table 1, and you've just skipped
past table 2. So if you run your code on these four tables, it'll delete
table 1 and table 3 and leave table 2 and 4. Run it again and you'll delete
table 2 and leave table 4. Run it a third time, and you'll get rid of table
3.

Make sense? :o)

--
Jeremy Shapiro
Asandia, Corp.
www.asandia.com
1.866.ASANDIA (272.6342)
"b b" <bb********@barrlabs.com> wrote in message
news:40*********************@news.newsgroups.ws...

I created the following code to delete all linked tables in my database
(Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and
deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked
tables.

Thus, in order to delete all linked tables I have to execute this code
several times.

My question is, since I'm using the connection string to determine if
the table is a linked table, then why does it return true some times and
false on other times for the same table? I'm assuming this is what is
happening as it is the only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Arno R]

Try refreshing the tabledefs collection first:

Dim dbs As Database
Set dbs = CurrentDb
dbs.Tabledefs.Refresh
For each ...
Next

Arno R

"b b" <bb********@barrlabs.com> schreef in bericht
news:40*********************@news.newsgroups.ws...

I created the following code to delete all linked tables in my database
(Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and
deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked
tables.

Thus, in order to delete all linked tables I have to execute this code
several times.

My question is, since I'm using the connection string to determine if
the table is a linked table, then why does it return true some times and
false on other times for the same table? I'm assuming this is what is
happening as it is the only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

"Arno R" <ar****************@tiscali.nl> wrote in
news:40**********************@dreader2.news.tiscal i.nl:

Try refreshing the tabledefs collection first:

Dim dbs As Database
Set dbs = CurrentDb
dbs.Tabledefs.Refresh
For each ...
Next

Arno R

I seem to recall that this is an issue with the bookmark bug.
Essentially when the record is deleted, the pointer index
increments by one but the records move down by one, leaving
undeleted records.

Try coding a for-next that starts at tabledefs.count and working
backwards to 0

Bob Quintal

"b b" <bb********@barrlabs.com> schreef in bericht
news:40*********************@news.newsgroups.ws...

I created the following code to delete all linked tables in
my database (Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of
tables and deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the
linked tables.

Thus, in order to delete all linked tables I have to execute
this code several times.

My question is, since I'm using the connection string to
determine if the table is a linked table, then why does it
return true some times and false on other times for the same
table? I'm assuming this is what is happening as it is the
only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ian Hinson]

Each time you delete a linked table you muck up the 'For Each' clause
because the number of items in the collection changes.

The code below is more predictable because it only does one deletion with
each pass through the collection.
If anything, it's overly cautious, but reliable.

Public Sub DetachAllTables()
Dim tdf As TableDef
Dim tdfs As TableDefs
Dim linked_table_found As Boolean

Set tdfs = DBEngine(0)(0).TableDefs
Do
linked_table_found = False
For Each tdf In tdfs
If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
tdfs.Delete tdf.Name
linked_table_found = True
Exit For 'exit loop and start again from top
End If
Next tdf
tdfs.Refresh
Loop Until Not linked_table_found
End Sub

Ian.

"b b" <bb********@barrlabs.com> wrote in message
news:40*********************@news.newsgroups.ws...

I created the following code to delete all linked tables in my database
(Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and
deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked
tables.

Thus, in order to delete all linked tables I have to execute this code
several times.

My question is, since I'm using the connection string to determine if
the table is a linked table, then why does it return true some times and
false on other times for the same table? I'm assuming this is what is
happening as it is the only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[Douglas J. Steele]

It's much simpler to go through the collection from the end:

Public Sub DetachAllTables()
Dim tdf As TableDef
Dim tdfs As TableDefs
Dim intLoop As Integer

Set tdfs = DBEngine(0)(0).TableDefs
For intLoop = (tdfs.TableDefs.Count - 1) To 0 Step -1
Set tdf = tdfs(intLoop)
If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
tdfs.Delete tdf.Name
End If
Next intLoop

End Sub
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Ian Hinson" <pp******@bigpond.net.au> wrote in message
news:9e******************@news-server.bigpond.net.au...

Each time you delete a linked table you muck up the 'For Each' clause
because the number of items in the collection changes.

The code below is more predictable because it only does one deletion with
each pass through the collection.
If anything, it's overly cautious, but reliable.

Public Sub DetachAllTables()
Dim tdf As TableDef
Dim tdfs As TableDefs
Dim linked_table_found As Boolean

Set tdfs = DBEngine(0)(0).TableDefs
Do
linked_table_found = False
For Each tdf In tdfs
If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then
tdfs.Delete tdf.Name
linked_table_found = True
Exit For 'exit loop and start again from top
End If
Next tdf
tdfs.Refresh
Loop Until Not linked_table_found
End Sub

Ian.

"b b" <bb********@barrlabs.com> wrote in message
news:40*********************@news.newsgroups.ws...

I created the following code to delete all linked tables in my database
(Access 200):

--------------------------------------------------------
Dim tbl As TableDef

Dim dbs As Database
Set dbs = CurrentDb

For Each tbl In dbs.TableDefs
If Len(tbl.Connect) > 0 Then ' Is a linked table
Debug.Print "Deleting table " & tbl.name & " ... "
dbs.TableDefs.Delete tbl.name
End If
Next tbl

---------------------------------------------------------

When I execute the code it goes through the collection of tables and
deletes "some" of the linked tables.

If I execute the code again, it again deletes "some" of the linked
tables.

Thus, in order to delete all linked tables I have to execute this code
several times.

My question is, since I'm using the connection string to determine if
the table is a linked table, then why does it return true some times and
false on other times for the same table? I'm assuming this is what is
happening as it is the only thing that makes sense.

Thanks for your help.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!