Navigation:  Articles > Feb-1998 >

Security, Replication, and No Automation

Previous pageReturn to chapter overviewNext page

199802_mc2 Mary Chipman and Peter Vogel          
 
Mary Chipman
 
 
I secured my application using the Security Wizard. I don't want my users to see the built-in security menus, but I need them to be able to set and clear their own passwords. I also need to be able to reset their passwords when they forget them. How do I do that?
 
First of all, if you don't want your users to interact with the built-in security dialogs, then you need to create your own custom toolbars and menus so that the built-in ones don't appear. You then need to build your own forms to act as your new security interface. Figure 1 shows a form for users to change their own password. It has three textboxes for the user to fill:

The user must provide his or her old password. If you don't do this, then after the user has logged on and left his or her desk, anyone could walk up and assign a new password to the user.
The new password.
A repeat of the new password. Since you won't want to display the new password on the screen, you must ask the user to type the password in twice to confirm that they know what they've typed.

Passwords can contain between 1 and 14 characters and are case-sensitive. To set the password, you must use the NewPassword method on a user object. In this case, that would be the current user, which can be obtained by using the CurrentUser() function. NewPassword takes two arguments: the old password and the new password -- another reason for users to type in their old password correctly in order to set a new one. The code I've provided for the Change Password button checks for the length of the new password and sets the new one. If there are no errors, then the password was reset successfully.

199802_mc1 Figure 1
 

Private Sub cmdChange_Click()

' Change the current user's password

 

 Dim varOld As Variant

 Dim varNew As Variant

 Dim varVerify As Variant

 Dim ws As Workspace

 Dim usr As User

 varOld = Me!txtOldPassword

 varNew = Me!txtNewPassword

 varVerify = Me!txtVerifyPassword

 

On Error GoTo HandleErr

 

' If both the old and new passwords are blank, then

' there's nothing to do, so exit.

   If IsNull(varOld) And IsNull(varNew) Then

       GoTo ExitHere

   End If

 

' There could be a blank old password, so make sure

' it's not null by concatenating with an empty string

   varOld = varOld & ""

 

' If a user wants to clear a password, they can leave

' the new password blank, so concatenate also

   varNew = varNew & ""

 

' Check to make sure the new password is no

' longer than 14 characters in length

 If Len(varNew) > 14 Then

   MsgBox "Password must be 14 characters or less." _

         , , "Invalid Password"

   Me!txtNewPassword.SetFocus

   GoTo ExitHere

 End If

 

' Check to make sure that the verify string matches

' the password string

 If StrComp(varNew, varVerify, 0) <> 0 Then

   MsgBox "Re-type password and verification.", , _

     "New password and verification are not the same"

   Me!txtNewPassword.SetFocus

   GoTo ExitHere

 End If

 

' If we got this far, then everything checks out

 Set ws = DBEngine(0)

 Set usr = ws.Users(CurrentUser())

 usr.NewPassword CStr(varOld), CStr(varNew)

 MsgBox "You have changed your password.", , _

     "Password Changed"

 

ExitHere:

   Exit Sub

 

HandleErr:

 Select Case Err

   Case Else

     MsgBox Err & ": " & Err.Description, vbCritical, _

      " Error in Change Password"

 End Select

 Resume ExitHere

End Sub

 
 
Unlike changing your password, clearing or resetting a user password that isn't yours requires that the person running the code be in the Admins group (only members of Admins can administer users, groups, and passwords). To perform this function, the form needs to display a list of the users and a textbox to input a new password for the selected user, as shown in Figure 2.

199802_mc2 Figure 2
 
You also use the NewPassword method for resetting user passwords, but you don't need to know the old password this time -- you can pass an empty string for the old password argument.
 

Private Sub cmdGo_Click()

   ' Reset or clear a user's password.

 

   Dim varPassword As Variant

   Dim varName As Variant

   Dim strOldPassword As String

   Dim ws As Workspace

   Dim usr As User

 

On Error GoTo HandleErr

 

   varPassword = Me!txtPassword & ""

   varName = Me!cboUser

 

   ' Make sure a user is selected

   If IsNull(varName) Then

       MsgBox "Please select a user", , _

           "Can't reset password"

       Me!cboUser.SetFocus

       GoTo ExitHere

   End If

   Set ws = DBEngine(0)

   Set usr = ws.Users(CStr(varName))

   strOldPassword = ""

   ' Change the password, passing an empty string for

   ' the old password argument

   usr.NewPassword strOldPassword, CStr(varPassword)

         

   MsgBox "Password successfully changed for " & _

     varName, , "Change Password"

 

ExitHere:

   Exit Sub

 

HandleErr:

 Select Case Err

   Case Else

     MsgBox Err & ": " & Err.Description, _

     vbCritical, " Error in Manage User Password"

 End Select

 Resume ExitHere

End Sub

 
 
I set up replication and it works fine, but I'd like to synchronize changes in one direction only. I can't figure out how to do this from the replication menus or in the Replication Manager.
 
If you want to synchronize your changes in one direction only, you need to use VBA code to accomplish the task. You won't find this option on the Access replication menu items or in the Replication Manager. In addition, design changes will always be bi-directional. In other words, design changes will always flow in both directions between replicas so that the entire replica set is consistent. Data changes, however, can be synchronized in either import-only or export-only modes.
 
You use the Synchronize method on a database object to synchronize in one direction only. The syntax is fairly simple:
 

database.Synchronize pathname [,exchange]

 
 
The exchange argument is optional -- if omitted, the default bi-directional synchronization is performed. In order to either import or export changes, you need to pass either the dbRepExportChanges or dbRepImportChanges intrinsic constant. The following procedure shows how to import only.
 

Sub SynchronizeExport(strSourceDB As String, _

  strTargetDB As String)

' Synchronize by exporting changes only

' Inputs:

'  strSourceDB

'   Fully-qualified path of source database

'  strTargetDB

'   Fully-qualified path of target database

 

   Dim ws As Workspace

   Dim db As Database

 

On Error GoTo HandleErr

   Set ws = DBEngine.Workspaces(0)

   Set db = ws.OpenDatabase(strSourceDB)

     

   'Synchronize replicas and export changes only

     db.Synchronize strTargetDB, dbRepExportChanges

'   Case 3  'Synchronize replicas (Import changes).

'     db.Synchronize strTargetDB, dbRepImportChanges

   db.Close

 

ExitHere:

   Exit Sub

 

HandleErr:

 Select Case Err

   Case Else

     MsgBox Err & ": " & Err.Description, _

         vbCritical, " SynchronizeExport"

 End Select

 Resume ExitHere

End Sub

 
 
Here's how to start an import-only synchronization:
 

Sub SynchronizeImport(strDestinationDB As String, _

  strSourceDB As String)

' Synchronize by importing changes only

' Inputs:

'  strDestinationDB

'   Fully-qualified path of destination database

'  strSourceDB

'   Fully-qualified path of source database

 

   Dim ws As Workspace

   Dim db As Database

 

On Error GoTo HandleErr

   Set ws = DBEngine.Workspaces(0)

   Set db = ws.OpenDatabase(strDestinationDB)

     

   'Synchronize replicas and import changes only

   db.Synchronize strSourceDB, dbRepImportChanges

   db.Close

 

ExitHere:

   Exit Sub

 

HandleErr:

 Select Case Err

   Case Else

     MsgBox Err & ": " & Err.Description, _

         vbCritical, " SynchronizeImport"

 End Select

 Resume ExitHere

End Sub

 
 
Peter Vogel
 
 
I want to be able to run Access reports, macros, and queries from Visual Basic, but I can't find any information on how to automate Access 2.0
 
Unfortunately, you can't automate Access 2.0 -- that feature wasn't added until Access 95. However, there's a workaround.
 
When you start Access, you can provide a variety of command line parameters. The /x parameter allows you to specify a macro to be run as soon as the database is open. So, you could create a macro called RunMyReport that runs your report and then opens Access from Visual Basic with the following command:
 

Dim intRet as integer

int = Shell("Access.EXE MyMDB.MDB /x:RunMyReport")

 
 
However, using this method can quickly create an enormous number of macros in your database. Instead of creating a macro for every occasion, you can create a single macro that calls an Access Basic routine. This Access Basic routine can take information from Access's command-line parameters (identified with by /cmd ) and use those to decide what to run.
 
So, you could create a macro called "RunAnything" and call it from your VB program like this:
 

Dim intRet as integer

int = Shell("Access.EXE MyMDB.MDB /x:RunAnything " & _

           "/cmd:Report:MyReport")

 
 
RunAnything would then call a routine like "RunFromCommandLine" below. This routine parses the object type from the commandline (retrieved through the Command function), and then executes the requested object. The code would look something like this:
 

Sub RunFromCommandLine

Dim strCLine as string

Dim strType as string

 

CLine = Command()

strType = Left(CLine, InStr(CLine, ":")-1)

strObject = Mid(CLine, Instr(Cline, ":")+1)

Select Case strType

 Case "Report"

         DoCmd OpenReport strObject, A_NORMAL

 Case "Query"

 etc

End Select

End Sub

 
 
Regardless of which macro you use, make sure that the last line of the macro is a Quit to close Access after your job is finished running.