Roadblock in my design

[Beth]

I have designed a database that is functioning well. Now I found that it
needs to be changed for some expansion and I can't seem to find the
solution, even though it seems like it should be really obvious. So, I am
hoping someone can give me a suggestion to get my brain moving again.

There are 2 tables, Invoices and InvoiceDetail, that appear in a
form/subform format. The InvoiceID and InvoiceDetailID (primary keys) are
both autonumbers and the Invoice form is set to sort Ascending so that the
most recently input invoice is at the end of the list.
There are 2 support tables, Customers, and Products, that are referenced
form these as dropdowns.

What I need to do is create a second database that is identical for use at
another location. The data from the second copy, needs to import into the
primary data table. The data from the Invoice and InvoiceDetails tables can
be deleted from the secondary location after each import. The data in the 2
support tables will need to be kept reconcilled where they match on both
ends.

The only way these 2 database will be "linked" is by copying data onto a cd
or jumpdrive and installing on the other computer.

I am open to just about any suggestions.

Thanks in advance,
Beth

 

[Steve Schapel]

Beth,

There are a number of approaches that are taken to this type of
sutuation, depending on circumstances. For example, it may be possible
to set up the computer at the second location to use Windows XP Remote
Desktop or PCAnywhere or Terminal Services or some such to directly
access the database at the first location. In this case, you wouldn't
have to change your database design at all, assuming you have a standard
frontend/backend structure. Anoterh approach is to use the
Replication/Synchronisation features built into Access. This would also
require very little change to your design, although your AutoNumber
fields would become Random rather than Incremental. However, if you
have decided on the manual import method, the main design change needed
would be the InvoiceID and InvoiceDetailID to change from Autonumber to
Number data types, with some sort of routine to allocate these numbers
for new records. Then you would need to decide whether you want a
separate InvoiceID numbering system for each location, in which case you
would need a new field in the Invoices table for Location (1 or 2), and
the combination of InvoiceID and Location could be the primary key.
Otherwise, you will need to use an Append Query and an Update Query when
the data from the second location is imported, in order to assign new
InvoiceID values to the imported Invoice records, and then update the
InvoiceID foreign key field in the InvoiceDetail table to correspond
with these values.

 

[NetworkTrade]

I agree w/ Steve.

Having an exact copy/duplicate at the new location is ideal in terms of
simplicity - - and then all you have to do is be sure the new location &
existing location do not generate the same key.

Appending data from the new location will be quite easy.

 

[Beth]

Thank you! The manual import is the only possibility for their situation.
I like the idea of adding the field to designate the location and making the
key off a concatenation of the invoice number and location.
I really appreciate the help.
Beth