Table trouble after compacting

[Julie]

I have a back-end with three tables. Two tables are
children to the third table - i.e. they are linked with
referential integrity.

Data is inserted in the front-end through VBA. First a
record is inserted in the parent table, then the records
are inserted in the two child-tables with the same key as
just "issued" (with autonumber) in the parent table.

It works well.

Then I just compacted the back-end - and suddenly my code
breaks at the point where I want to insert the key in one
of the child tables....

What is wrong?

 

[Armen Stein]

I have a back-end with three tables. Two tables are
children to the third table - i.e. they are linked with
referential integrity.

Data is inserted in the front-end through VBA. First a
record is inserted in the parent table, then the records
are inserted in the two child-tables with the same key as
just "issued" (with autonumber) in the parent table.

It works well.

Then I just compacted the back-end - and suddenly my code
breaks at the point where I want to insert the key in one
of the child tables....

What is wrong?

Hi Julie,

How does the code break? A duplicate key?

There was an autonumber bug in Access a while back, where Access would
attempt to reuse key values, resulting in attempted duplicates. Make
sure you have the latest service packs for Access and Jet.

Problem is described in KB article 257408:
http://support.microsoft.com/default.aspx?scid=kb;en-us;257408

Without knowing more, that's all that occurs to me...

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[John Vinson]

Then I just compacted the back-end - and suddenly my code
breaks at the point where I want to insert the key in one
of the child tables....

What is wrong?

Either the compaction or the code... <g>

Please post the code. Compacting will reset the next autonumber; if
your code is making assumptions about what the next autonumber value
will be, this may be the root of the problem.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Julie]

It is not a "dublicate key" issue - when I insert the
parent record I get a new key and use that in the child
tables.

When trying to make the insert, the child key already has
a value...?!

But - it seems that relinking solves the problem.