Import data according to table relatioship order

[skmwenda]

I am trying to create a generic importation VBA function in access database.
(I will link the external tables of a similar database and then import their
data into the local tables.)

For starters, the function should get a list of the tables in the local
database, ordered by their primary/foreign key, so as to allow importation
based on table relationship rules.

Therefore i am trying to do something similar to the following:
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=50 , but in
access VBA.

I therefore need help getting a list of the local tables, ordered in such a
way as to allow inserts (the tables with primary keys to be listed before
those with corresponding foreign keys)

Kindly help

 

[Marshall Barton]

I am trying to create a generic importation VBA function in access database.
(I will link the external tables of a similar database and then import their
data into the local tables.)

For starters, the function should get a list of the tables in the local
database, ordered by their primary/foreign key, so as to allow importation
based on table relationship rules.

Therefore i am trying to do something similar to the following:
http://education.sqlfarms.com/education/ShowPost.aspx?PostID=50 , but in
access VBA.

I therefore need help getting a list of the local tables, ordered in such a
way as to allow inserts (the tables with primary keys to be listed before
those with corresponding foreign keys)

You can get a list of the tables by looping through the
TableDefs collection. If a TableDef's Connect property
contains something, it is a linked table.

That's the easy part. Following a tree of relationships is
an extremely complex problem that would require a LOT of
very complex VBA code. The Relationships and possibly many
tables' Indexes collection would also get involved while
trying to calculate a value that could be used to sort the
table in the desired order.

I tried to put together such an algorithm and code several
years ago and I think I got it to work for my specific
situation. But, the algorithm was so complex that just a
few months later, I could not follow the convoluted logic
well enough to even test to see if it worked in a slightly
different situation. I gave up on the whole mess and just
created a little table of table names and a field where I
could enter the order the tables should be added/imported.

 

[skmwenda]

Thanks Marshall for the helpful insight. If possible, could you send me the
sorting part of your code, so I may look at it when am feeling brave ?

 

[Marshall Barton]

Thanks Marshall for the helpful insight. If possible, could you send me the
sorting part of your code, so I may look at it when am feeling brave ?

Ok. but I will need an email address to send it.

 

[skmwenda]

Ok. but I will need an email address to send it.