Create Tables, remove tables, set relations etc.

[hgeron]

I am using Access VB and recordsets to create tables, and set relationships
between tables. When a new dataset is loaded, I need to remove tables and
begin again. There will be differnt tables and different field names. I want
to programatically remove most of the tables, but the delete object fails
when tables are related, so I tryed to use Alter Tables to remove the
relationships first, but
that failed also because a related table existed. How do you delete these
table objects when a relationship exists?

 

[John Vinson]

I am using Access VB and recordsets to create tables, and set relationships
between tables. When a new dataset is loaded, I need to remove tables and
begin again. There will be differnt tables and different field names. I want
to programatically remove most of the tables, but the delete object fails
when tables are related, so I tryed to use Alter Tables to remove the
relationships first, but
that failed also because a related table existed. How do you delete these
table objects when a relationship exists?

Delete the relationships first. Here's a small nuclear device that
blows away all relationships in an entire database; use with caution
or adapt to your needs:

Sub KillAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim inti As Integer
Set db = DBEngine(0)(0)
For inti = db.Relations.Count - 1 To 0 Step -1
Set rel = db.Relations(inti)
Debug.Print "Deleting relation "; rel.Name, rel.Table, _
rel.ForeignTable
db.Relations.Delete rel.Name
Next inti
End Sub

Once this is done, delete your tables.

John W. Vinson[MVP]

 

[hgeron]

Wow!

It worked for me. Thanks a bunch

 

[David Cox]

Not the answer to your question, and a kludge at that, but you could put the
tables with a short shelf life into a separate database and link them. If
you then replace that database using the same name for the new one, and put
it into the same location any queries using the same table names and fields
will still work. If you use a different name or location for the new
database then the linked table manager will stll be able to link to tables
with the same name.

 

[hgeron]

Thanks, but eventually these won't be short life tables. These will be
tables that
will be the results of an XML file. Data will be reviewed and updated. Then
writtened back to an XML file. The XML input will be one of several methods
of file input methods to construct the final product.