Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multi-User / Runtime Requirements (2003)

    I' m about to start a project creating a database for a small group, with about 20 users and about 400 records to start in the main table. I thought about using Access 2003, but am having second thoughts based on some research I've done.
    1. If all the tables, forms, queries, etc. are stored in 1 mdb file, how can I break things out so the data is stored on the server and the procedures are run from the local user's PC?
    2. Microsoft says the runtime is included with the product, but the runtime license must be purchased separately through an MSDN subscription or Visual Studio. What is this runtime license, a legality or some actual software/checksum that must be included with each user's copy of the runtime?
    3. MSDN subscription range from $199 for a dlcument library to $699 for MSDN Operating Systems to $2799 for MSN Universal. Which is the cheapest version I need for distributing apps. It seems like the $699 flavor, but that is a HIGH price.
    4. Is the Microsoft packager sufficient for what I need? A number of posts knock the packager and recommend Wise, which I've used before, but that adds another $699 or so to the cost of the project.
    5. Including Help - Microsoft talks about modifiying the Registry (KB Article ID 842004), but that is really absurd. Is it the only approach?
    6. Am I missing something in how Access 2003 systems get set up? I have a lot of VB 6 and FoxPro (ancient history, I know) experience and thought Access would be a rapid development tool for a relatively small system, but I'm beginning to wonder.
    Thanks in advance.
    (An earlier post by me several hours ago never appeared, so I'm re-posting. Sorry for any duplicates).

    Mark

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,483
    Thanks
    3
    Thanked 41 Times in 41 Posts

    Re: Multi-User / Runtime Requirements (2003)

    Hi Mark,

    Welcome to Woody's Access forum - things are a bit slow this weekend as a number of us who frequent the Lounge are away this weekend.

    Others may want to chime in and provide you more background, but here's my take on your situation. You basically have two options when you deploy an Access database in a production environment. Either get MS Office Pro installed on all the workstations, or use Visual Studio 2003 to create a runtime version of your application. Both approaches have merit. The Office Pro approach adds an incremental cost of about $100 per workstation, while if you have to buy Visual Studio and then go through the pain of building a run-time package, you've like spent around $1400 for software, and at least $600 worth of developer time to create the run-time. If 20 users translates into 20 workstations, you're somewhere near the break-even point from a cost perspective. On the other hand, if you anticipate continued development and refinement after you deploy the initial version, the cost of putting out a new version using the run-time approach gets to be a substantial burden. For that reason, we almost never take that approach - when you create an application that is really useful, users will always come up with a lengthy list of enhancements and improvements. Others may differ with me on this, but our point at which we start considering a runtime approach is more than 100 users.

    There are other issues as well when you create a runtime version, but I'll let others weigh in on those since we seldom use it. As to how you deploy it so the data is on the server, but the application runs from the workstation, there are two basic ways. First of all, the Jet engine, which is the native mode for Access databases, is not a database server, but must run on each application. So one solution is to put all of the objects in a single file - a characteristic unique to Access - and users then pull the forms, reports and data, along with any code, across the LAN to their workstation. There are performance issues in doing that, especially if the LAN runs at 10Mbit, and there are occasional corruption issues that result from workstation crashes, LAN errors, and the like. The approach favored by most is to split the database into a front-end and a back-end, where the tables in the database reside in the back-end on the server, and the front-end contains all the queries, forms, reports and code and is deployed to each workstation. The front-end connects to the back-end using the Linked Table feature in Access. Thus forms, reports and code are loaded from the local hard drive, and data is read from and written to the server. In this respect, it is rather similar to a VB6 project that runs on the workstation and accesses a Jet database on a server. Finally, depending on the criticality of the data, the volume of data entry, and other performance and back-up issues, for applications the size you are suggesting we often choose to put the back-end in SQL Server (or another robust Data Base Server) rather than an Access MDB file.

    Finally, you asked about Help, and we've only deployed a small number which used any custom Help. There are a number of features in Access that help to lead a user through the process of using an application. Those include such things as Tool Tips, Data Validation, Tab Sequences, DropDown Combos, List Boxes, and the judicious use of VBA to restrict the number of errors a user can make. We believe that with our experience level, we can deploy an Access application with one-third to one-half the effort of any other database technology.

    Let's hear what others have to say on the subject.
    Wendell

  4. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Multi-User / Runtime Requirements (2003)

    no way - I'm listening to you! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,325
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Multi-User / Runtime Requirements (2003)

    FWIW, here are my thoughts:

    1. DON'T store everything in 1 database. You have a backend database (often referred to here as BE) which holds tables and relationships, and sits on server. The frontend (FE) holds all queries, forms, etc. and each user has a copy on local disk. Access has a wizard to split a single database into parts, but I don't use it. I create them split right from the beginning.

    2. The runtime version is essentially a restricted version of Access, in that users can't design forms, etc. I've used the runtime successfully at many sites, but I've also had problems with it a few times. I'm not sure whether it can get updated via normal Office Updates, and if you have different equipment configuration (not all computers have same O/S, same releases, etc.) you can have installation problems. I'd bite the bullet and get Office Pro for everyone.

    3. If you do go with a runtime, the runtime and packager now comes with Visual Studio Tools (not sure what current price is, but it is a 1-time price). I don't know that it comes with MSDN subscription, but the subscription prices are yearly, aren't they?

    If you do get everyone Office Pro, you may not really need to get a packager. That is, unless you are including libraries, ActiveX controls, etc., that aren't normally part of Access. When I install, I have 3 files I install on server.
    - The BE mdb.
    - The FE mdb (and I immediately link it to BE).
    - A .bat file that copies FE down to local drive.

    At each workstations:
    - I put a shortcut to that .bat file in each users Startup folder, so they get a "fresh" copy of FE each time machine is restarted.
    - Then a shortcut on desktop to local FE.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #5
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-User / Runtime Requirements (2003)

    Wendell,
    Thanks for your very detailed post and the welcome to the Woody's Access forum. I'm definitely interested in hearing what others have to say - Catharine, don't be shy!
    I'll be glad not to create a runtime version. It DOES sound like a lot of work with potential for disaster. However, if everyone has Office Pro, what prevents some well-meaing, but possibly error-prone individual, from going in native mode (directly in Access) and even unintentionally changing/deleting, etc the original data?
    I would like to have users create their own reports, but I'm worried that unrestricted access (pardon my pun) through Access could result in dependencies getting messed up, etc.
    How can I avoid these problems?
    Mark

  7. #6
    2 Star Lounger
    Join Date
    Oct 2003
    Location
    New York, New York, USA
    Posts
    165
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-User / Runtime Requirements (2003)

    Mark,
    Thank you for the point by point reply. You've given me some good ideas. Here are followup questions:
    1. "I create them split right from the beginning" - do you do this by "File Save" to a different file name/mdb?
    2. I can recommend Office Pro for everyone, but how do you prevent users from opening the BE directly in Access and bypassing all my error handling, linking, dependencies, etc?
    3. I like the design of separating and using a .bat file. Wow, I haven't used a .bat file in a while. It does seem ironic using a DOS batch file in a supercharged graphic environment.
    Mark

  8. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,325
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Multi-User / Runtime Requirements (2003)

    1. "I create them split right from the beginning" - do you do this by "File Save" to a different file name/mdb?

    Just create the databases separately. There is no "File/Save" for saving databases.


    2. I can recommend Office Pro for everyone, but how do you prevent users from opening the BE directly in Access and bypassing all my error handling, linking, dependencies, etc?

    I recommend Garry Robinson's book "Real World Microsoft Access Database Protection and Security".
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,483
    Thanks
    3
    Thanked 41 Times in 41 Posts

    Re: Multi-User / Runtime Requirements (2003)

    <hr>what prevents some well-meaing, but possibly error-prone individual, from going in native mode (directly in Access) and even unintentionally changing/deleting, etc the original data?
    I would like to have users create their own reports, but I'm worried that unrestricted access (pardon my pun) through Access could result in dependencies getting messed up, etc.
    <hr>
    The solution to this sort of thing is to implement Access User Security, and in general to hide the database container window from your users. You can read a brief User Security Tutorial on our website - it includes links to a number of Knowledge Base articles on the subject - and you might find Jack MacDonald's in-depth User Security paper useful. Finally Mark has pointed you to a pretty comprehensive book on the subject - I don't concur with everything he says, but it's one of the most complete looks at securing an Access database. As to creating reports, we find that very few users are proficient enough in Access to actually create reports. What we do see however, is situations where users do create their own queries - we typically create a separate database for users who want that ability, and restrict their ability to edit data in any way from that database. Finally, I should note that where we use SQL Server back-ends, we also implement the SQL Server security model, which operates at a much different level than the Access model.
    Wendell

Posting Permissions

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