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
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