August 2011 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

If you are serious about implementing User-Level Security in Microsoft Access 2003 (mdb) databases and want to continue using them in Access 2007 security settings intact, avoid converting them to the Access 2007 format. Once upgraded, the security model changes completely, and User-Level Security is no longer supported in the accdb databases.

If you have already converted an MDB database into ACCDB format and lost all user-level security settings, the situation is not entirely hopeless. If you have a backup of the original .mdb file, you are in luck—simply restore the backup and continue using it in Access 2007, retaining your User-Level Security.

If no backup exists, you can still downgrade the database. Use the Save As… option from the Office Menu (top-left corner), select an earlier format such as Access 2002–2003 or Access 2000, and save the active database as a new .mdb copy. However, this will not automatically restore the lost security settings. You must reapply them manually, assigning permissions to each database object either at the group level or for individual users.

Running the Workgroup Administrator.

In either case, you must join the Workgroup Information File (a database with the file extension .mdw) from Access 2007 to attach your database Users/Groups and continue using them with your .mdb databases, or to implement new security profiles.

When you open an .mdb database in Access 2007, the Users and Permissions menu will appear under Database Tools. However, your database Users and Groups will not be visible under this option unless you first join the Workgroup Information File that you were using in an earlier version of Access.

In Access 2003, you can do this from Tools → Security, which provides an option to run the Workgroup Administrator program to create or join a Workgroup Information File. In Microsoft Access 2000, this program is located in the Language folder (…\1033 for U.S. English) of MS Office:

C:\Program Files\Microsoft Office\Office\1033\WrkgAdm.exe

Running this utility allows you to join the appropriate Workgroup Information File and manage security accordingly.

However, in Microsoft Access 2007, none of these menu options are available. The only way to access the Workgroup Administrator is by running the following command, either from VBA or directly in the Immediate Window:

DoCmd.RunCommand acCmdWorkgroupAdministrator

You can execute this command manually from the Immediate Window (open the VBA editor with Alt+F11, then press Ctrl+G to display the Immediate Window), or programmatically from a Command Button Click event on a form. This will allow you to create a new Workgroup Information File or join an existing one on the server.

Once you have joined the Workgroup Information File, you can continue using .mdb files with their existing User-level security in Microsoft Access 2007, or manually restore any lost security settings.

Technorati Tags: Microsoft Security

Earlier Post Link References:

Introduction.

Normally, we use filter settings on Forms or Reports to extract records based on specific conditions. This can be accomplished in several ways—for example:

Adding a WHERE condition (without the keyword WHERE) in the parameter setting of the OpenForm macro action.

Specifying a condition in the parameter of the ApplyFilter action in a macro.

Supplying criteria in the DoCmd.OpenForm method:

DoCmd.OpenForm "myForm", acNormal, , "EmployeeID Between 5 AND 10"

Using Filter by Selection directly on a form.

Setting a Query as the form’s record source.

What many developers overlook, however, is that the Filter property of a table itself can also be used to limit records when the table is opened in Datasheet View.

For instance, if you are reviewing someone else’s project and notice that a table displays only a few records in Datasheet View—while you are told the table should contain hundreds—you may wonder what happened to the rest. In such cases, the hidden culprit might be an active Table Filter.

Setting a Filter on a Table.

We will now explore how this filtering trick works on a Table, for a change. If you have ever applied a filter condition directly on a Form, then you already know enough to do the same with a table—it works almost identically.

To try this out, we need some ready-made data. The Northwind sample database is an excellent choice, but you may also experiment with any table from your own database.

Import the Order Details Table from the Northwind sample database.
Open the Table in the Design view.
Press F4 to display the Property Sheet of the Table.

Right-click to open Large Image in New Tab/Window.

Table Property Sheet View.
Find the Filter Property and type [Order ID] Between 35 AND 45 into the property (in Access 2007). For earlier Access Versions, type [OrderID] between 10249 AND 10251.
Set the Filter On Load property value to Yes.
Save and close the Table Structure.
Open the Table in Datasheet View to see the Filter in action.

The output records displayed will be only those with Order IDs between 35 and 45. If you design a Quick Form or Quick Report from this table, the Record Source property will be set automatically. It will include an SQL SELECT statement containing the WHERE condition derived from the table’s Filter property.

However, keep in mind that if you later modify the filter condition on the table, the change will not be reflected automatically in the Record Source SQL of the Form or Report. Once created, the Form or Report retains the original filter condition unless you manually update it. Automating through VBA.

Like most features in Microsoft Access, you can automate this process with VBA to change the filter criteria at the click of a button. However, there is a small catch, which I’ll explain shortly, so that you understand why it’s important. After all, we’re talking about setting a simple filter condition on the Filter property of a table’s structure.

There are two ways to address the Filter property of a table in VBA.

Front-door approach: Accessing the property through the path TableDef.Properties("Filter").
Backdoor approach: Accessing it via Containers("Tables").Documents("TableName").Properties("Filter").

As you can see, the second method is more elaborate, which is why I refer to it as the backdoor method. We’ll try out examples of both approaches.

The second approach becomes especially useful when working with Forms or Reports—for example, when generating a list of all Forms or Reports, or when you want to change the name of a Form. In an earlier blog post, I demonstrated how to create a user-defined (custom) property to store values, such as the last record you worked on, so that the Form can reopen at that record and allow you to continue seamlessly. [Click here to learn more.]

Example-1:

Copy and Paste the following VBA Code into a Standard Module of your Database.

Public Function TableFilter1(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database
Dim Tbldef As TableDef

Set db = CurrentDb

Set Tbldef = db.TableDefs("Order Details")
Tbldef.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
Tbldef.Properties("FilterOnLoad").Value = True
db.TableDefs.Refresh

End Function

Run the above sample Code from the Debug Window or from a Command Button Click Event Procedure, like the sample run given below:

TableFilter1 35,45

NB: The above code and sample run are demonstrated using the Order Details table in Access 2007.
If you are working with an earlier version of Access, make the following adjustments:

Change the field name from [Order ID] to [OrderID] (without the space).

In the sample run, use:
TableFilter1 10249, 10251
instead of the values 35, 45 for the OrderID range.

If you have not tried out the manual filter method explained above or removed the filter criteria setting of the Filter Property then you will run into problems with the above program reporting an Error message stating that the Filter Property not found.
When you implement the VBA method see that an initial criteria setting is set in the Filter property of the Table. Without the criteria setting the Filter Property will not be visible in VBA.

Example-2:

Copy and paste the following VBA Code into the Standard VBA Module and run the Code in the same way as Example-1 with a different set of OrderIDs:

Public Function TableFilter2(ByVal OrderStart As Long, ByVal OrderEnd As Long)
Dim db As Database, ctr As Container, doc As Document

Set db = CurrentDb

Set ctr = db.Containers("Tables")
Set doc = ctr.Documents("Order Details")
doc.Properties("Filter").Value = "[Order ID] >=" & OrderStart & " AND [Order ID] <=" & OrderEnd
doc.Properties("FilterOnLoad").Value = True
doc.Properties.Refresh

End Function

Technorati Tags: Microsoft