The auto-incrementing ID field becomes radom number (1 Viewer)

[youyiyang]

Hi! everbody,

I made a replica of my original access database. However, when I add records in a table in my original database, the auto-incrementing ID field becomes a very big or small radom number.
Here is:

2377
2378
2379
2380
1242611267
(auto incrementing)

This would leads error in filling records. So, would you please give me a clue to turn the ID to original style?

Any comments would be appreciated!

 

[branston]

How strange! There is probably a much better way than doing it, but what i would do is simple delete that column in its table and then re-enter it with the same name. This would 're-set' the auto number to 1 but if its named the same it would be picked up as it was.
Hope this helps!

 

[neileg]

Autonumbers can do some strange things. That's why it is not recommended to use them for anything with meaning. Have a look at the properties for your field in the table design. Should be set to Increment but it may have changed to Random.

 

[gemma-the-husky]

I am not 100% sure, as I never use replication

But if you really instituted replication (you say a replica) I think thats what happens.

The idea is that multiple SEPARATE systems can use replicas, and then get brought together without clashes. Access uses a system of allocating replication IDs (autonumbers?) in a way that means the likelihood of clashes is almost eliminated. You may be seeing the effect of this.

In any event an AUTONUMBER field cannot be guaranteeed to maintain any sequence.

 

[Dennisk]

that is what it is supposed to do when replicating. The auto number is generated using a randomizing algorith, so that any other databases in the replication group do not generate the same number.

It's really irrelevant what the number is so long as its unique.

 

[youyiyang]

Thank you all for your quick response.

Yes, the original table design of ID is incremental and not radom. After I made replicaiton the property of ID is changed to random and that is why it shows so strange.

The solution is just as branston said, delete this column and re-enter it with same name and it is changed to incremental number.

 

[gemma-the-husky]

that may be a sort of solution, but the point is, is that the number is generally used as a foreign key in other tables (otherwise therei s little ponit in an autonumber) - therefore you cannot just change it without considering the consequences.

 

[youyiyang]

yes, gemma-the-husky, it would be trouble if the number is used as a foreign key in other tables. It is lucky for me to do it since my database design is so simple that there is only one main big table. hehe.

 

[gemma-the-husky]

but it begs the question - if it isnt used as a foreign key - there is no point in having this number at all.

to be honest, a database with one table is not really a database at all, more likely a spreadsheet.

 

[dfenton]

You should never set an Autonumber primary key to INCREMENT in a replica, because you'll end up with collisions between replicas (i.e., the next increment added in both replicas). If you're not editing in two different replicas, then there's not much point to have replication.

In other words, if your app actually works with a replicated table that has an incrementing Autonumber PK, then you obviously aren't actually using replication, and it shouldn't be replicated in the first place.

But if you *are* using replication, the increment will never ever work.

 

[wiklendt]

to be honest, a database with one table is not really a database at all, more likely a spreadsheet.

isn't it actually a 'flat file' database?

 

[youyiyang]

The situation is like this: my boss use access to deal with customer relationships and the original database design is just one big table. I am programming asp webpages to show queries in this database. My colleague is using access to fill records. Therefore I met the problem that if I retrieve data from access table through asp then the asp page can not open because my colleague is using access. So I decide to replicate the database and use asp to retrieve data from a replica.

 

[Atomic Shrimp]

isn't it actually a 'flat file' database?

I guess it's not much more than, say a file-based table such as a .dbf

I can see why someone might choose to create a single flat table in Access in preference to, say, Excel - Excel isn't a proper database (for example: integrity of rows isn't enforced - it's possible to sort just one column independently of the others) - and although I'm sure there's probably a way of autonumbering new rows in Excel, I bet it's something that a data entry person could destroy more readily than in Access.

 

[dfenton]

The situation is like this: my boss use access to deal with customer relationships and the original database design is just one big table. I am programming asp webpages to show queries in this database. My colleague is using access to fill records. Therefore I met the problem that if I retrieve data from access table through asp then the asp page can not open because my colleague is using access. So I decide to replicate the database and use asp to retrieve data from a replica.

Eh? That still doesn't make any sense. If you made a copy and then copy data from the copy into the MDB behind the ASP app, then that would make sense, but wouldn't require replication.

There isn't actually any reason why a properly-written ASP app would lock the database if all it's doing is retrieving and display the results of queries -- that's a read-only operation and shouldn't put write locks on the database.

Likewise, you don't say how you're synchronizing the replicas. Your response implies that you're using ASP to initiate the synch, but that sounds unlikely since it would require some fairly high-level programming, and you sound to me like something of a novice (I could be wrong on that).

I still wouldn't recommend an incremental Autonumber in any replica because sooner or later it's going to bite you in the nether parts. It oughtn't matter in the first place, unless you're completely mis-using Autonumber fields.

 

[youyiyang]

Eh? That still doesn't make any sense. If you made a copy and then copy data from the copy into the MDB behind the ASP app, then that would make sense, but wouldn't require replication.

Yes, I can make a copy to instead the replication,but since replication can also achieve the same goal why I do not use it. The database design is simple, my colleagues don't need replication to work together, so it is just a tool to be used to share database records to asp apps.

There isn't actually any reason why a properly-written ASP app would lock the database if all it's doing is retrieving and display the results of queries -- that's a read-only operation and shouldn't put write locks on the database.

It is not the asp app that lock the database but working on access database will prevent asp app to retrieving data since directly working on access will lock the database and asp app cannot open. I think you also have the same experience.

Likewise, you don't say how you're synchronizing the replicas. Your response implies that you're using ASP to initiate the synch, but that sounds unlikely since it would require some fairly high-level programming, and you sound to me like something of a novice (I could be wrong on that).

Synchronizing is acheived through access form. I use Timer event to periodically synchronize at a certain time.
dbsTemp.Synchronize "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\nbo_replica.mdb", dbRepExportChanges

I still wouldn't recommend an incremental Autonumber in any replica because sooner or later it's going to bite you in the nether parts. It oughtn't matter in the first place, unless you're completely mis-using Autonumber fields.

Maybe you are right that I don't need to use incremental autonumber in replica. This is the first time I meet replication in access so I feel odd when I see random number in autonumber field. Maybe it is good for me to let it be.

 

[dfenton]

Yes, I can make a copy to instead the replication,but since replication can also achieve the same goal why I do not use it.

Sorry, but I'm confused. Did you attempt to use replication or not? If copy works fine, why bother with replication, which introduces a host of issues, not least of which is randomizing autonumbers.

The database design is simple, my colleagues don't need replication to work together, so it is just a tool to be used to share database records to asp apps.

This is not the designed purpose of Jet replication.

It is not the asp app that lock the database but working on access database will prevent asp app to retrieving data since directly working on access will lock the database and asp app cannot open.

If you have a split Access app, data tables in back end, forms/reports/etc. in front end, there is absolutely no reason that editing the data from Access should lock out ASP. If it's not split, that's entirely a different issue.

I think you also have the same experience.

No, I have not. First off, I've never written an ASP app (I use PHP for web apps, and have only once use a Jet database for the datastore for that). I have, in fact, had good luck with Cold Fusion and sharing a Jet back end, but there was only one read/write user of the Jet database (and all the web users were read-only).

Synchronizing is acheived through access form. I use Timer event to periodically synchronize at a certain time.
dbsTemp.Synchronize "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\nbo_replica.mdb", dbRepExportChanges

Is that the real path? That's likely to be a major issue, as that's not normally a writable location. Secondly, if your app is unsplit, you should split it so that you remove that issue from the mix.

Maybe you are right that I don't need to use incremental autonumber in replica. This is the first time I meet replication in access so I feel odd when I see random number in autonumber field. Maybe it is good for me to let it be.

Autonumbers should never be used as meaningful data, since they can easily have gaps. They are just meaningless surrogate keys used for linking records between tables. If you care what the numbers are then you shouldn't be using Autonumbers at all -- you should be coding the assignment of the values in that field yourself.

 

[youyiyang]

My colleague fills records by using quering. This is not a splitted model as dfenton said. It is better for me to use splitted access app.