strange counter? 
Author Message
 strange counter?

Hello
anyone ever heard of a problem like this?:
a database app is run on a computer. There are very much operations
performed using DAO. DAO does read data from one (in fact many different)
database and adds new records to the app's database. These operations affect
always two tables, that have a 1:m relation, so that I will read from the
1.table in the database and write this record to the applications database
and then take the 1.table.ID/autoval/counter as a foreign key in the m.table
in the applications database, to which I will add records from the extern
database.
This seems to be nothing special. It works fine on my computer. However in
my customers environment (using the app on a laptop) it happened repeatedly,
that the autoval/index of the 1.table gets corrupted! It will simply stop at
a certain point. I can manually loop/ step through the records in the direct
window, using a recordset and move-commands. The records are very strangely
connected, suddenly from the middle of the table there is a 'jump' to the
end, from there is goes somewhere else, and so on.
Any idea, what might affect this? Is there not enough memory on the
customers computer (I guess its about 16 to 24MB RAM, laptop)? Any problem
know with cache?
Thanks for any help, idea...
regards
alex


Sat, 16 Jun 2001 03:00:00 GMT  
 strange counter?
I'm afraid I don't really understand what you are trying to say.
What do you mean when you say "the autoval/index of the 1.table gets
corrupted!"?
Are you aware that Autonumbers can produce "random" numbers?
It sounds to me like this is what is happening... (And it's quite normal and
acceptable behaviour as well)
Michael Gray
Adelaide, Australia

Quote:

>Hello
>anyone ever heard of a problem like this?:
>a database app is run on a computer. There are very much operations
>performed using DAO. DAO does read data from one (in fact many different)
>database and adds new records to the app's database. These operations
affect
>always two tables, that have a 1:m relation, so that I will read from the
>1.table in the database and write this record to the applications database
>and then take the 1.table.ID/autoval/counter as a foreign key in the
m.table
>in the applications database, to which I will add records from the extern
>database.
>This seems to be nothing special. It works fine on my computer. However in
>my customers environment (using the app on a laptop) it happened
repeatedly,
>that the autoval/index of the 1.table gets corrupted! It will simply stop
at
>a certain point. I can manually loop/ step through the records in the
direct
>window, using a recordset and move-commands. The records are very strangely
>connected, suddenly from the middle of the table there is a 'jump' to the
>end, from there is goes somewhere else, and so on.
>Any idea, what might affect this? Is there not enough memory on the
>customers computer (I guess its about 16 to 24MB RAM, laptop)? Any problem
>know with cache?
>Thanks for any help, idea...
>regards
>alex



Sun, 17 Jun 2001 03:00:00 GMT  
 strange counter?
to Michael/ Michaels question:

Thanks for your reply, Michael
I  try to explain:
There are two tables, lets say tableA and tableB, that exist in a Database E
and a Database P, which is also the application.
Now I read a record from tableA in E and add it as new record to tableA in
P. I do this with DAO-Objects, and after Recordset.AddNew, setting the
fields values, and Recordset.Update I perform a Recordset.MoveLast-Command.
Then I store the ID (or AutoValue? Sorry, I'm not used to the english
terminology) or Counters value to a variable, and use it as a foreign key
for many records I add as new records to tableB in P, using again other
fields from the according tableB in E. However, this does not work properly:
MoveLast sometimes (I dont know when or why so far) will move repeatedly to
the same record, using again and again this as foreign key in tableB in P.
This is what I called corrupted, because I looked like that to me. I even
could manually loop through a recordset, and see that Move-Commands move
very strangely through the records in the tableB.
I hope this explains the problem a little bit.
Kind regards.
alex.



Sun, 17 Jun 2001 03:00:00 GMT  
 strange counter?
I am still a little unsure of the exact nature of your problem, but
perhaps these comments will help.

EXPLANATION??? Using an autonumber as a keyfield can create problems,
because when you start moving record between tables autonumber re-assign
values in the primary key field.  For example, let's say you want to
copy a record from table A to table B.  Both tables have primary key
fields named ID# of the type autonumber.  When you copy the record with
ID# 5 from Table A to Table B, its ID# will probably change.  The
records new ID# will be generated by Table B's autonumber field, and be
equal to Table B's greatest ID# plus 1.  For this reason **Your primary
key field values are not stable when you transfer records.**  

Perhaps in your case, when you transfer records to a table with a
foreign key, it can't find the foreign key value it is looking for and,
due to referential integrity, does not allow the records to be copied.

SOLUTION????  I would suggest not using autonumber for a primary key
field.  Instead, use long integer, and set the default value to
=GenUniqueID().  This way as records are added, they will automatically
be assigned a unique value, and that value will remain stable.

--
Tenbroeck G. Smith MA, Project Coordinator
Rollins School of Public Health - Emory University
1462 Clifton Road, Room 513
Atlanta, GA 30322
Work: (404) 727-8450.  Fax:  (404) 727-7261



Mon, 18 Jun 2001 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Stranger In A Strange Land...

2. Strange Strange Error

3. Help with displaying a counter

4. Passing Counter to Procedure

5. String counter?

6. Report counter

7. Progress Counter

8. Dynamic Counter in a Table or a Query to Count Records (Q199679)

9. Counter

10. Very Simple Counter

11. How to Simulate a Dynamic Counter in a Table or a Query to Count Records

12. Counter field in Access 2000

 

 
Powered by phpBB® Forum Software