Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Adding Data directly into External Databases


 The Back-End, Front-End database designs are common in MS-Access.  The back-end can be MS-Access, dBase, SQL Server, Excel, or Paradox databases with their linked Tables.  Once the tables are linked they function as if they are the native tables of the Access Database.  You can design Queries, Forms, Report on them and manage them from FE.

But, can we manage without directly linking them to the FE?  For example; can we create a Query in the current database using an external Table (not a linked table) from another MS-Access database?

This topic we have already discussed earlier proved that it is possible.  Check the following Blog Posts to learn this trick on different types of external data sources:

  1. Opening External Data Sources
  2. Opening dBase Files directly
  3. Opening Excel Database directly
  4. Display Excel Values directly on Form
  5. Database Connection String Property
  6. Source ConnectStr Property and ODBC
  7. Link External Tables with VBA
  8. Lost Links of External Tables
  9. MS-Access Live data in Excel
  10. MS-Access Live data in Excel-2

As you can see from the above list that serial numbers 1 to 6 methods are used for bringing external data into Access in different ways without keeping them linked to the Access Database.  When working with dBase or FoxPro Tables, the path to the Folder, where the dBase/FoxPro Table is located stands as the database name.

If you have gone through the second Article Opening dBase Files directly, then you already know what we are going to explore here and have a general idea by now as to how to send output data into external databases without linking them to MS-Access.

Sample SQL for External dBase Table.

Before going into that, let us take a closer look at the sample SQL that brings in data from a dBase Table through a Query, without linking the table into the MS-Access database.

NB:  If you don’t have a dBase Table, to try out these examples, you can create dBase Tables by exporting one or more of your Access Tables into a separate folder on your disk.  You don’t have to install a dBase Application on your machine. Necessary ODBC Driver Files are already installed by MS Office on your machine.

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

The SELECT Query will return the entire records from the Employees.dbf Table from the dBase database ‘C:\MydBase’.  The text  [DBASE IV;] is the database type and version indicator.  The SQL IN 'C:\MydBase'[DBASE IV;]; clause creates a direct link to the Employees.dbf Table without a physical link.  That means the Employees.dbf data are available to other processes through this query only.

Throughout the above articles, we were discussing bringing data from external databases, without keeping them linked to Access.  This time we will explore how to update or add data to the external databases.

Updating Data into 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 External dBase Table.

Let us append some data from 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:

  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.

  1. Open one of the above Queries in Design View.

  2. 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.

  3. 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 Query's Property Sheet, not the Table or Query Column Property Sheet. Check the sample image given below.

  4. 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 the 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.

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


Even though the above methods provide some convenient way to manage external tables, without keeping them permanently linked to the MS-Access database, extensive use of this method can lead to issues at a later stage if you are not careful. It is important that you maintain some form of documentation of these Queries for safekeeping.

Constant Location Reference Issues?

Let us take the example of an external MS-Access database itself. The SQL given below appends some data into the Employees table in another Microsoft Access database on LAN Server directly. This is a routine process done daily or weekly etc.

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

Everything works just fine and you forgot about this specific Query or other Queries similar to this one. After about six month’s time you thought of shifting or copying the databases from the current location into another Folder on the Server (say T:\Export\mdbFolder), leaving a copy in the old folder as a backup, and installed in the new . . .\Export\ folder. Everything was found to work OK without triggering any error in the new location and the Users are also happy.

Your database has a few Queries with the above Connection Strings in their SQL, which never crossed your mind of attending to them and changing the SQL to point them correctly to the databases to the new location. The Queries will keep on servicing the Table in the old location . . .\Sales\. . . , instead of the Table in . . .\Export\. . . Location. The data missing problem when reported by the user may not trigger the Query IN Clause button in your mind immediately and you may be pulling your hair to find out what went wrong, wasting hours or days, till you arrive at the accident spot.

In spite of these drawbacks, it is a good way to use the external databases when needed only, if the frequency is minimal, rather than keeping them always attached to the FE. 


Users and Groups Listing from Workgroup Information File


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

This is only for users of Microsoft Access 2003 or earlier version databases implemented with Microsoft Access Security. You can use earlier version databases in Access2007 or in Access 2010 without converting them.

You can run the Workgroup Administrator program from Access2007 to link to the Workgroup Information File (.mdw). Please refer to the Article: Running Workgroup Admin Program from Access2007 for details.

Frankly speaking, I am not happy with the idea of frequent changes in versions, irrespective of what advantages they provide. It is true that I am really excited to learn new features, but not at the cost of what we have already learned and implemented. I still wonder why the Menus and Toolbars are jumbled around and presented with fancy names like Ribbons in Access2007. These kinds of changes will only add to the confusion and waste of time to find out things we are already familiar with in the earlier versions.  By the time users are out of the woods with the new changes, they are slapped with a new version and start all over again looking for things, which were in easy reach earlier.

Upgrades are good if drawbacks and bugs of earlier Versions are corrected and implemented with real enhancements, which users can find them easily. 

Coming back to the topic of taking print out of Users and Groups, there is an option already available in Access.  The only problem is that the listing will be dumped directly into the Printer.

To use this option select Tools - - > Security - - > Users and Group Accounts - - > Users - - > Print Users and Groups.  

In Microsoft Access2007, open a database of Access2003 or earlier version first, then select Database Tools - - > Administer - - > Users and Permissions - - > User and Group Accounts . . . - - > Users - - > Print Users and Groups.

But, why you should waste stationery when you have a better option?  You can create a list of Users and Groups in a Text file and save it on your disk with the VBA Program given below.  Copy and paste the following Code into a Standard Module and save 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 "-----------------------" 
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 

'Groups, except Default Admins & Users 
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 
      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 
'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 the Code directly from the Debug Window or from a Command Button Click on a Form.  The Text File will be created in the current database folder with the name UserGroup.txt and it will be opened in Windows Notepad automatically. 

Whenever you run this program the earlier file will be over-written.  You may rename the file, for the safe-keep.

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

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


Access Security Key Diagram

Introduction - Access 2003.

As you are aware implementing Microsoft Access Security is a serious business. Even though this has been deprecated from Access2007 and later versions, thousands of Access Developers are still using this feature.  There are several pages of MS-Access Help text explaining the complexities of this feature and it is difficult to visualize how all of them fit together to form the security key.

I made an attempt here to put the main elements of Microsoft Access Security elements together into the form of a picture so that we will get a general idea of what all components are involved and where they are all kept for implementing Microsoft Access Security.  

It is important to regulate Users' roles and maintain the security of data, the integrity of various objects, and the VBA Code.

We already have several Articles discussing Microsoft Access Security. You can access these articles from the Security Sub-Menu from the Main Menu Bar on this site.

Microsoft Access Security - Two Sections.

  1. 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. 
  2. Object-level access rights information that resides within the Database forms the second part.

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

Workgroup FileID.

The first three elements: Workgroup Name, Organization & Workgroup Id form the unique Workgroup Information File identification elements. You must keep this information in a safe place, after creating the Workgroup Information File.  If you somehow got lost this file you must give this specific information to create this Workgroup Information File again.  MS-Access distinguishes one Workgroup Information File from the other using this unique information.

User Specific Credentials.

The next three elements: User or Group Name, Personal ID & Password, are User-specific information.  Group-Account have only Group Names and Personal IDs, no passwords.  It is very important that you keep a record of the User/Group Names and their Personal ID information in a safe place.

The Group Security Account is only a means of organizing Users into different groups so that their access privileges can be assigned at the Group level.  Users inherit the access privileges assigned to the Users’ Group Account when they are added to the Group.

When you create a new Workgroup Information File, by default, there will be only one User Account: Admin and two Group Accounts: Admins & Users.  The Admin User account is a member of the Admins & Users Group Accounts.  You cannot delete these two Group Accounts and any new User Account, you create will be a member of the Users Group Account by default.  You cannot delete the Admin User Account either, but it can be removed from the Admins Group Account as part of a security measure.

Members of the Admins Group have the full administrative power to assign permissions to Objects and transfer ownership of objects (except the Database Object) to any other User/Group accounts.

Database Owner.

Here, one important aspect you have to keep in mind is that the Owner of the Database (the User who created the Database)/Object has equal privileges of an Administrator, a member of the Admins Group Account.  The owner of an object can assign permissions, like an administrator, for other Users or transfer his ownership of the object to another User.  

Ownership of a Database Object cannot be transferred to anybody.  But, one who likes to take ownership of a Database, must create a new database and import all the objects (if, he has enough privileges to do that) into the new Database.


Updating Sub-Form Recordset from Main Form


Sub-Form on the Main Form is the common design that we follow to display several related records on the record on the Main Form. Order Detail records related to Orders, Bank accounts and their transactions, Mark-List and Student's Id, and in short almost all databases have this kind of one-to-many relationship, and records are displayed in this way to get a quick view of maximum information.

When we open a form with a Table or Query as Record Source the Form when loaded opens a parallel record set in memory with unique bookmarking of each record. We can search through this virtual record set in memory without directly touching the actual Table or Query. But, when we add or update a record in this virtual record set that change is saved in the actual table. We call this virtual record set the RecordsetClone of the Form.

Working with Recordset Clone.

The sample VBA Code given below shows how to address the Form's RecordsetClone to find a record based on some criteria.

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
End If

End Sub

We have a Blog Post on Data search and filter on Form through the above code. If you would like to take a look at it then click here.

In the above example, we were using the RecordsetClone Object of the Main Form on the main form module itself. But how do we address the RecordsetClone Object of the Sub-Form, from Main-Form, to update the current record set on the Sub-Form? Here, what we have to keep in mind is that the records, which appear in the Sub-Form Datasheet View are related to the current record on the Main Form and only those records can be accessed for whatever operation we planned to do with them. Not all the records of the Record Source Table/Query.

On the Main Form, all records of the Record Source Table/Query can be accessed through the RecordsetClone object, for search or update operations. But, the RecordsetClone of the sub-form will have only those records displayed on the sub-form, related to the current record on the main form.

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.

  1. Import the following two Tables from the Northwind (or Northwind.mdb) database:

    • Orders
    • Order Details

    Create a New Field for testing purposes.

  2. Open the Order Details Table in the design view.

  3. Add a new field: Sale Value with Data Type Number and Field Size Double.

  4. Save the Order Details Table with the new field.

  5. Design the Main form for 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 (Access2007). Display the Property Sheet of the Form, find the Default View property, and change the Split Form value in Single Form.

    Create a Sub-Form.

  6. Design a Datasheet Sub-Form for Order Details Table.

  7. Expand the Footer of the Sub-Form and create a Text box there.

  8. Change the Name Property value to TotSale.

  9. Write the expression =Sum([Sale Value]) in the Control Source property.

  10. Save and close the Form with the name: Order Details.

  11. Insert the Order Details sub-form in the Detail Section of the Orders Form below the Orders Form controls. See the image given below:

    Sub-Form Link with the Main form.

  12. While the Sub-Form is still in the selected state display its Property Sheet (F4).

  13. Set the Link Master Field property value to [Order ID].

  14. Change the Link Child Field property value to [Order ID].

  15. Add a Command Button above the sub-form as shown on the design above

  16. Display the Property Sheet of the Command Button (F4 or Alt+Enter.

  17. Change the Name property value to cmdUpdate

  18. Select the On Click Event property and select [Event Procedure] from the drop-down list.

  19. Click on the Build (. . .) Button at the right end of the property to open the VBA Module of the Form.

  20. 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
    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![SaleValue] = m_SaleValue
        [Order Details].Form.Bookmark = rst.Bookmark
    Set rst = Nothing
    End Sub
  21. Create a Textbox to the right of the Command Button.

  22. Set the Caption property value of the Child Label to Order Value:

  23. Write the expression =[Order Details].[Form]![totSale]. The idea of this expression is to bring the Summary Value from the Text box, we have created in the Footer Section of the sub-form, into the Order Form.

  24. Save and close the Orders Form.

    Open the Order Form.

  25. Open the Orders Form in normal View.
  26. If the Sale Value column is not appearing in the visible area of the Datasheet then move the bottom scroll bar to the right, highlight the Sale Value column, click and hold the mouse button and drag it to the left and place it into the visible area.

    You can now see the Sub-Form shows some records related to the Order ID on the main Form. The new Text box we have created to the right of the Command Button is empty because we have not updated the [Sale Value] field on the Datasheet.

  27. Click on the Command Button to calculate and update the Sale Value of each record on the Datasheet Sub-Form.

Now you will find the Sale Value column of all records on the sub-form, updated and the Summary value of all records appearing in the Text box to the right of the Command Button. The records updated are only those Order Detail records related to Order Id on the Orders Form. If you move the Orders table record forward their related record sale value is not updated. They will be updated only when you click on the Command Button.

The statement in the above code Set rst = [Order Details].Form.RecordsetClone is setting a reference to the RecordsetClone Object of the Sub-Form [Order Details]. Subsequent lines calculate the Sale Value after Discount, if any, and update the sale value into the new field [Sale Value] we have created in the Order Details Table.

The statement [Order Details].Form.Bookmark = rst.Bookmark overwrites the form's Bookmark with the record set's current record Bookmark. The result of this action is that the current record processed in the record set clone becomes the current record on the sub-form. If you have several records on the sub-form you can see some visible action on the sub-form moving the cursor from one record to another very fast, starting from the first record to the last one by one as the updating action progress through the records.


Easy Reference Access2003 Commands in Access2007


You are very familiar with Access2003 Menus and Toolbars. Design tasks are so easy when you know what to look for and where they can be located.  Everything runs so smoothly and one day you hear some exciting news, Microsoft Access2007 was released.  You can’t wait to see it and want to start using its exciting new features. We got lots of ideas from Access2003 and are ready to learn more and new things.

Finally, that day has come and you have got Microsoft Access2007 installed on your machine.  Started exploring the new interfaces to get a feel of things in the new version.

After a few days, you are uncomfortable with the whole setup.  Don’t know where to locate the Menus and Toolbar buttons (new name Ribbon) you were so familiar with the earlier version of Access.  You suddenly realize that the design tasks of Forms and Reports are not going to be as easy as before, at least till you are familiar and comfortable with the new version of Access.

I know, you are ready to take any possible help from someone to get going.  I think Microsoft also knows about your hardships and they have something for you to get around this issue, provided if you know what to look for in Access2003. 

AccessMaps.xls File.

There is an Excel Workbook: accessmaps.xls with Access2003 and Access2007 Menus and Toolbar Options side-by-side.  You can select the Access2003 Menus in Worksheets File, Edit, etc., and at the left side locate menu options of Access2003 and at the right column, you will find the corresponding Access2007 option, easy?

Well then, find the accessmaps.xls file and save a copy where you can find it easily, probably on your desktop.

Do the following to get the file:

  1. 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.
  2. Click on the Getting Started option, under the Table of Contents, to open the sub-topics.
  3. Find Reference: Locations of Access 2003 commands in Access 2007 and click on it to open the details page in the right window.
  4. 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.
  5. 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.
  6. You better save a copy to your Desktop so that whenever you want it you can find it easily.

If you could not locate the AccessMaps.xls file through the above procedure, then download it from the following link:





Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code