Hello,
I'm converting an application from MS Access to MSDE (most likely as
compared to SQL S Express) and I have a few design questions.
Background:
I'm dealing with a disconnected scenerio in which inspectors go out into the
field, generate reports and then come back into the office. Back at the
office, an office assistant takes their laptops and updates the main Access
database (via code in the application). Then, in the morning, after the main
database has been updated by all of the laptops, the office assistant
updates the laptops with fresh data from the main database. It sort of has
to be done this way or the Report ID's can get all screwed up and they wind
up working on each other's reports.
In the Access database, the main table has an autonumber field that is used
as the report ID for that table. I would like to us replication, if that is
appropriate in this situation, but am not exactly sure how that will work.
When the main database is updated from the satellite laptops, new records
are identified by a field having been set in this table and modified records
are identified in the same fashion. VB code does all of the updating.
So, in MSDE, what sort of field do I use to generate this unique key, like
Access does the auto number field. I've heard that IDENTITY fields aren't
that great to use in SQL Server.
I'm assuming, if I use replication, that it will be merge replication. And,
having that in mind, how do I handle this Report ID field on the laptops and
then with the main database too. Because, while out in the field, the
laptops will likely be generating their own Report ID but there has to be
concurrancy. How do I make sure that the Report ID fields are valid on the
laptops as:
Laptop A generates Report ID 999 and then Laptop B, while also disconnected,
also generates Report ID 999. The database has to be in a valid state.
I hope I've been clear and that I can get some input on the design issues
here.
Thanks in advance
Steve
