Merging tables

[Freshman]

Hi all,

This is what I have:

tblOrders tbl and tblItems tbl with one-to-many relationship (ID Auto)
Items tbl also has a ItemsID (Auto)

I have a few frontend Users sharing the backend tables to update data.
The problem is that sometimes their internal server goes offline due to hardware issues but they need to keep on working offline.

So I created two local tables named tblOrdersOffline and tblItemsOffline with the exact same structure and relationship.
So now each User will populate his own local table while the server is offline.

When they go online again, I need to merge each of the local tables to the main backend table ensuring correct relationships.

Is there a build-in function I can use of will I have to resolve to coding a solution?

I did look at Append Query but I can only append to a table not to a combined query. So I have to do each table separately (tblOrdersOffline to tblOrders and then tblItemsOffline to tblItems). I however foresee a problem when the process is automated (checking for server to be online and then merging) that multiple Users will merge at the same time and because I'm dealing with having to append 2 tables it might cause problems in between the processes.

Thanks
Pierre

 

[Freshman]

Ahh - I think I've got it.
The main problem is that if anyone of the multiple Users have AutoNum in any of the 2 tables already existing in the main online tables it will not append the data.
One route I can follow is to set a "range" for each User by setting the AutoNum using Allen Browne's function here:
http://allenbrowne.com/ser-26.html

I still have to test this using multiple machines to be sure but in theory it should work

 

[Cronk]

Don't play around with setting auto number fields. You will be playing with a live grenade and will come to grief when it goes off.

You need to maintain the foreign key between Orders and Items.

I'd personally be importing off line records.

However, you could look to using replication which is an inbuilt feature with Access. (Search for "access replication"). Note I've never used it so cannot comment its effectiveness.

 

[Galaxiom]

Use a composite key so records for each branch have a separate branch number component in the key

To increment the other half of the key with something along this line:

DMax("ItemsID"."tblItems", "BranchID=" & BranchID) + 1

 

[Freshman]

Hi Cronk & Galaxiom
A lot of thought has gone into the process since my initial post and subsequent own reply.
@Galaxiom – Thanks I actually did end up using a “compsite” key made up my the CurrentUser and PCName to make sure Users data are kept separate in the event of more than one User updating their data at the very same time when the LAN Server goes live. It seems to work well.
@Cronk - As far as the Autonum is concerned I only use it in one offline temp table to reset the count to 1 after I clear it out completely.
Also – From the write-up, the “Replication” function seemed to be a perfect fit if it was not for the fact that it was discontinued due to technical issues and therefore not reliable.
https://www.quora.com/What-is-database-replication-in-Microsoft-Access-and-what-is-it-used-for

Thanks a lot