Replication changes Record Order in tables?

[ions]

I decided to learn about replication today and I made a master database.

To my surprise, I noticed the records in the tables in the Master version are not in the same order as the original version. The records are now in random order but before they were in the order of entry.

Now i have to create new queries for all the forms that have a table as a record source just to get the record ordering right.

I want to know how reliable is Access Replication and should I expect a lot more suprises like this. Is replication more trouble than it's worth?

 

[ions]

I figured out that replication randomizes autonumber fields which now produces a new problem. The new numbers are not in sequence and can even be negative.

I was hoping to use replication (currently sales guys use slow PC Anywhere to update DAO system) but now starting to realize that I would have to redesign it tremendously.

Am I correct in coming to the conclusion that Replication has to be considered at the start of development and is not really feasible after the completion of major development?

How complicated is it to convert a database based on DAO to ADO and allow outside connectivity for my sales guys?

Thank YOu

 

[Pat Hartman]

1. If you have used autonumbers as visible numbers such as StudentID or CustomerID, replication will cause issues with the autonumbers being reassigned as you saw. You need to use autonumbers only for joining. IDs which you let people see, need to be generated via a code routine. There are lots of samples here that show how to use DMax() to find the highest number assigned so far.
2. With a relational table, there is no implied promise as to a particular row order since tables are unordered sets. In small tables it often "appears" that there is a fixed order since Access reorders the table into key sequence each time you compact but updated records or records added out of sequence can "move" between compacts. You ALWAYS need to use a query with an order by clause to assure a particular order.
3. You shouldn't need to convert from DAO to ADO even if you are changing from a Jet back end to something else such as SQL Server. If you have a very large database, you may see some performance improvements with ADO for linked tables but most Access applications simply don't have large enough tables to be a problem.