Consolidating Access files.

[Guest]

I have five databases with identical tables structures that need to be consolidated into one. The databases are large (~20mb each) and some of the tables (27 tables) are large (15,000 records). When I apped using VBA with SQL I get messages that records have not been appended when in fact they are, messages that say the append was successful when it was not and messages of key violations. These errors do not occur consistently. It appears as though the appends are successful early in the session but become more common as the session procedes

What causes these problems and how do I fix it

 

[Allen Browne]

Hi Richard

There are many issues that could arise when importing related data, e.g.:
- If you used AutoNumbers as primary keys, the 5 databases probably have
shared numbers, which will fail.

- If a field (or combination of fields) is "Indexed (No Duplicates"), the
import will fail when the 2nd and subsequent ones arrive.

- The order of import will matter, e.g. if you have relationships with
referential integrity, import the related records first may fail.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have five databases with identical tables structures that need to be

consolidated into one. The databases are large (~20mb each) and some of the
tables (27 tables) are large (15,000 records). When I apped using VBA with
SQL I get messages that records have not been appended when in fact they
are, messages that say the append was successful when it was not and
messages of key violations. These errors do not occur consistently. It
appears as though the appends are successful early in the session but become
more common as the session procedes.

 

[John Vinson]

I have five databases with identical tables structures that need to be consolidated into one. The databases are large (~20mb each) and some of the tables (27 tables) are large (15,000 records). When I apped using VBA with SQL I get messages that records have not been appended when in fact they are, messages that say the append was successful when it was not and messages of key violations. These errors do not occur consistently. It appears as though the appends are successful early in the session but become more common as the session procedes.

What causes these problems and how do I fix it?

What are the Primary Keys of these tables? If any of the tables have
Autonumbers - even random ones - then it is VERY likely that two
different records in different databases have been assigned the same
primary key, and you will get this error when you try to combine them.
If the autonumbers are sequential it's a *certainty* that there will
be duplicates.

This can be a very tedious job in the best of cases; identifying which
records are identical except for their PK, or have identical PK's but
different data, or (worst) identical PK's and *not quite identical*
data require advance purchases of large stocks of midnight oil and
headache remedies.

Good luck!