C
Caimari
Hi,
I have an Access 2003 database that I am running on a desktop and a mobile
PC running in Windows Mobile 2005. The mobile database is built using Visual
CE (VCE) software by Syware (www.syware.com). I download the Access tables
to the PDA and build forms over them in VCE to enter data. I then
synchronize the databases using the VCE synchronization function (via
ActiveSync 4.0). Both run fine independently, but I'm having problems with
synchronization. I'm posting this to the tables forum (as well as the forms
forum) because I think the solution involves new code in my Access form that
populates the table (see below). Sorry about the lenghty description, but my
other efforts at solving this issue have all ended eventually up in detailed
descriptions, so I'm posting it all up front.
Database Structure:
The primary database structure consists of two related tables that are used
to record "general" and "detailed" observations: tblObsGen and tblObsDet.
The relationship is built on a one-to-many relationship using the field
ObsID, which is the primary key in tbl_tblObsGen and is a foreign key in
tblObsDet. In tblObsDet the primary key is the field DetID. So for each
general observation recorded as a unique ObsID record in tblObsGen, there can
be one or more detailed observations recorded in tblObsDet (each having their
own unique ID indexed with no duplicates in the field DetID). Both of the
fields ObsID and DetID are indexed autonumber fields set to increment (as
opposed to random) that allow no duplicates. In Access I use a single form
(containing several subforms) to enter data into tblObsGen and tblObsDet.
Problem Description:
I built the database(s) and forms on the assumption that I could specify
separate ranges for unique (autonumber) IDs in the desktop and handheld
databases that would prevent duplication of autonumber Unique IDs in the
primary and foreign keys. This turns out not to be true. I've tested the
two possible scenarios for specifying separate ranges for Autonumber fields
for Obs_ID and Det_ID as follows:
1) The VCE form is assigned the high range (e.g. start the autonumber
control in VCE at 100000. I then reset the autonumber field in Access to
start at one. I then enter records 1-10 on the desktop and records
100000-100010 on the PDA. When I synch the database the records come in fine
from the mobile device with two ranges intact. However, when I then enter
data on the desktop device it starts the autonumber field at the next
increment after the last value added to the database (i.e. 100011) even
though it's in the "PDA range", thus the separation doesn't work for this
scenario.
2) The VCE form is assigned the low range (e.g. start the autonumber
control in VCE at 1). I then reset the autonumber field in Access to start
at 100000. I enter records 1-10 on the PDA and records 100000-100010 on the
desktop. When I synch the database the records again come in fine from the
mobile device with two ranges intact. However, when I then enter data on the
desktop device it starts the autonumber field at the next increment after the
last value added to the database (i.e. 10), thus the separation doesn't work
for this scenario either, because Access is looking for the last number
entered instead of the "starting value" assigned when I reset the autonumber
field in access (i.e. 100000).
When I end up with duplicate records in either autonumber field, the error I
get when I try to synchronize duplicate Unique IDs is: "[Microsoft][ODBC
Microsoft Access Driver] the changes you requested to the table were not
successful because they would create duplicate values in the index, primary
key, or relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit duplicate
entries and try again."
Based on this information, this technique would work if there was only one
PDA being used and there was no possibility that some one would enter data on
the desktop in between mobile synchronizations, in which case the ranges
would not be necessary. This is not the case for my application. It is
possible, if not likely, that I will have someone in the field entering data
while someone else is on the desktop at the office entering data at the same
time. We will have multiple PDAs in the field at the same time and down the
road a bit we may also use Tablet PCs in the field and thus have multiple
versions of the Access database that need to be synched as well.
Thus, there seem to be several options left:
1) Maintain separate databases for the mobile and desktop database. These
can then be synched manually by changing the autonumber field to a number and
then appending the tables together in the master database that is only
accessed by the database administrator. This option is cumbersome and
doesn't fully meet the goal of our system.
2) Create a composite UniqueID consisting of an autonumber field combined
with a unique identifier for the device on which the data was entered. I can
do this on the handheld by combining the autonumber field with a second field
that calls a function that returns the device name for each PDA.
REQUEST: For this scenario I need to know how to create a similar field in
Access that combines an autonumber field with a "device name" to create a
single Primary Key field in each table.
3) Alternatively, I could abandon the autonumber approach and use a unique
identifier that creates a text string or integer that is a composite of the
date and time (carried out to seconds), on the assumption that users will not
be entering data at the exact same second on different devices. I suppose I
could add the device name to this to deal with that possibility.
REQUEST: To do this, I need to know how to build the function in Access to
create the unique ID and return it to my ObsID and DetID fields in the
tables. Does this require a function running as an event in the Access form
used for data entry into the two tables. Also, would the unique ID need to be
different for each table?
4) I also tried adding a field for "device" to tblObsGen and tblObsDet and
using both device and the autonumber fields as a combined primary key (i.e.
both fields set to primary key). This still gave me duplicate numbers in the
autonumber field which generated my usual error on synchronization.
The second or third scenarios are most attractive, but I'm running into the
limits of my knowledge regarding programming in Access. Any help is
appreciated. I can send a simplified sample Access database that illustrates
the problem on the Access side if that would be helpful
Thanks Bruce (from Caimari)
I have an Access 2003 database that I am running on a desktop and a mobile
PC running in Windows Mobile 2005. The mobile database is built using Visual
CE (VCE) software by Syware (www.syware.com). I download the Access tables
to the PDA and build forms over them in VCE to enter data. I then
synchronize the databases using the VCE synchronization function (via
ActiveSync 4.0). Both run fine independently, but I'm having problems with
synchronization. I'm posting this to the tables forum (as well as the forms
forum) because I think the solution involves new code in my Access form that
populates the table (see below). Sorry about the lenghty description, but my
other efforts at solving this issue have all ended eventually up in detailed
descriptions, so I'm posting it all up front.
Database Structure:
The primary database structure consists of two related tables that are used
to record "general" and "detailed" observations: tblObsGen and tblObsDet.
The relationship is built on a one-to-many relationship using the field
ObsID, which is the primary key in tbl_tblObsGen and is a foreign key in
tblObsDet. In tblObsDet the primary key is the field DetID. So for each
general observation recorded as a unique ObsID record in tblObsGen, there can
be one or more detailed observations recorded in tblObsDet (each having their
own unique ID indexed with no duplicates in the field DetID). Both of the
fields ObsID and DetID are indexed autonumber fields set to increment (as
opposed to random) that allow no duplicates. In Access I use a single form
(containing several subforms) to enter data into tblObsGen and tblObsDet.
Problem Description:
I built the database(s) and forms on the assumption that I could specify
separate ranges for unique (autonumber) IDs in the desktop and handheld
databases that would prevent duplication of autonumber Unique IDs in the
primary and foreign keys. This turns out not to be true. I've tested the
two possible scenarios for specifying separate ranges for Autonumber fields
for Obs_ID and Det_ID as follows:
1) The VCE form is assigned the high range (e.g. start the autonumber
control in VCE at 100000. I then reset the autonumber field in Access to
start at one. I then enter records 1-10 on the desktop and records
100000-100010 on the PDA. When I synch the database the records come in fine
from the mobile device with two ranges intact. However, when I then enter
data on the desktop device it starts the autonumber field at the next
increment after the last value added to the database (i.e. 100011) even
though it's in the "PDA range", thus the separation doesn't work for this
scenario.
2) The VCE form is assigned the low range (e.g. start the autonumber
control in VCE at 1). I then reset the autonumber field in Access to start
at 100000. I enter records 1-10 on the PDA and records 100000-100010 on the
desktop. When I synch the database the records again come in fine from the
mobile device with two ranges intact. However, when I then enter data on the
desktop device it starts the autonumber field at the next increment after the
last value added to the database (i.e. 10), thus the separation doesn't work
for this scenario either, because Access is looking for the last number
entered instead of the "starting value" assigned when I reset the autonumber
field in access (i.e. 100000).
When I end up with duplicate records in either autonumber field, the error I
get when I try to synchronize duplicate Unique IDs is: "[Microsoft][ODBC
Microsoft Access Driver] the changes you requested to the table were not
successful because they would create duplicate values in the index, primary
key, or relationship. Change the data in the field or fields that contain
duplicate data, remove the index, or redefine the index to permit duplicate
entries and try again."
Based on this information, this technique would work if there was only one
PDA being used and there was no possibility that some one would enter data on
the desktop in between mobile synchronizations, in which case the ranges
would not be necessary. This is not the case for my application. It is
possible, if not likely, that I will have someone in the field entering data
while someone else is on the desktop at the office entering data at the same
time. We will have multiple PDAs in the field at the same time and down the
road a bit we may also use Tablet PCs in the field and thus have multiple
versions of the Access database that need to be synched as well.
Thus, there seem to be several options left:
1) Maintain separate databases for the mobile and desktop database. These
can then be synched manually by changing the autonumber field to a number and
then appending the tables together in the master database that is only
accessed by the database administrator. This option is cumbersome and
doesn't fully meet the goal of our system.
2) Create a composite UniqueID consisting of an autonumber field combined
with a unique identifier for the device on which the data was entered. I can
do this on the handheld by combining the autonumber field with a second field
that calls a function that returns the device name for each PDA.
REQUEST: For this scenario I need to know how to create a similar field in
Access that combines an autonumber field with a "device name" to create a
single Primary Key field in each table.
3) Alternatively, I could abandon the autonumber approach and use a unique
identifier that creates a text string or integer that is a composite of the
date and time (carried out to seconds), on the assumption that users will not
be entering data at the exact same second on different devices. I suppose I
could add the device name to this to deal with that possibility.
REQUEST: To do this, I need to know how to build the function in Access to
create the unique ID and return it to my ObsID and DetID fields in the
tables. Does this require a function running as an event in the Access form
used for data entry into the two tables. Also, would the unique ID need to be
different for each table?
4) I also tried adding a field for "device" to tblObsGen and tblObsDet and
using both device and the autonumber fields as a combined primary key (i.e.
both fields set to primary key). This still gave me duplicate numbers in the
autonumber field which generated my usual error on synchronization.
The second or third scenarios are most attractive, but I'm running into the
limits of my knowledge regarding programming in Access. Any help is
appreciated. I can send a simplified sample Access database that illustrates
the problem on the Access side if that would be helpful
Thanks Bruce (from Caimari)