dB design approach RE: multiple disconnecting / reconnecting users to a WAN dB (1 Viewer)

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
hi all

i would be most grateful for advice on database design - i think in the area of relication vs splitting a database... but im not entirely sure. If i could give you the scenario.

its access 2003. No choice.
i am pretty familar with general dB design, splitting databases and vba with a bit of sql.

up until now i have been building databases that run on a largeish wider area network - but not the more recent cloud type techs.

these databases are typically split with the back end and its tables located on a central server and multiple (satellite) users with their own front end located in their own workstations.
This has always worked well using vba to deploy new front ends to users etc. the vast majority of data is held in the common back end. a small amout of data (mostly personal set up prefs etc) is held in a few tables in the users front end.

Typically there could be 30 users of a system - tho not necessarily all at the same time. historically, when i have had larger numbers of simultaneous users i have used an ADO Disconnected recordsets approach to minimise strain on the network. this works as invariably users create and work on their own records - minimising record update conflicts etc.

ALL GOOD and working great.

NOW...
imagine the same type of structure except that some of the 'satellite' users are now working on laptops and will often disconnect from the network for extended periods. therefore during a day several users could be disconnecting or reconnecting to the WAN and db backend

when they are disconnected..

the front end of their satellite db needs to contain their records - identified by their id. to save space etc, they do not need access or to see records created by other users.

they do however need access to a range of master files - typically used to populate lists for dropdowns or the customer master file etc.

whist disconnected they must be able to create new records and update their own existing records. They do not need to be able to make changes to the records of others (infact the records of others ideally would not be present on their satellite). They do not need to make changes to commonly used master files.

Whist they are disconnected other users of the system who are conntected to the back end on the network, should have available to them a read only version of the last versions of the disconnected users records as they were at the time of disconnection.

Upon re-connection to the network...

the system either automatically or via a prompt should update any changes to satellite records that existed before disconnection and add records newly created whist disconnected, back into the hub.

----------------

in essence thats it! lol. i have been reading a bit about replication and see it appears to have its issues. As i am totally unfamilar with replication I would have to start a series of tests etc to familiarise myself with the concept.

but as it stands right now i am not sure if that is the correct way to go - so i thought i would ask for your expert advice as to how best approach the above problem.

I am reasonably familar with vba and the likes of ADO disconnected recordsets etc and have in the back of my mind that a self build VBA solution might be the way forward. but again i am not sure and would really appreciate your thoughts.

You have been a very helpful forum in the past and i am really keen to hear any ideas you may have.

Regards

Michael
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Sep 12, 2006
Messages
15,613
it doesn't really matter whether it is a wan or a lan - it's the principle of how you resynchronise separated databases.

you can have distinct serial numbers for each set of data, but some tables might not be amenable to such treatment. i think you will have to research replication, and decide for yourself the best way to implement a soultion.

it's probably not even so easy to determine when the connection is live again, and ready to synchronise - and there must be an issue with managing the position if the connection fails in the middle of the resynch.

sorry I can't be more help, but this seems very tricky to me

good luck!
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
hey Dave (of Gemma)

thanks for your info. yes it seems tricky to me! lol

im looking into the replication side of things as we speak. would still love to hear from any one with experience of such a prob.

as i get to a solution ill post up what i find and hopefully it will prove useful to some one.

Regards

Michael
 

DavidAtWork

Registered User.
Local time
Today, 11:43
Joined
Oct 25, 2011
Messages
699
I can envisiage a methodology that would work here, essentially all users have their own databases and the synching process would consist of some vba to establish a connection using a DSNless connection string then looping through the table of records (marked with this userID) checking if the record already exists on the central server, update else append new. Other users records can be also be refreshed by deleting and reloading, would also require a userID marker to achieve this.
During the connection, all the master lookup tables are refreshed from the central server.
After the synch, the connection is closed.
The userID marker can also be used to ensure only that user can update their own records

David
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
hi David

i am familar with ado disconnected recordsets and have previously set up databases where the front and back ends connect to each other.

with that in mind i was thinking originally of some kind of method similar to what you are are lining using dsn.

im trying my best to see if there is a way of avoiding the programming involved as the system is pretty complex with several ifs n buts..

to those ends and to my surprise i am progressing ok with the replication approach so far. there are a few issues - do to with the way it changes the primary key to a huge number, and a having a requirement for a few local non relicatable tables for a users local setting etc... but so far-ish ... (TOUCH WOOD) so good. it is early days but looks promising.

Illl keep you posted - and by all means if there are any readers who advise NOT to use replication.. i'd love to hear.

regards to all

M
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Sep 12, 2006
Messages
15,613
the large repid number is supposed to avoid collisions - which is another indicator that a autonumber should not be used to control anything.

we have had semantic discussions here about the likelihood of actually getting a (random) clash within a range of numbers up to 2billion.
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
yeah ... cant really use the large nos as a job ref any more! lol


are you saying that you dont even let the system use the large number even as a primary key to link tables in relationships?

the replication process has linked up numerous tables automatically inthis way. all seems ok so far.

fingers crossed (a billion and one times!)

goodnight for now


M
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
yeah ... cant really use the large nos as a job ref any more! lol


are you saying that you dont even let the system use the large number even as a primary key to link tables in relationships?

the replication process has linked up numerous tables automatically inthis way. all seems ok so far.

fingers crossed (a billion and one times!)

goodnight for now


M
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 11:43
Joined
Sep 12, 2006
Messages
15,613
no - you should use it as the PK/FK for record linking purposes. it's just that for replication purposes it deliberately randomises in the range of a longint (therefore max approx. 2bn) and is therefore not going to be useful for anything else.
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
Good morning!

Hi Dave

many thanks for that... it's what i was doing.. using the random nos as pk/fk... but great to have it confirmed by you.

many thanks

Michael
 

chaeljc

Registered User.
Local time
Today, 11:43
Joined
Nov 10, 2012
Messages
12
Guys (and gals)..

As i stagger through my learning of the access replication process could i ask for some related guidance on an issue or two which is confusing me somewhat....

in the usual non replicated scenario where users are permanently connected to the network i use a split database structure where the backend with the tables are located on the central server and the front ends with forms n coded etc are located on each users workstation. all users being linked to the central back end.

My question is how should my db be structured given that users now need to be able to undock their workstations(laptops) and use the db whilst out and about then re-dock to the network and re-sync....

should the database still be in a split format? if so how would that work for the undockable laptop users as they will need the tables etc brought with them.

do they use a split structure with the replica front and back ends both on their laptops?

or do they use a non split version?

bit confused here by this and the various pro / cons and ramifications.

Any input gratefully received.

Michael ;)
 

Users who are viewing this thread

Top Bottom