Temporary Tables for Importing/Exporting

I am currently setting up a small application where some of the users will
not always have access to the database on the office shared drive. Though
not related to this point, I am going to split the database. On the users
that do not have constant access to the back-end, I was planning on inserting
some tables in their front end which would hold data temporarily until they
connected to the shared drive. I was simply going to have an Autoexec pop-up
form which asked them if they were at the office. If they answer yes, then
their computer would run a saved export to the backend table. If they answer
no, then they would simply continue to save in their table. They will have
four tables on their front end: [1] which will be a lookup table
(tlook_Employees), [2] will be a main table (t_Daily) connected to [3]
(t_EmployeeDaily) through a subform and [4] another table (t_IndivActivity)
connected to (t_EmployeeDaily) through a subform. I am thinking that it is
safe to have the three data tables update the backend while the backend will
be used to update the lookup table. The main data entry table (t_Daily) will
have a field which designates username with this field being used as one of
the primary keys. This key will be hidden, locked and not enabled with the
default value being set for each user. Am I asking for problems? Does
anyone see anything flawed with this idea which may cause data corruption or
worse? If so, any alternative suggestions? Thank you in advance.

Brian

On Feb 8, 4:56*pm, Brian Carlson
wrote:
I am currently setting up a small application where some of the users will
not always have access to the database on the office shared drive. *Though
not related to this point, I am going to split the database. *On the users
that do not have constant access to the back-end, I was planning on inserting
some tables in their front end which would hold data temporarily until they
connected to the shared drive. *I was simply going to have an Autoexec pop-up
form which asked them if they were at the office. *If they answer yes, then
their computer would run a saved export to the backend table. *If they answer
no, then they would simply continue to save in their table. *They will have
four tables on their front end: [1] which will be a lookup table
(tlook_Employees), [2] will be a main table (t_Daily) connected to [3]
(t_EmployeeDaily) through a subform and [4] another table (t_IndivActivity)
connected to (t_EmployeeDaily) through a subform. *I am thinking that it is
safe to have the three data tables update the backend while the backend will
be used to update the lookup table. *The main data entry table (t_Daily) will
have a field which designates username with this field being used as one of
the primary keys. *This key will be hidden, locked and not enabled with the
default value being set for each user. *Am I asking for problems? *Does
anyone see anything flawed with this idea which may cause data corruption or
worse? *If so, any alternative suggestions? *Thank you in advance.

Brian

One thing I would watch out for... if the tables in your front end are
using *sequential* Autonumbers for primary keys, it could be a problem
if they can conflict with existing backend numbers. Since autonumbers
should do nothing more than guarantee uniqueness (if used properly),
you could just set the autonumber fields on your front end (and
probably backend) to Random and there's little likelihood you'll have
problems.

Just something to watch for...

Piet:

Thanks for the recommendation. I wasn't going to use the Autonumber as
Primary for this reason, but rather was going to use a combination of
username and date. Given that each user will only be creating one record for
any given date, then I believe that this should work. I will give it a
whirl, unless anyone else points out a problem with this design. Thanks for
the help.

Brian

"Piet Linden" wrote:

On Feb 8, 4:56 pm, Brian Carlson
wrote:
I am currently setting up a small application where some of the users will
not always have access to the database on the office shared drive. Though
not related to this point, I am going to split the database. On the users
that do not have constant access to the back-end, I was planning on inserting
some tables in their front end which would hold data temporarily until they
connected to the shared drive. I was simply going to have an Autoexec pop-up
form which asked them if they were at the office. If they answer yes, then
their computer would run a saved export to the backend table. If they answer
no, then they would simply continue to save in their table. They will have
four tables on their front end: [1] which will be a lookup table
(tlook_Employees), [2] will be a main table (t_Daily) connected to [3]
(t_EmployeeDaily) through a subform and [4] another table (t_IndivActivity)
connected to (t_EmployeeDaily) through a subform. I am thinking that it is
safe to have the three data tables update the backend while the backend will
be used to update the lookup table. The main data entry table (t_Daily) will
have a field which designates username with this field being used as one of
the primary keys. This key will be hidden, locked and not enabled with the
default value being set for each user. Am I asking for problems? Does
anyone see anything flawed with this idea which may cause data corruption or
worse? If so, any alternative suggestions? Thank you in advance.

Brian

One thing I would watch out for... if the tables in your front end are
using *sequential* Autonumbers for primary keys, it could be a problem
if they can conflict with existing backend numbers. Since autonumbers
should do nothing more than guarantee uniqueness (if used properly),
you could just set the autonumber fields on your front end (and
probably backend) to Random and there's little likelihood you'll have
problems.

Just something to watch for...

One side note:

Your user name and date are being used for two things:
1. Create the PK
2. Record those 2 pieces of information.

Under #1, the rest of your applicaiton might require that you never change a
PK. Whereas under #2 you might have to make corrections etc. If this is an
issue for you, you might want to make "two sets" of those fields, with only
one being editable. I put quote marks around "two sets" because technically
they aren't the same fields. One set contains the initial entry, the other
the definitive information.

Sh*t...I have done this for numerous tables without even considering this
possibility. It appears that an autonumber key combined with say the users
initials would be more appropriate, i.e. neither one of the items are
required data and the combination of them will ensure their is always a
unique primary key. Thanks for the suggestion Fred.

Brian

"Fred" wrote:

One side note:

Your user name and date are being used for two things:
1. Create the PK
2. Record those 2 pieces of information.

Under #1, the rest of your applicaiton might require that you never change a
PK. Whereas under #2 you might have to make corrections etc. If this is an
issue for you, you might want to make "two sets" of those fields, with only
one being editable. I put quote marks around "two sets" because technically
they aren't the same fields. One set contains the initial entry, the other
the definitive information.

On Mon, 9 Feb 2009 18:13:02 -0800, Brian Carlson
wrote:

Sh*t...I have done this for numerous tables without even considering this
possibility. It appears that an autonumber key combined with say the users
initials would be more appropriate, i.e. neither one of the items are
required data and the combination of them will ensure their is always a
unique primary key. Thanks for the suggestion Fred.

An autonumber is unique already. Adding initials (which AREN'T unique!) can't
make it any "uniquer".
--

John W. Vinson [MVP]

John:

I think that I may be confused. If two people are using computers that are
not attached to the backend and are storing their data in a temporary table
until which time they are connected to the backend and the data will be
uploaded, doesn't this mean that each user's temporary table will create
autonumbers that will not be unique because they are being created by two
seperate tables? I thought that adding the initials would do so. The
autonumber would be unique to their temporary table and with the addition of
their initials it would ensure that it is unique in the backend table....Or
am I missing something, i.e. that the autonumber field would somehow be
created by the backend table upon import? Please elucidate. Thank you.

Brian


"John W. Vinson" wrote:

On Mon, 9 Feb 2009 18:13:02 -0800, Brian Carlson
wrote:

Sh*t...I have done this for numerous tables without even considering this
possibility. It appears that an autonumber key combined with say the users
initials would be more appropriate, i.e. neither one of the items are
required data and the combination of them will ensure their is always a
unique primary key. Thanks for the suggestion Fred.

An autonumber is unique already. Adding initials (which AREN'T unique!) can't
make it any "uniquer".
--

John W. Vinson [MVP]

On Tue, 10 Feb 2009 18:15:01 -0800, Brian Carlson
wrote:

John:

I think that I may be confused. If two people are using computers that are
not attached to the backend and are storing their data in a temporary table
until which time they are connected to the backend and the data will be
uploaded, doesn't this mean that each user's temporary table will create
autonumbers that will not be unique because they are being created by two
seperate tables? I thought that adding the initials would do so. The
autonumber would be unique to their temporary table and with the addition of
their initials it would ensure that it is unique in the backend table....Or
am I missing something, i.e. that the autonumber field would somehow be
created by the backend table upon import? Please elucidate. Thank you.

Sorry... it was I who was confused. Your approach will work IF you take care
to ensure that each user has a unique set of initials (that is, you don't have
users Joe Doakes and Jane Darcy both using JD).

It appears that what you're trying to emulate is Access Replication (which
uses Random autonumbers as a way to avoid duplication).

--

John W. Vinson [MVP]

John:

Given that it should only be myself and another user, this should work for
me. Other than this, do you see any problem with the use of temporary
tables? An additional follow-up question; Should the records in the
temporary table be erased after exporting to the backend? It seems that they
would need to be. I imagine that I could do a delete query to manage this.
Thank you for your assistance.

Brian

"John W. Vinson" wrote:

On Tue, 10 Feb 2009 18:15:01 -0800, Brian Carlson
wrote:

John:

I think that I may be confused. If two people are using computers that are
not attached to the backend and are storing their data in a temporary table
until which time they are connected to the backend and the data will be
uploaded, doesn't this mean that each user's temporary table will create
autonumbers that will not be unique because they are being created by two
seperate tables? I thought that adding the initials would do so. The
autonumber would be unique to their temporary table and with the addition of
their initials it would ensure that it is unique in the backend table....Or
am I missing something, i.e. that the autonumber field would somehow be
created by the backend table upon import? Please elucidate. Thank you.

Sorry... it was I who was confused. Your approach will work IF you take care
to ensure that each user has a unique set of initials (that is, you don't have
users Joe Doakes and Jane Darcy both using JD).

It appears that what you're trying to emulate is Access Replication (which
uses Random autonumbers as a way to avoid duplication).

--

John W. Vinson [MVP]

On Tue, 10 Feb 2009 19:53:02 -0800, Brian Carlson
wrote:

John:

Given that it should only be myself and another user, this should work for
me. Other than this, do you see any problem with the use of temporary
tables?

Keeping track of what's been added and what hasn't becomes a real hassle and
requires care, but other than that, no.

An additional follow-up question; Should the records in the
temporary table be erased after exporting to the backend? It seems that they
would need to be. I imagine that I could do a delete query to manage this.
Thank you for your assistance.

I can make arguments for either of two approaches: create an all new .mdb file
for the temporary database (by copying an empty template database, or empty
except for lookup tables) and deleting the entire database after it's
appended; OR adding a date/time timestamp field and archiving the offline
additions (using a date selective query in the transfer), just in case you
need to go back and find out where and when a particular record was added (and
perhaps re-add them). You'll need to decide which - or some other! - method
suits your business needs.

--

John W. Vinson [MVP]