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.D elete 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! 5 5099
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********@bar rlabs.com> wrote in message
news:40******** *************@n ews.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.D elete 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!
Try refreshing the tabledefs collection first:
Dim dbs As Database
Set dbs = CurrentDb
dbs.Tabledefs.R efresh
For each ...
Next
Arno R
"b b" <bb********@bar rlabs.com> schreef in bericht
news:40******** *************@n ews.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.D elete 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" <ar************ ****@tiscali.nl > wrote in
news:40******** **************@ dreader2.news.t iscali.nl: Try refreshing the tabledefs collection first:
Dim dbs As Database Set dbs = CurrentDb dbs.Tabledefs.R efresh 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********@bar rlabs.com> schreef in bericht news:40******** *************@n ews.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.D elete 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!
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_fo und As Boolean
Set tdfs = DBEngine(0)(0). TableDefs
Do
linked_table_fo und = False
For Each tdf In tdfs
If (tdf.Attributes And dbAttachedTable ) = dbAttachedTable Then
tdfs.Delete tdf.Name
linked_table_fo und = True
Exit For 'exit loop and start again from top
End If
Next tdf
tdfs.Refresh
Loop Until Not linked_table_fo und
End Sub
Ian.
"b b" <bb********@bar rlabs.com> wrote in message
news:40******** *************@n ews.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.D elete 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!
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******@bigpo nd.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_fo und As Boolean
Set tdfs = DBEngine(0)(0). TableDefs Do linked_table_fo und = False For Each tdf In tdfs If (tdf.Attributes And dbAttachedTable ) = dbAttachedTable Then tdfs.Delete tdf.Name linked_table_fo und = True Exit For 'exit loop and start again from top End If Next tdf tdfs.Refresh Loop Until Not linked_table_fo und End Sub
Ian.
"b b" <bb********@bar rlabs.com> wrote in message news:40******** *************@n ews.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.D elete 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!
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: michael |
last post by:
Gotta post because this is driving me nuts.
Trying to DELETE orphans. I can successfully:
SELECT GroupID FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE
UsersID IS NULL;
but when I try:
DELETE FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE UsersID
|
by: Robert McGregor |
last post by:
Hi all,
I've got a Front End / Back End database that was working just fine.
One day i opened the FE to find that if I tried to open one of the
linked tables from the database window, nothing happened (hourglass
for about 2 seconds then nothing).
I tried relinking the tables and got the same response. (Access even
completely bombed out once with a Dr Watson failure).
|
by: Michael Plant |
last post by:
Hello one and all.
I have a stored table in my database and the form I'm using is based on a
query that draws data from my stored table and a linked table. The linked
table is a *.txt file.
However, whenever I try to edit data I get the error message "Updating data
in a linked table is not supported by this ISAM." I can understand not being
able to edit the linked data, but the field I'm trying to update is drawn
from the stored...
|
by: DataB |
last post by:
Hi everyone!
I have a forms problem.
Bakground:
I have created a number of tables. Of these, I have a main parent
table (Personal Details) and a number of other child tables (Tax file
No., VISA card no.), etc......
All tables have a primary key of "Name", all child tables forming
|
by: Neil Ginsberg |
last post by:
I have ODBC linked tables to a SQL 7 database in an A2K database. The linked
tables do not have the password stored in them, so the first time the user
accesses them, they need to enter the SQL password.
I am developing a process that will automatically run at night which will
access those tables. I need to be able to give Access the password, as the
user currently does, so that the process can run without a password prompt
appearing....
| |
by: Alfonso Esteban Gonzalez Sencion |
last post by:
I am trying to use Access as a front end for extracting information from
an Oracle database. I started using linked tables but I am getting a
very curious behaviour.
When I consult the linked table in access, the total number of records
is OK but some records appear several times and some records do not
appear at all. It seems as if access or the ODBC drivers returns several
times the same record and skips some of the records, curiosly...
|
by: RBohannon |
last post by:
I'm using Access 2000. I have a DB with 19 tables, and I have just
split it. When I look in the back end, all 19 tables are there.
However, only 17 of them in the front end are actually linked to the
back end. When I look at the table objects in the front end, two of
them do not have arrows to indicate they are linked. These same two
tables do not show up in the Linked Table Manager.
If I open the tables in design view and look at...
|
by: pokerboy801 |
last post by:
OK, I will try to explain this as clearly and as concise as possible.
I am using Access, which has three MS Excel Linked tables, to store
call center metrics for reps. My Excel workbook has three separate
worksheets, all linked to Access. I have a query that performs some
minor calculations, not to hard, and will be ued to drive a report.
Here is my problem, I only have about 14 rows of data in each table as
we only have three reps. ...
|
by: Mike |
last post by:
I have a multiuser access database to which I have split into fe &
be. The system refreshes the links at each log-on between the fe & be
automatically via code.
PROBLEM:
Locally it runs perfectly... in the client server setting the re-
linking is extremely slow...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |