NRAO Home  >  Green Bank  |  Wiki Topic:    GB > Electronics > RfiGroup > NrqzDataBase

Changes | Index | Search | Go

NRQZ Database Project

• Project Proposal
  • Deliverables
  • Need & Benefit
  • Random Notes
  • Email correspondences

Project Proposal

Deliverables

1. A Microsoft Access database that is multi-user capable.
2. Data entry pages that the RFI group can use to manually enter content.

Need & Benefit

The workload of the NRQZ office is becoming increasing difficult to administer efficiently. The current Access database appears to be single user mode limiting data entry. Back-ups are done twice/week onto a network drive as a preventative measure minimizing the affect of a computer storage crash or data corruption. SQL has been mentioned as an option but has yet to be investigated.

Random Notes

• Back up schedule as follows:
  • 4 backups at 12:15, 12:20, 12:25, and 12:30 to \\gbfiler\pwoody\Backups. These are simply copies, overwriting the previous backup files. The files created and what they are backups of are:
    • nrqzltrs.bkf c:\MS Office\Winword folder
    • nrqzmdb.bkf ...\pwoody\My Documents\nrqz.mdb
    • swtap.bkf c:\SWTAP\NRQZ, DRAWINGS, and WORK folders
    • terrnav.bkf c:\Program Files\MapTech\Terrain Navigator Pro folder
  • NOTE: Back-up schedule needs to be modified to daily. Server space needs to be monitored to insure that their is adequate space.

• Contacted library to obtain copy of Microsoft Access reference material.
  • Received "Using Microsoft Office 2000"

• Investigate if network version of Access is available.
  • Microsoft Access is not available in a network version.

• See if existing database is capable of multi-user access.
  • Review of configuration indicates the database is already multi-user capable.

• Current database is Access 2000. IT indicates Access 2007 will be available soon.
  • As database is 2000 version:
    • 1) Make back-up immediately prior to migration to 2003,
    • 2) Migrate to Access 2003,
    • 3) Migrate database to server,
      • See Microsoft Access Help topic: About sharing an Access database on a network (MDB)
    • 4) Migrate to Access 2007

• Multi-user access
  • Access is capable of multi-use access. Access Admin privileges will remain with the NRQZ Admin. Users will have a unique UID/PW and privileges will be user specific.
    • Set up for multi-user access
      • See Microsoft Access Help topic: Set options for a shared Access database (MDB), and Editing data in a Shared Database
    • Set up USERS and permissions
      • See Microsoft Access Help topic: Manage user and group accounts (MDB), Add USER Statement, Specify whether a user can add, edit or delete a record.

• Database security
  • IT will administer security settings so that only members of the RFI Group will have RWE permissions.

• Database management
  • Split database to data and objects
    • See Microsoft Access Help topic: Split an Access database (MDB)
  • Microsoft SQL is available should it be deemed necessary to migrate Access at a later time.

Email correspondences

16OCT07: The workload of the NRQZ office is becoming more than 1 FTE can handle. One of the major obstacles in distributing the workload is the fact the NRQZ database is an Access database which lives on a single PC hard drive (it is copied periodically to the network for backup). If we simply move the database files to the network, that still would only allow one user at a time to have the file open for editing, so that does not solve our problem. An alternative to a network version of Access would be to migrate the database to SQL, but that would mean I would need software resources to write the interface for it, MAYBE NOT and I am currently struggling to do exactly that with another project. (We'll see how it goes; I'm not going to start in this direction with the NRQZ database until the current project succeeds.)- CB

16OCT07: Gareth, Is there a version of Microsoft Access that allows simultaneous multiple-user editing of the database? If so, what would it cost us? -CB

16OCT07: Apparently, Access CAN use a SQL database. "Access can use data stored in Access/Jet, Microsoft SQL Server , Oracle , or any ODBC -compliant data container."

from http://en.wikipedia.org/wiki/Microsoft_Access

...wish I had known that. Anyway, I CANNOT start working on a project to migrate the NRQZ database at this time. Wes, is this something you'd be interested in working on? - CB

17OCT07: Now this document (http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm) has some cool stuff, but I didn't read it well enough to see if it really addresses the heart of your initial question: how to make Access multi-user. Anyway, it discusses the merits of splitting the data from the forms as I'd mentioned the other day, and it mentions making the database into a .mde (a standalone executable which is awesome and reason enough to convert the database to Access 2003). Anyway, have a read and I'll see what else I can find on multi-user access databases. - CM

18OCT07: Carla,As far as I know, there is not. Access was designed as a single-user database. For multi-user access, with record-locking, etc. you usually use a database designed for the purpose - MS SQL, Oracle, MySQL?, Postgress, etc. - just as you appear to be doing. If you are prepared to do a little VBScript, you can find recipes on the web to enable what you need, for example:

http://www.aspfree.com/c/a/Microsoft-Access/Upgrading-your-Access-Application-for-a-Multi-user-Environment/ For several years, I have been vainly advocating the need for an NRAO-wide Data Base Administrator (DBA) group precisely to help with this type of (very understandable and reasonable) request. The only group who may have the expertise available to help you is Amy Shelton's group, but I have no idea whether they actually have the resources available. - GH

-- PauletteWoody - 31 Oct 2007