Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139

    Answered: Networking Advice -- General Advice

    Greetings all,

    I am trying to implement (on a volunteer basis; I am retired) a small Access application on a network at a local food bank. The bank has about 700 registered users. Each week day, if fulfils about 40 food orders. The database has two central tables, "clients"; which contains information concerning households and their members (actually two central tables, households and members), and distribution (food items provided, by client, type and date).

    I am loathe to spend too much on network equipment, so am thinking of using a WD Mycloud [WDBCTL0030HWT-NESN] (cheap; about $200 CDN) 3 taraabyte network hard drive connected to the router. I have an Access App at home that runs from the network drive, and it seems to run fine (though sometimes I need to kick the network drive to wake it up; its a heavy sleeper). The daily usage level will be quite light: 40 transactions or so for food distribution, and perhaps the same number comprised mostly of two ladies adding 20 new clients per day each. In effect, 3 users on three separate computers. The plan is to have all the users back up daily the network application to their own hard drives (not that I don't trust Western Digital.....).

    I would like to get this up and running by April 1st, and then start adding refinements (for example, dividing the app into front end and back end) as time allows (at the moment, their IT department, and IT hardware specialist is ME). The bank's fiscal year begins April 1st. Will this work? Any advice?

    Regards

    J Smith
    Gatineau, Quebec, Canada

  2. Best Answer
    Posted by healdem

    "Access runds fine on a local network (it was picking up on the 'cloud' but that threw me )
    Access is strictly speakign a fron end, it can talk to numerous SQL and ODBC data stores. the default SQL datastoiree which most people believe is Access is actually JET, but as far as Access itsefl is concerned it can talk to any SQl DB (and a lot of non SQL datastorage mechanisms.

    if your application is going to run on a local network then its almost fine. There are intrinsic problems, with using a file server datastore such as JET which is they don't scale very well, they are not good on intermittent or unreliable networks. there is no precise number at which point Access/JET craps out its anywhere upwards of 15..30 concurrent users. a file server db such as JET shunts a lot of data upo and down the network as each client has to do the processignj on the locla client machine. A lot of the default out of the box cehaviours when desiging reports and forms can really hammer a network, expecially if there are lots of segments /switches between the clients and the actual datafile,


    if you are designign this as a multi user application then deploy it as:-
    split the app into a frotn end and backend
    the front end should be an encrypted file (MDE or ACCDE) so users cannot make changes / secure edit locks and so on
    by deploying as an encrypted file you cna use Access runtime at zero licence fee as opposed to having a copy of Access per workstation
    ideally each workstation should have its own local copy of the MDB pointing to the common data store
    if you deploy a MDE / ACCDE then you need to retain your MDB/ACCBD as your design master.
    in an ideal world you have (at least) 4 files
    a development pair of front end and back end
    a live pair of front end and back end

    there is code in the code bank to make certain users are using the most current version of the front end.
    if you sue linked tables define those linked tables as server shares not drive letters (unless you cna coerce all users to use the same drive mappings

    learn to love backups (and periodically prove your backups are 'good')
    consider audit logs / audit trails
    consider the implications of security / data protection. do you have a 'duty of care' of personal information. do you need to demonstrate legal compliance as to who can access the data and how. Access is a file server db, it can be really tricky to tie down securely an Access application talking to JET"


  3. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    acces isn't smaret or clever as an internet/WAN product.
    you woudl be better off using a sedrver based bd to store the data
    I'd rather be riding on the Tiger 800 or the Norton

  4. #3
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139
    Hi again,

    This is one of those quick education processes ( I have not worked with MS Access in a multi-user environment). Does it make a difference if I split (front end, back end) the databases? Or if I use an access front end, and something else on the back end (though I know MySQL, and can imagine the potential data type problems)?

    I have an old dogs new tricks problem (and I am an old dog). MS Access is nice and easy to program in, compared to some others (I once worked with Java, and barely escaped hurting myself). I would prefer to stick with what I know, rather than enter a new arena (SQL server, etc). I also really don't want to saddle the food bank with an ongoing headache.

    More thoughts.

    Regards

    J Smith
    Gatineau, Quebec

  5. #4
    Join Date
    Mar 2009
    Location
    Gatineau, Quebec Canada
    Posts
    139
    Another comment. The intention was to use the router, and never to try it over the internet. What I really need is a NAS hard drive attached to a purely local (and wireless) network. I did, BTW, split the app into front end and back end, and then start using it through (in effect) my cheap NAS hard drive (the WD MyCloud) . Seemed to work fine; I got two computers working on the same table concurrently, without problem. Speed seemed reasonable too.

    Multiple user Access is all new stuff to me, as is NAS Network drives. Only got into the latter out of curiosity. I have, BTW, been running a personal Access App via the WD McCloud without incident for a around six months. Only problem is waking the little server up (sometimes takes a while to get it started).

    Regards

    John S
    Gatineau, QC

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access runds fine on a local network (it was picking up on the 'cloud' but that threw me )
    Access is strictly speakign a fron end, it can talk to numerous SQL and ODBC data stores. the default SQL datastoiree which most people believe is Access is actually JET, but as far as Access itsefl is concerned it can talk to any SQl DB (and a lot of non SQL datastorage mechanisms.

    if your application is going to run on a local network then its almost fine. There are intrinsic problems, with using a file server datastore such as JET which is they don't scale very well, they are not good on intermittent or unreliable networks. there is no precise number at which point Access/JET craps out its anywhere upwards of 15..30 concurrent users. a file server db such as JET shunts a lot of data upo and down the network as each client has to do the processignj on the locla client machine. A lot of the default out of the box cehaviours when desiging reports and forms can really hammer a network, expecially if there are lots of segments /switches between the clients and the actual datafile,


    if you are designign this as a multi user application then deploy it as:-
    split the app into a frotn end and backend
    the front end should be an encrypted file (MDE or ACCDE) so users cannot make changes / secure edit locks and so on
    by deploying as an encrypted file you cna use Access runtime at zero licence fee as opposed to having a copy of Access per workstation
    ideally each workstation should have its own local copy of the MDB pointing to the common data store
    if you deploy a MDE / ACCDE then you need to retain your MDB/ACCBD as your design master.
    in an ideal world you have (at least) 4 files
    a development pair of front end and back end
    a live pair of front end and back end

    there is code in the code bank to make certain users are using the most current version of the front end.
    if you sue linked tables define those linked tables as server shares not drive letters (unless you cna coerce all users to use the same drive mappings

    learn to love backups (and periodically prove your backups are 'good')
    consider audit logs / audit trails
    consider the implications of security / data protection. do you have a 'duty of care' of personal information. do you need to demonstrate legal compliance as to who can access the data and how. Access is a file server db, it can be really tricky to tie down securely an Access application talking to JET
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •