Installation of the Library Deposit Distribution Databases
The Backend Location
The location of the backend database Library Deposit Distribution Service Database_A97_BE.mdb can be located anywhere on your network and be placed into a folder subsystem with any name. For example these are possibilities:
or if you like
or if you need to nest the directories deeper then, for example
Please note that the location and directory name can be anything. The Application however expects that there will be a subdirectory LDDS User Interface Source and a subdirectory Saved Letters. The subdirectory Saved Letters will be actually created at runtime by the application.
In our examples we will use the last example above.
The Content of the Backend Folders
The following content must be present in the backend folders.
The main folder:
The LDDS User Interface Source Folder
This is a collection point for Users. Users will click on Setup.exe and the application - the User Interface - or Frontend Database - will be installed to their workstation. It should be noted that the Frontend will not be usable - will not be allowed to run - if executed on a network drive.
The Saved Letters Folder
This directory will be created at runtime if it is not present. Eventually it will have content as in the example below. Initially it will have no content unless content is provided by NOE of letters already created and sent.
Installing the Frontend
Given that the above installation of the backend is completed - and clearly it is a manual operation - the installation of the Frontend Database is relatively straightforward.
The User will double click on SETUP.EXE from the LDDS User Interface Source Directory.
The following windows will present:
The User will be requested to confirm the place on their workstation for the Frontend. It is recommended that the default be accepted. However, the User may install the Frontend anywhere on their workstation as long as it is not to a network location.
On the request for backup options check Yes.
and then
The setup application will install to the User's workstation the Dynamic Link Libraries required (in case they are missing) and copy the Frontend to the location on the workstation previously specified.
On setup completion the following will present:
The DLLs/References copied are:
Reference to VBA, Kind:=0, FullPath:=C:\PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\VBA\VBA332.DLL, Version:= 3. 0Reference to Access, Kind:=0, FullPath:=C:\PROGRAM FILES\MICROSOFT OFFICE97\OFFICE\MSACC8.OLB, Version:= 8. 0
Reference to stdole, Kind:=0, FullPath:=C:\WINDOWS\SYSTEM\stdole2.tlb, Version:= 2. 0
Reference to DAO, Kind:=0, FullPath:=C:\WINDOWS\SYSTEM\DAO360.DLL, Version:= 5. 0
Reference to Word, Kind:=0, FullPath:=C:\Program Files\Microsoft Office97\Office\MSWORD8.OLB, Version:= 8. 0
Reference to Scripting, Kind:=0, FullPath:=C:\WINDOWS\SYSTEM\SCRRUN.DLL, Version:= 1. 0
These are referred to as References in MsAccess. The exact paths to these may vary from system to system.
The Frontend Application
The Frontend should be available now from the Start button on the bottom left of the User's Screen.
After entering the Frontend Password in
references are checked displaying any failed references/DLLs
example:
and
and
and if it still fails
and clicking No will display the following screen showing the correct order.
References can be placed in the correct order by opening the frontend and opening any module in design mode and clicking on Tools + References.
In extreme cases, where there has been a flawed implementation of Office 97 a User or Administrator might have to install DLLs/References manually. This will be evident when the application simply does not run and reports compile errors against the existing code.
Resolving this might involve the execution of the regsvr32.exe application as in the following examples:
regsvr32.exe c:\nt\system32\dao360.dll or regsvr32.exe "c:\windows\system\dao360.dll"
Also see Microsoft Knowledge Base article Q249873. It says support techs can use Regsvr32 to register and unregister object linking and embedding (OLE) controls, including both DLL and/or ActiveX Controls (OCX). The article also describes some of the typical error messages you might receive if Regsvr32 doesn't work.
and also Microsoft Knowledge Base article Q207132. This goes into more detail about Regsvr32, saying it functions as a five-step program to register or unregister DLLs or OCXs.
Please DO NOT USE DAO351.DLL as it will crash your system. DAO350.DLL has to be used for some Windows 95 systems instead of DAO360.DLL.
Again, please note that in the frontend, the order of the references for MsAccess 97 must be as shown in the above screen. The application DOES NOT test the ORDER of References as of version 2s. So if the order is wrong please adjust the order manually.
The application will automatically resolve the references in most cases if the User has executed the SETUP.EXE application at least once. Only where MsOffice 97 has not been fully implemented will any reference problem occur.
Given that the references all tested ok the following may present on first execution if the backend cannot be located.
Note that if the application finds the application the following messages and dialog will not occur.
Again, if the frontend has already been configured before collection the above will not present and the following will not be necessary. Clicking Ok will present a dialog something like the following:
The User should navigate to the network location of the backend.
Highlight the backend database and Click Select.
The following will present:
Click Yes to confirm, and the backend tables will be attached/linked. The User will see the following as the tables are attached.
and at last the User is presented with the main menu
It is suggested that once a frontend has been configured for a new location that it be copied back to the distribution point on the server. This will mean that subsequent Users will not have to configure the Frontend.
Subsequent execution of the Frontend will yield the following immediately after entering the password:
Unless you require to test and/or refresh the attachments/links you may safely always answer No.
It is advisable on or after an upgrade (when it occurs) to answer Yes.
When an upgrade become available the following advice will present.
In our case it will be I:\Databases\Library Deposit Distribution Database\LDDS User Interface Source. You will use SETUP.EXE to copy it for you instead of copying manually.
Login of NEW User or Existing User on NEW Workstation
When a NEW User first executes the Frontend or when an existing User executes the Application on a NEW workstation the following dialog presents:
Note the Login name will be the USERLOGIN on WorkstationName.
Expanding User we have:
If the User has already had their name added by an Administrator they should choose that name. For example Donald Neal would choose Neal Donald. If the User has not been explicitly included in the list he/she can choose
LD Administratorif they have been given the Administrative Password
Or
Defaultif they have been given the non-administrative password
Or
Normal Userif they have been given the non-administrative password
LD Administrator has rights to almost everything
Default and Normal User do not.
The look and feel of the Interface is different for each. Toolbars are not available for non-administrators and a variety of other security measures are imposed.
In the example that follows the User chooses Default, enter the password and presses Enter.
The 'Password accepted" message presents, and clicking OK presents:
and the following will present where there is a security level change.
Upon reentering the application the Frontend Interface will look like this:
and menu items on the toolbar will be comparatively more restrictive than a login with Administrative Rights.
Had the User chosen LDS Administrator or themselves (given the higher security level) then the Frontend Interface looks like this:
The above differentiation between Security Level 1 and 0 is accomplished by a code segment executed on startup:
After_Special1: ' THIS FOR SECURITY LEVEL 1
'ChangeProperty "StartupForm", DB_Text, "frmMain Menu"
ChangeProperty "StartupShowDBWindow", DB_Boolean, False
ChangeProperty "StartupShowStatusBar", DB_Boolean, True
ChangeProperty "AllowBuiltinToolbars", DB_Boolean, True
ChangeProperty "AllowFullMenus", DB_Boolean, True
ChangeProperty "AllowBreakIntoCode", DB_Boolean, True
ChangeProperty "AllowSpecialKeys", DB_Boolean, True
ChangeProperty "AllowBypassKey", DB_Boolean, True
Else
After_Special0: ' THIS FOR SECURITY LEVEL 0
'ChangeProperty "StartupForm", DB_Text, "frmMain Menu"
ChangeProperty "StartupShowDBWindow", DB_Boolean, False
ChangeProperty "StartupShowStatusBar", DB_Boolean, True
ChangeProperty "AllowBuiltinToolbars", DB_Boolean, False
ChangeProperty "AllowFullMenus", DB_Boolean, False
ChangeProperty "AllowBreakIntoCode", DB_Boolean, False
ChangeProperty "AllowSpecialKeys", DB_Boolean, False
ChangeProperty "AllowBypassKey", DB_Boolean, False
End If
Administrative Setting Up of Users
The Administrator (or a User with such rights) can click on and and configure Users and Security Rights.
Currently (Version 2s) there is Level 1 and Level 0.
Level 1 = all rights
Level 0 = restricted rights.
There are coded rights for the Developer Don Neal and for the project Leader Kim Nelson regardless of anything here.
The immediate menu items are:
Clicking Add New Users presents:
If the new User is to have a security level of 0 (restricted) then just enter the Surname, FirstName, Position, email, phone Mobile and Fax numbers. If the new User is to have all rights change the 0 to a 1.
You will notice that the Password is not editable here. You have to click on Change Password.
and confirm it:
The password for each User can be anything you like. Please DO NOT FORGET to provide a password for each User added. Users and Passwords can be edited by anyone with Level 1 Security. Passwords are encrypted.
Resetting a User from a security level of 0 to 1 has to be done by the Administrator in both the backend and the User's Frontend. The Administrator can access the User's restricted Frontend by entering the backend from the User's Workstation and using the linked table tblLocalUser to clear the record.
Simply delete the record . . . and in the table [tblLogon Names], delete all references to the User (garryx in the example).
Another way for the Administrator do reset this is: in the backend, delete the User from the table [tblLogon Names] and get the User to collects a fresh copy of the Frontend (by executing SETUP.EXE).
Another Feature that may be apparent on first execution on a new workstation by a User is the
Configuration of All Reports
When the application starts it scans all reports and if any are found not conforming to the default set for this application screens and dialogs like the following will present. Please answer Yes for each to force to the correct defaults.
and
This check is also done each time an individual report is accessed, and there is also the option in the menu system under Reports:
Record Locking Consideration for both the Backend and Frontend
For the correct operation of record locking in a multi-user environment and for the appropriate configuration of our Application the Administrator as part of the INSTALLATION PROCESS should
Open the Backend with the SHIFT key held down (held down after entering the system password for the backend), navigate to TOOLS + OPTIONS +ADVANCED tab, and ensure the setting are as in the following example:
This should only have to be done the one time. However, please note that whoever accesses this Backend first will impose the settings of their copy of MsAccess and their Frontend Application on the Backend Database. Regardless of the next User's configuration, the prior connection, if still active, will totally cause this second User's settings to be disregarded even though they are correct.
THEREFORE, in order to ensure that each User's copy of the Frontend Application opens with the correct settings the following code segment is executed as part of the STARTUP process every time the Frontend Application is executed.
Please note again: setting the above has to be done manually for the Backend. The Frontend is taken care of with the following code segment - provided here in this section of this document to give the Administrator some insight into the process.
Despite all this, Record Locking in MsAccess 97 is really PAGE-LEVEL locking. True ROW-LEVEL record locking will only be achieved when the application is converted to MsAccess 2000+. The article extract from Microsoft included below the code segment will assist in understanding this concept.
SEE:
Private Function Cust_Options()
On Error GoTo err_Cust_Options
'Advanced Tab (MDB Only)
'Open databases using record-level locking -> Use Row Level Locking
On Error Resume Next
If Application.GetOption("Use Row Level Locking") = False Then'For Access 2000+ only
Application.SetOption ("Use Row Level Locking"), True'For Access 2000+ only
End If'For Access 2000+ only
On Error GoTo err_Cust_Options
'Default Record Locking Default Record Locking
If Application.GetOption("Default Record Locking") > 2 Then
Application.SetOption ("Default Record Locking"), 2
End If
'Refresh Interval (Sec) Refresh Interval (Sec)
If Application.GetOption("Refresh Interval (sec)") > 59 Then
Application.SetOption ("Refresh Interval (sec)"), 30
End If
'Number Of Update Retries Number of Update Retries
If Application.GetOption("Number Of Update Retries") < 4 Then
Application.SetOption ("Number Of Update Retries"), 4
End If
'Enable DDE Refresh Enable DDE Refresh
If Application.GetOption("Enable DDE Refresh") = False Then
Application.SetOption ("Enable DDE Refresh"), True
End If
'Default Open Mode Default Open Mode for Databases
If Application.GetOption("Default Open Mode for Databases") = 1 Then ' 1 = Exclusive
If Not CDNMsgBox("Do you really want to open this database in exclusive mode?") Then
Application.SetOption ("Default Open Mode for Databases"), 0 ' 0 = Shared
End If
End If
'Print margins, Left margin Left Margin
If Application.GetOption("Left Margin") > "1cm" Then
Application.SetOption ("Left Margin"), "1cm"
End If
'Print margins, Right Margin Right Margin
If Application.GetOption("Right Margin") > "1cm" Then
Application.SetOption ("Right Margin"), "1cm"
End If
'Print margins, Top Margin Top Margin
If Application.GetOption("Top Margin") > "1cm" Then
Application.SetOption ("Top Margin"), "1cm"
End If
'Print margins, Bottom Margin Bottom Margin
If Application.GetOption("Bottom Margin") > "1cm" Then
Application.SetOption ("Bottom Margin"), "1cm"
End If
'Use four-year digit year formatting, This database
'For MsAccess 2000+ only
On Error Resume Next
If Application.GetOption("Four-Digit Year Formatting") = False Then
Application.SetOption ("Four-Digit Year Formatting"), True
End If
'Use four-year digit year formatting, All databases Four-Digit Year Formatting All Databases
If Application.GetOption("Four-Digit Year Formatting All Databases") = False Then
Application.SetOption ("Four-Digit Year Formatting All Databases"), True
End If
exit_Cust_Options:
Exit Function
err_Cust_Options:
DoCmd.Echo True: LogError "modAutoExec Continue", "Cust_Options", Err.Number, Err.Description
Resume Next
End Function
Microsoft Office 2000/Visual Basic Programmer's Guide
------
Page-Level Locking vs. Record-Level Locking
When a user edits a record in a shared database, you can prevent conflicts with other users by locking the data while it is being edited. When data is locked, any number of users can read it, but only one user can make changes to it.
In previous versions of the Jet database engine (version 3.5x and earlier), locking a record locks one page of data. For previous versions of the Jet database engine, a page is equal to 2K (2048 bytes) of data within the database file; for the current version, Jet 4.0, a page is 4K (4096 bytes) of data. (The size was doubled to accommodate storing data as Unicode characters, which occupy 2 bytes instead of the 1 byte used by previous characters.) Locking at the page-level can lock multiple records if the combined length of two or more records is smaller than the size of a page; this prevents other users from editing any records on that page until the user is finished editing the record that caused the entire page to be locked. Page locking generally results in better performance, but can reduce the ability of other users to edit data.
A new feature of Jet 4.0 is the ability to lock individual records rather than pages. In Access, this is controlled by the Open databases using record-level locking setting on the Advanced tab of the Options dialog box (Tools menu). By default, this setting is selected (on), which means two users can update or delete two different records that are located on the same page within the database (which isn't possible when you use pessimistic locking under page-level locking). The locking mode that is in effect is determined by the first user to open a database. If the first user has the Open databases using record-level locking setting selected, all users who subsequently open that database will use record-level locking whether they have the setting selected or not. Conversely, if the first user to open a database has the Open databases using record-level locking setting cleared, all users who subsequently open that database will use page-level locking.