October 2011 ~ LEARN MS-ACCESS TIPS AND TRICKS

Adding Data directly into External Databases

a.p.r. pillaiOctober 31, 2011database, External, Queries, Query 1 comment

Introduction.

The Back-End/Front-End database design is a common practice in Microsoft Access. The back end may consist of Access, dBase, SQL Server, Excel, or Paradox databases, with their tables linked to the front end (FE). Once the tables are linked, they behave just like native tables within Access—you can design queries, forms, and reports on them, and manage everything from the FE.

But what if we want to work without directly linking these tables to the FE? For example, can we create a query in the current database that uses an external table (not linked) from another Access database?

We have already explored this topic earlier and confirmed that it is indeed possible. You can check the following blog posts for practical demonstrations of this technique applied to different types of external data sources:

As you can see from the list above, methods 1 through 6 show different ways of bringing external data into Access without keeping the tables permanently linked to the database.

When working with dBase or FoxPro tables, the folder path where the table resides is treated as the database name.

If you’ve already read the second article, Opening dBase Files Directly, you should now have a good idea of what we are about to explore—namely, how to send output data into external databases without linking them to Microsoft Access.

Sample SQL for External dBase Table.

Before we dive into output operations, let’s take a closer look at a sample SQL statement that retrieves data from a dBase table through a query—without linking the table to the Access database.

Note: If you don’t already have a dBase table to test with, you can easily create one by exporting one or more of your Access tables into a separate folder on your disk. You don’t need to install a dBase application on your machine—the required ODBC driver files are already included with Microsoft Office.

SELECT Employees.* FROM Employees IN 'C:\MydBase'[DBASE IV;];

The SELECT query shown above will return all records from the Employees.dbf table located in the dBase database folder C:\MydBase. The text [DBASE IV;] specifies the database type and version. The clause


IN 'C:\MydBase' [DBASE IV;];

creates a direct connection to the Employees.dbf table—without establishing a permanent physical link. In other words, the data from Employees.dbf is accessible only through this query and not as a linked table in Access.

Up to this point, we have been focusing on how to bring data from external databases into Access without linking them. Now, let’s take it a step further and explore how to update or add data to these external databases.

Updating Data into an External dBase Table.

A sample SQL that updates an external dBase Table is given below:

UPDATE Products IN 'C:\MydBase'[DBASE 5.0;] SET Products.TARGET_LEV = 45
WHERE (((Products.TARGET_LEV)=40) AND ((Products.REORDER_LE)=10));

With the above SQL, we are updating the Products stock Target level to 45 from 40, for items with Re-order Level (Minimum Stock Level) is 10, and the current stock quantity target level is 40.

Appending Data into an External dBase Table.

Let us append some data from the Products_Tmp Table from the current MS-Access Database to the Products.dbf Table of C:\MydBase dBase Database. The sample SQL is given below:

INSERT INTO Products
  SELECT Products_Tmp.*
  FROM Products_Tmp IN 'C:\MydBase'[DBASE 5.0;];

IN Clause and Query Property Setting

Source Database and Source Connect Str Properties.

Let us examine the Property Sheet of one of the above Queries to check for any indication about whether the SQL IN Clause setting is in there or not.

Open one of the above Queries in Design View.
Display the Property Sheet of the Query. Press F4 or ALT+Enter to display the property sheet and make sure that it is the Query Property Sheet. Under the Title of the Property Sheet, there will be a description: Selection Type Query Property.
You may click on an empty area to the right of the Table on the Query Design surface to make sure that the Property Sheet displayed is the Query's Property Sheet, not the Table or Query Column Property Sheet. Check the sample image given below.

Right-click to open Large Image in New Tab/Window.
Check the Source Database and Source Connect Str Property Values. If you find it difficult to memorize the correct syntax of the IN Clause in SQL, then you can populate the respective values in these properties of the Query as shown. This will automatically insert the Connection String with the correct syntax in the SQL.

You can find the correct syntax for Access, Excel, Paradox, and ODBC connection strings for IBM iSeries machines, SQL Server, etc., from the above-quoted Articles.

Caution:

While the above methods offer convenient ways to work with external tables without permanently linking them to an Access database, relying on this approach too heavily can cause problems down the line if not managed carefully. To avoid confusion or data management issues, it is essential to maintain proper documentation of these queries and keep them safely stored for future reference.

Constant Location Reference Issues?

Let’s consider the case of an external Microsoft Access database. The SQL example below demonstrates how to append data directly into the Employees table of another Access database located on a LAN server. This type of operation is commonly performed on a scheduled basis—daily, weekly, or at other regular intervals.

INSERT INTO Employees IN 'T:\Sales\mdbFolder\Database1.accdb' 
SELECT Employees_tmp.*
FROM Employees_tmp;

Everything works smoothly, and over time, you may even forget about this particular query—or others like it. After six months, suppose you decide to shift or copy the databases from their current folder into a new location on the server (say, T:\Export\mdbFolder), while leaving a copy in the old folder as a backup. The database seems to function perfectly in the new location, no errors appear, and the users are satisfied.

However, some of your queries still contain the original connection strings hard-coded in their SQL statements. Since these were never updated to point to the new folder, the queries continue working against the old database copy in ...\Sales..., instead of the intended ...\Export... location. When users eventually report data discrepancies, it may not immediately occur to you that the culprit is the IN clause of your SQL. By the time you discover the oversight, you may already have wasted hours—or even days—troubleshooting the wrong problem.

Despite this drawback, the method remains useful when external databases are needed only occasionally. If the frequency of use is minimal, it is often better than keeping the external tables permanently attached to the front-end.

Users and Groups Listing from Workgroup Information File

a.p.r. pillaiOctober 25, 2011Groups, MS-Access Security, Users, Workgroup No comments

Introduction

How about taking a printout of Users and Groups from the active Workgroup Information File (the Microsoft Access Security File)?

This applies only to users of Microsoft Access 2003 or earlier databases that were implemented with Access Security. Such databases can still be opened and used in Access 2007 or Access 2010 without conversion.

To link to the Workgroup Information File (.mdw) from Access 2007, you can run the Workgroup Administrator program. For step-by-step instructions, please refer to the article: Running Workgroup Admin Program from Access 2007.

On a personal note, I am not particularly fond of the frequent version changes in Access. While new features are always exciting to explore, they often come at the cost of what we have already learned and implemented. For example, I still wonder why the familiar Menus and Toolbars were reorganized and rebranded as the Ribbon in Access 2007. Such changes often add confusion and waste time, forcing users to relearn tasks they could once do quickly. By the time users finally become comfortable with the new interface, a newer version appears—and the cycle starts all over again.

Upgrades are always welcome—provided they correct the drawbacks and bugs of earlier versions and implement genuine enhancements that users can easily locate and benefit from.

Coming back to the topic of printing Users and Groups from the active Workgroup Information File, Microsoft Access already provides a built-in option. The only limitation is that the listing is sent directly to the printer, which may not be ideal.

To use this option in Access 2003 or earlier:

Select Tools → Security → Users and Group Accounts → Users → Print Users and Groups.

In Access 2007 (with an Access 2003 or earlier database open):

Go to Database Tools → Administer → Users and Permissions → User and Group Accounts → Users → Print Users and Groups.

But why waste stationery when there’s a more flexible option? You can generate a list of Users and Groups and save it to a text file on your disk instead. Below is a simple VBA procedure you can use for this purpose. Copy and paste the following code into a Standard Module, then save and run it:

Database UsersList() Function.

Public Function UsersList() 
'------------------------------------------------------------- 
'Author : a.p.r. pillai 
'Date   : Oct 2011 
'Rights : All Rights Reserved by www.msaccesstips.com 
'Remarks: Creates a list of Users & UserGroups from the 
'       : active Workgroup Information File and saves 
'       : the list into a text file: UserGroup.txt 
'       : in the current database path 
'------------------------------------------------------------- 
Dim wsp As Workspace, grp As Group, usr As User 
Dim fs As Object, cp_path As String 
Dim a, txt 
Const ten As Integer = 10 

cp_path = CurrentProject.Path 
Set wsp = DBEngine.Workspaces(0) 
'Create a Text file: UserGroup.txt with FileSystemObject 
Set fs = CreateObject("Scripting.FileSystemObject") 
Set a = fs.CreateTextFile(cp_path & "\UserGroup.txt", True) 

'Write headings 
a.writeline "SYSTEM-DEFAULT GROUPS" 
a.writeline "-----------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

'List Default Admins & Users Group First 
For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
     a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
     For Each usr In grp.Users 
        txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
        a.writeline txt 
     Next: a.writeline crlf 
   End If 
Next 

'Groups, except Default Admins & Users 
a.writeline "ADMINISTRATOR-DEFINED GROUPS" 
a.writeline "----------------------------" 
a.writeline ("User-Groups  User-Names") 
a.writeline ("-----------  ----------") 

For Each grp In wsp.Groups 
   txt = grp.Name 
   If txt = "Admins" Or txt = "Users" Then 
      GoTo nextitem 
   Else 
      a.writeline txt & Space(ten - Len(grp.Name)) & Space(3) & "..." 
      For Each usr In grp.Users 
         txt = Space(Len(grp.Name) + (ten - Len(grp.Name))) & Space(3) & usr.Name 
         a.writeline txt 
      Next: a.writeline crlf 
   End If 
nextitem: 
Next 
a.Close 
'Open UserGroup.txt file with the list User list. 
Call Shell("Notepad.exe " & CurrentProject.Path & "\UserGroup.txt", vbNormalFocus) 

End Function

Running UsersList() Function

You can run this program either directly from the Immediate Window (Debug Window) or by calling it from the Click event of a Command Button on a Form.

When executed, the program will create a text file named UserGroup.txt in the same folder where the current database resides. Once the file is generated, it will automatically open in Windows Notepad for review.

Keep in mind that each time you run this program, the existing file will be overwritten. If you would like to preserve previous listings, remember to rename or move the file to a safe location before running the procedure again.

The result of a sample run of the program is given below:

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

If you need a printout of Users and Groups, then you may print it from the text file.

Access Security Key Diagram

a.p.r. pillaiOctober 17, 2011Access Security, Diagram, MS-Access Security 1 comment

Introduction - Access 2003.

Implementing Microsoft Access Security is a serious undertaking. Although this feature was deprecated starting with Access 2007, thousands of developers still rely on it in their applications. Microsoft’s documentation spans several pages, detailing the intricacies of this system, and it can be difficult to visualize how all the components work together to form the complete security framework.

To clarify this, I have created a diagram that consolidates the key elements of Microsoft Access Security. This visual overview provides a general understanding of the components involved and how they fit into the overall security structure.

Maintaining proper security is critical—not only to regulate user roles but also to safeguard data, protect the integrity of database objects, and ensure the security of VBA code.

For a deeper dive, you can explore the collection of articles on Microsoft Access Security available under the Security sub-menu on the main menu bar of this site.

Microsoft Access Security - Two Sections.

The first part of the Security elements (Workgroup File Id elements and User/Group Names, Personal IDs, and Passwords) resides within the Workgroup Information File.
Object-level access rights information that resides within the Database forms the second part.

When both parts are combined, consisting of fourteen security elements, it becomes the full security key of a User. See the diagram given below:

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

Workgroup FileID.

The first three elements—Workgroup Name, Organization, and Workgroup ID—serve as the unique identifiers of a Workgroup Information File. It is essential to keep this information safely stored after creating the file. If the file is ever lost, you will need these exact details to recreate it. Microsoft Access uses this combination of values to distinguish one Workgroup Information File from another.

User Specific Credentials.

The next three elements—User or Group Name, Personal ID, and Password—are user-specific credentials. Group accounts, however, contain only a Group Name and Personal ID; they do not use passwords. It is crucial to maintain a secure record of all User and Group Names along with their corresponding Personal IDs.

Group accounts are primarily a way to organize users, allowing access privileges to be assigned at the group level. Any user added to a group automatically inherits that group’s access permissions.

When you create a new Workgroup Information File, it includes, by default:

One User Account: Admin
Two Group Accounts: Admins and Users

The Admin user account is automatically a member of both groups. While the two group accounts (Admins and Users) cannot be deleted, new user accounts you create will always be added to the Users group by default. Similarly, the Admin user account itself cannot be deleted, but as a security precaution, it can be removed from the Admins group.

Members of the Admins group hold full administrative authority. They can assign permissions to objects and transfer ownership of objects (except the database object) to other user or group accounts.

Database Owner.

An important point to remember is that the owner of a database or object (i.e., the user who created it) has the same privileges as an administrator, specifically as a member of the Admins group. The owner of an object can assign permissions to other users or transfer ownership of that object to another user, just as an administrator would.

However, ownership of a database itself cannot be transferred. If someone wishes to assume ownership of a database, they must create a new database and then import all the objects from the original database. Provided they have sufficient permissions to do so.

Updating Sub-Form Recordset from Main Form

a.p.r. pillaiOctober 10, 2011Forms, Main Form, SubForm, updating No comments

Introduction

A Subform on a Main Form is a common design pattern used to display multiple related records alongside a single record from the main form. For example, order details linked to orders, bank accounts with their transactions, or student IDs with their mark lists. In short, nearly all databases with one-to-many relationships use this approach, as it provides a convenient way to view a large amount of related information in one place.

When a form is opened with a table or query as its record source, Access loads a parallel Recordset in memory, with each record uniquely bookmarked. This allows us to search and navigate through the virtual recordset without directly interacting with the underlying table or query. However, when a record is added or updated in this virtual recordset, the change is automatically saved back to the actual table. This in-memory recordset is known as the form’s RecordsetClone.

Working with Recordset Clone.

The sample VBA code below demonstrates how to work with a form’s RecordsetClone to locate a record based on specific criteria. In this example, we search for an order using its ProductID and then move the form’s current record pointer to that record:

Private Sub FindPID_Click()
'Find Record matching Product ID
Dim m_find, rst As Recordset

'validation check of search key value
m_find = Me![xFind]
If IsNull(m_find) Then
   Me.FilterOn = False
   Exit Sub
End If

'validation check of search key value
If Val(m_find) = 0 Then
  MsgBox "Give Product ID Number..!"
   Exit Sub
End If

'Find a record that matches the ProductID
'and make that record current using the recordset bookmark.
If Val(m_find) > 0 Then
   Set rst = Me.RecordsetClone 'declare the recordset of the form
   rst.FindFirst "ProductID = " & m_find
   If Not rst.NoMatch Then '<if record found then make that record current
      Me.Bookmark = rst.Bookmark
   End If
   rst.Close
End If

End Sub

We already have a blog post demonstrating how to search and filter data on a form using the RecordsetClone object. You can check that article [here].

In the earlier example, we worked with the RecordsetClone of the main form directly from its own module. But what if we need to access the RecordsetClone of a subform from the main form?

The key point to remember is this:

The subform displays only those records that are related to the current record on the main form.
Therefore, the subform’s RecordsetClone will contain only this filtered set of records—not all records from the underlying table or query.

By contrast, the RecordsetClone of the main form always includes all records from its record source, making it possible to search or update across the entire Recordset. For the subform, however, you can work only with the records currently visible in the subform for the active main form record.

Accessing Sub-Form Recordset from Main Form.

Let us try an example to learn how to access the sub-form record set from the main form and update records.

Import the following two Tables from the Northwind (or Northwind.mdb) database:
- Orders
- Order Details
Create a New Field for testing purposes.
Open the Order Details Table in the design view.
Add a new field: Sale Value with Data Type: Number and Field Size: Double.
Save the Order Details Table with the new field.
Design the Main form for the Orders Table in column format.
If you have used the Form Wizard and created a Split Form, then delete the Datasheet sub-form or table (Access 2007). Display the Property Sheet of the Form, find the Default View property, and change the Split Form value to Single Form.
Create a Sub-Form.
Design a Datasheet Sub-Form for Order Details Table.
Expand the Footer of the Sub-Form and create a Text box there.
Change the Name Property value to TotSale.
Write the expression =Sum([Sale Value]) in the Control Source property.
Save and close the Form with the name: Order Details.
Insert the Order Details sub-form in the Detail Section of the Orders Form below the Orders Form controls. See the image given below:
Right-click to open Large Image in New Tab/Window.

Sub-Form Link with the Main form.
While the Sub-Form is still in the selected state, display its Property Sheet (F4).
Set the Link Master Field property value to [Order ID].
Change the Link Child Field property value to [Order ID].
Add a Command Button above the sub-form as shown on the design above
.
Display the Property Sheet of the Command Button (F4 or Alt+Enter.
Change the Name property value to cmdUpdate
Select the On Click Event property and select [Event Procedure] from the drop-down list.
Click on the Build (...) button at the right end of the property to open the VBA Module of the Form.

Copy and paste the following VBA Code into the VBA Module, overwriting the skeleton lines of the Sub-Routine there.

Sub-form Module Code.

Private Sub cmdUpdate_Click()
Dim rst As dao.Recordset
Dim m_UnitPrice As Double
Dim m_Discount As Double
Dim m_Quantity As Long
Dim m_SaleValue As Double

'Address the recordset on the Sub-Form [Order Details]
Set rst = [Order Details].Form.RecordsetClone
rst.MoveFirst
Do While Not rst.EOF
    m_UnitPrice = rst![Unit Price]
    m_Discount = rst![Discount]
    m_Quantity = rst![Quantity]
    m_SaleValue = m_Quantity * ((1 - m_Discount) * m_UnitPrice)
    rst.Edit
    rst![SaleValue] = m_SaleValue
    rst.Update
    [Order Details].Form.Bookmark = rst.Bookmark
rst.MoveNext
Loop
rst.Close

Set rst = Nothing

End Sub

Create a Textbox to the right of the Command Button.
Set the Caption property value of the Child Label to Order Value:
Write the expression =[Order Details].[Form]![totSale]. This expression brings the Summary Value from the Text box, in the Footer Section of the sub-form, into the Order Form.
Save and close the Orders Form.

Open the Order Form.
Open the Orders Form in Normal View.
If the Sale Value column is not visible in the datasheet, use the bottom scroll bar to move to the right. Locate the Sale Value column, then click and hold the mouse button, drag the column to the left, and drop it within the visible area.

Now you can see that the subform displays the records related to the current Order ID on the main form. However, the new text box we created to the right of the command button is still empty because the [Sale Value] field on the datasheet has not yet been updated.
Click on the Command Button to calculate and update the Sale Value of each record on the Datasheet Sub-Form.

Now you will see that the Sale Value column of all records on the subform has been updated, and the summary value of these records is displayed in the text box to the right of the command button. Only the Order Detail records related to the current Order ID on the Orders form are updated. If you move to another record in the Orders table, its related Sale Value records will remain unchanged until you click the command button again.

In the code, the statement:


Set rst = [Order Details].Form.RecordsetClone

creates a reference to the RecordsetClone object of the subform [Order Details]. The following lines calculate the Sale Value (after applying any discount) and update it into the new field [Sale Value] in the Order Details table.

The line:


[Order Details].Form.Bookmark = rst.Bookmark

sets the subform’s Bookmark equal to the current record’s Bookmark in the recordset clone. This ensures that the record being processed in the recordset clone also becomes the current record on the subform.

If the subform contains many records, you may notice visible movement of the cursor as it rapidly shifts from one record to another, starting at the first record and progressing through to the last, while the update operation runs.

Macro and Temporary Variables
Easy-read Reports
Top N Records in Query
Attachment field in Access 2007
Embedded Macros in Access 2007

Easy Reference Access2003 Commands in Access2007

a.p.r. pillaiOctober 01, 2011Access2003, commands, How Tos, reference No comments

Introduction.

If you have been working with Access 2003 Menus and Toolbars, you know how easy design tasks can be when everything is familiar and right where you expect it. Work moves smoothly—until one day you hear some exciting news: Microsoft Access 2007 has been released. You can’t wait to try out its new features. With all the experience you’ve gained in Access 2003, you feel ready to explore and learn even more.

Finally, the day arrives—you install Access 2007 and begin exploring the new interface.

But after a few days, discomfort sets in. The Menus and Toolbars you relied on are gone, replaced with something called the Ribbon. Suddenly, you’re not sure where to find the commands you once used instinctively. Design tasks that were quick and easy in Access 2003 now feel awkward and slow. You realize that creating Forms and Reports is not going to be as straightforward as before—at least not until you become familiar with the new layout.

At this point, you’re ready to accept any help you can get to get back on track. Microsoft anticipated this struggle, too—and they included something to ease the transition, as long as you already know your way around Access 2003.

AccessMaps.xls File.

There is an Excel workbook named accessmaps.xls that displays the Access 2003 and Access 2007 menus and toolbar options side by side. On the left, you will see the familiar Access 2003 menu options, and in the corresponding right column, you will find their equivalents in Access 2007. This makes it much easier to locate the new Ribbon commands based on your prior knowledge of Access 2003.

To get started, locate the accessmaps.xls file and save a copy in a place where you can easily access it—your desktop is a good option.

Do the following to get the file:

Click on the Access Help button (the circular button with a white question mark in the blue background at the right end of the Menu Bar). Microsoft Access Help window opens.
Click on the Getting Started option, under the Table of Contents, to open the sub-topics.
Find Reference: Locations of Access 2003 commands in Access 2007 and click on it to open the details page in the right window.
Click on the New locations of familiar commands hyperlink to send you to the bottom of the document, or use the vertical scroll bar to move to the bottom of the document.
Click on the Access Ribbon mapping workbook, and a File Download dialog box opens up asking whether you want to open or save the accessmaps.xls file.
You'd better save a copy to your Desktop so that whenever you want it, you can find it easily.