VBA, strange counter?

strange counter?

Author	Message
alex #1 / 4	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

Michael Gra #2 / 4	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

alex #3 / 4	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

Tenbroeck Smit #4 / 4	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

Page 1 of 1

[ 4 post ]