creating a database subset in vba

[Guest]

My database contains information about different organizations in several
relational tables. I have created a utility that allows the user to select
one or more organization(s). The code then copies the entire back end
database, deletes all records from all tables that do not apply to the
selected organization(s), compacts this filtered copy of the back end, and
then zips it up. The motivation is the ability for organizations to share
their portion of data with each other in an efficient way. I should mention
that these organizations are entirely independent and are not required to
communicate with each other and there is no centralized repository so
database replication does not suit our needs.

All of this is working code. My question is whether it is the best way to
do things.

In general the subset of data will be much larger than the original
database, so would it be better to start with a blank back end and just
append the data requested rather than starting with a full back end and
deleting the data not requested?

Assuming it is better to start with a blank back end, how do I create a copy
of the entire back end structure (including relationships, indexing, etc)
from code? Can it all be obtained by ADOX catalog? Does anyone have VBA
code for copying the structure of a back end database into a new blank
database?

I should note that the back end distributed with the database contains 2
sample organizations with data so it can not be used as a template. Should I
give up on copying the structure programmatically and just resign myself to
adding a blank back end template database with each distribution?

Thanks for any thoughts and comments.

 

[Allen Browne]

If you want an alternative, it should not be too difficult to create a copy
of the back end, and delete the 2 sample orgs so you can use it as a
template. It would then be just a matter of populating the tables and
distributing this copy.

But if you already have your copy'n'delete process working fine, I don't see
a problem with that.

Trying to recreate all the tables programmatically is a pain. You must use
DAO to get all the properties of the fields and tables, but you cannot
create Decimal fields with ADO, so that requires ADOX as well. It's a real
dog's breakfast to try to approach it that way. And there is no way to get
all the proerties right with just DDL, so copying the file will be much
easier.

 

[Guest]

I did spend some time playing with ADOX to copy the structure
programmatically. I can create the tables but the ADOX Columns collection
seems to be arranged alphanumerically rather than in the order the columns
are displayed in the Access table and I can't identify any property of the
column that indicates the proper display order. That means all of the tables
in my database copy have the columns rearranged. Indexing was easily
transfered by looping through the Indexes collection. But I have not been
able to create any of the relationships by looping through the Keys
collection. I keep getting an error when I try to append the key. It seems
close but probably not worth any more time. I could have manually created a
stripped out blank template in far less time than I have already spent
pursuing this "elegant solution".

 

[Allen Browne]

Yes, I agree with your conclusion.

There is no way in ADOX to do things like setting the DisplayControl of the
field (to get a check box for Yes/No Fields), or the Format or Caption
properties of the field. That's why I say you have to use both ADOX and DAO
together to do everything.