What is MS Access?
Microsoft Access is relational database management system (RDBMS). Whether users are creating a stand-alone desktop database for personal use, departmental use or for an entire organization, Access offers an easy-to-use database for managing and sharing data. Access 2000 brings not only the traditional broad range of easy data management tools but also adds increased integration with the Web for easier sharing of data across a variety of platforms and user levels and additional ease-of-use enhancements to assist with personal productivity.
Access 2000 allows easily sharing information via the corporate intranet and the ability to easily host a database within the browser. This combines the power of a desktop database with the power of the web.
What are Macros?
An Access Macro is a script for doing some job. For example, to create a button, which opens a report, you could use a macro, which fires off the "OpenReport" action. Macros can also be used to set one field based on the value of another (the "SetValue" action), to validate that certain conditions are met before a record saved (the "CancelEvent" action) etc. Each line of a macro performs some action, and the bottom half of the macro screen provides the details of how the action is to apply.
What are Modules?
This is where you write your own functions and programs if you want to. Everything that can be done in a macro can also be done in a module, but you don't get the Macro interface that prompts you what is needed for each action. Modules are far more powerful, and are essential if you plan to write code for a multi-user environment, since macros cannot include error handling. Most serious Access users start out with macros to get a feel for things, but end up using modules almost exclusively. On the other hand, if your needs are simple, you may never need to delve into the bottomless depths of Access modules.
Microsoft Access Query Optimization
1. Display the minimum number of fields in a query. Set criteria dependant fields that are not required in the dynast to "not shown".
2. Index all restriction based fields, all fields included in expressions, all sorted fields and all join fields.
3. Use primary keys or unique indexes wherever possible.
4. Use numeric rather than text primary keys.
5. Use non-blank unique fields.
6. Avoid the use of If() function in queries.
7. Avoid domain aggregate functions such as Dlookup().
8. Make careful use of Between and Equal to, rather than > or < speeds up queries.
9. Use fixed column headings in Crosstab queries.
10. For reports based on queries use Portrait view in preference to Landscape and select Fast Laser Printing to Yes (View,Options,Other Properties).
11. Use Make table queries for running reports on static data. These are called snapshot reports.
12. Use Count (*) rather than Count(Column).
13. When creating restrictions on a joined column in one-to-many relationships, test out the comparative performance when placing the restriction on the "one" side or the "many" side. The "one" side is not always the fastest - the "many" may have markedly fewer records.
14. Short table and field names run faster than long names.
15. Normalize tables - join strategies execute more quickly on smaller tables.
16. Denormalize tables - reduce the number of joins. Get the balance right between normalization and denormalization by experiment.
17. Avoid the use of Distinct Row queries - Union queries do not need the distinct row feature as they are automatically return unique fields unless set to Union All.
Speeding Up Combo Boxes on Forms and the loading of Forms that contain Combo Boxes.
Combo Boxes are one of the easiest way to allow the user to pick from a category of input options or to pick a specific record. However, if the combo box is filed with data such as a list of customers, products or orders, or any other data which can run into the thousands of records, or is based on a complex query, this can dramatically slow down both the time it takes to open the combo box itself, and the time it takes to open and load the Form which contains the combo box.
Form loading time can become a problem because Access runs the query and sorts the records to fill the combo box at least twice just when opening the form. If you have multiple combo boxes on your form, this can make the form seem sluggish. Here's some hints to speed up the process of form loading and opening of combo boxes with large number of records:
1. It may sound self-evident but make sure that the sort and parameter fields in the underlying query are indexed fields in the tables used.
2. Limit the number of fields returned to the combo box in the underling query.
3. Leave the Row Source for the combo box blank in its property sheet. Then add an event procedure to the "OnEnter" event of the combo box. In that procedure set the combo box's RowSource, to the desired query or table by specifying:
Me!ComboBoxName.RowSource = "queryortablename"
This will make the form load faster by not running the combo box query until the user requests the data. (The combo box query is usually run again when the user selects it anyway.)
4. Set the "Auto Expand" property to No.
5. Limit the number of rows the combo box returns. Displaying 50 records is much faster than filling a combo box with 1000 or more records. There are many strategies to accomplish limiting the number of records that a combo box is filled with when it loads. One option is to make the combo box query dependent on, or filtered according to criteria of a text field on the same form. This is easy to do if you are using an alphabetical list such as a list of Customers.
Place a condition in the criteria field of the query which fills the combo which looks at another text control on the form for the first (and subsequent letters entered into the text box) such as:
Like [Form]![txtAlpha] & "*"
(Note you don't need the formal form name in the criteria, Access will look to the current form for the field,) then in the OnChange event of the field txtAlpha enter code in an event procedure similar to "MyCombo.Requery". Another advantage to this technique is that since the query returns no rows when the form is opened, the form loads faster as well. 6. If the Bound Column of the Combo Box is a numeric column of the query, don't hide this column.
Use the "Tag" Property of a Form or Report Control to manage Form or Report functionality.
The "Tag" property of a form or report control is not used by Access directly, and can be used by the programmer to manage how a form or report functions in many ways. As an example, you may have a Form, which under certain circumstances you want to show one set of multiple fields and under other circumstance show another set of multiple fields. You could write an event procedure to hide or show the controls, similar to the following list, in the AfterUpdate event of an option group:
If Opt=1 then
The former type of code requires you to change the code if you add more controls to those you want to hide or show. Alternately you could type in either "Opt1" or "Opt2" to the "Tag" property of each control you want to change whether to hide or show according to the option by the user. Then you could write your code, entering it to the "After Update" event of an Option Group, to loop through the controls on the form and make the changes to the identified controls automatically. The code might look like this:
Dim Frm as Form
Dim I as integer
Set Frm = Me
If Opt=1 then
For I = 0 to Frm.count -1
If Instr(Frm( I ).tag, "Opt1")>0 then Frm( I ).visible = True
If Instr(Frm( I ).tag, "Opt2")>0 then Frm( I ).visible = False
For I = 0 to Frm.count -1
If Instr(Frm( I ).tag, "Opt1")>0 then Frm( I ).visible = False
If Instr(Frm( I ).tag, "Opt2")>0 then Frm( I ).visible = True
This later set of code also allows you to add or delete controls from the those that are visible or hidden without changing your code, simply by adding one of the options to a controls "Tag" property. (Note, we check whether the Tag property has "Opt1" or "Opt2" in the string contained in the Tag property rather than checking whether the Tag is equal to either "Opt1" or "Opt2" because we may place more than one-action tags in the Tag property of the same control.)
How to create your own custom Progress/Status Meter/Bar Chart
You may find a need for creating a "Progress Meter" on your Form, separate from the status bar progress meter, which is callable, by using the sysCmd function in Access. Using two label controls, directly on top of one another in the form can create a Progress meter. Here's how you do it:
1. Create a label control, with a sunken style, (we'll call this lblbase) and make it's backcolor transparent, and forecolor to black.
2. Create a label control, of 0.00 width, identical in height to the baselbl (we'll call this lblmeter); align the lblmeter exactly with the left edge of the lblbase control, and send it to the back (i.e. behind the lblbase.) Make its edges transparent.
3. To update the progress meter place the following code in the form's module and send it the current and total amounts to measure progress as appropriate (you can use a timer event or place the calls in looping code etc.): Sub updatemtr (currentamt, totalamount)
' This function changes the color based on progress.
' You set the back color of lblmeter to be a single color if desired.
Dim MtrPercent as Single
MtrPercent = currentamt/totalamount
Me!lblbase.Caption = Int(MtrPercent*100) & "%"
Me!lblmeter.Width = CLng(Me!lblbase.Width * MtrPercent)
Select Case MtrPercent
Case Is < .33
Me!lblmeter.BackColor = 255 'red
Case Is < .66
Me!lblmeter.BackColor = 65535 'yellow
Me!lblmeter.BackColor = 65280 'green
4. Note this same tip can also be used to create a simple horizontal bar chart on a report, and is especially handy if you don't know what the total amount might be before you run the report. (The total amount would be required to be known a priori if you were to use MS Graph to create the chart, because you would have to set the scale when creating the chart; with this method, you don't have to know the total amount before the report is run.)
How to control Tabbing and "Enter" key movement in and out of a sub-Form and a Main Form.
When a user presses the tab key or the enter key when they are in the last field on a main form, where the next field is a sub form, this will automatically result in the cursor moving to the first control in the subform. However, if the user then holds down the Shift key and presses the tab key, (to move backward in the form,) rather than re-entering the main form, the cursor will either move to the last field on the sub form (if on the first record of the sub form,) or to the previous record in the sub form. Similarly, if the user is on the last control in the sub form and presses the enter key, they will be taken to the next record in the sub form, rather than to next control in the main form. This behavior can be modified to progress directly between the main form and current record of the sub form by using event procedures in the "On Key Down" event of the sub-Form's first and last controls. To do this uses the code below. (Note the "Parent" property of the sub form refers to the main form):
1. On the Declarations page of the Form's module enter the following lines: Const Key_Tab = &H9
Const SHIFT_MASK = 1
2. In the "On Key Down" event of the first control on the subform create an event procedure and enter the following code:
ShiftDown = (Shift And SHIFT_MASK) > 0
If KeyCode = Key_Tab Then
If ShiftDown Then
3. In the "On Key Down" event of the last control on the subform create an event procedure and enter the following code:
ShiftDown = (Shift And SHIFT_MASK) > 0
If KeyCode = Key_Tab Then
If ShiftDown = 0 Then
KeyCode = 0
ElseIf KeyCode = Key_Return Then
KeyCode = 0
Else Exit Sub
To stop the user from re-entering the main form, without moving to the next record, simply remove the lines of code, which set the focus on a control of the Main Form.
Access 95 and 97:
If you need to trap key actions and movement in Access 95 and 97, especially if it involves multiple controls and/or subforms (e.g. subforms on a tab control,) you can save time and coding by creating a single function for the form by setting the KeyPreview property of the form to "Yes", and writing a single function in the OnKeyDown event of the form. You can use a select case routine to test the CurrentControl.name and set your form movement from there.
There is a simple sample db of how to implement this located in the ACG Free File Library which you can download. The files called "Inandout" ACG Free Files
Do Your Forms Which Have Filter Options Take "Forever" to close in Access 97?
Access 97 introduces many new features including the option to filter by form. Many developers also add options and methods to set filters on a form to search for or limit the form's records to a selected set.
In addition to its new functionality, Access 97 introduces a new form property called "Filter", which is set each time you set a filter on the form. The problem is that when you close the form with a filter remaining set on the form, that filter is set as the form's "Filter" property, and Access, sensing the form has changed from when it was first opened, saves the form on closing it, which appears to the user to slow down performance.
To rectify this problem is fairly simple since most developers add a "Close" or "Exit" button to their form. In the code which you use in the event procedure for this button, make sure that you include the "acSaveNo" option to the close command as in: DoCmd.Close acForm, "frm_MyForm", acSaveNo.
Your form will close much faster and preserve the feel of fast performance.
If you are using the control wizards to add the proper code to your command buttons in Access 97, you may need to change the code. Many of Access 97's control wizards place old style Access 2 and 95 "DoCmd.DoMenuItem" code in your event procedures (as withnessed by the A_MENU_VER70 tag,) and this can be updated to the code above.
General Form Loading Speed Issues
In all versions of Access the issue of form loading speed is critical to the sense of performance of the application. Here are some general rules for maximizing form loading speed, and minimizing load time:
1. Minimize the number of controls and fields on your form. This may seem self evident, but it's important. Use separate popup forms for various sections of data or for fields only used by certain individuals. If you must use a form with many fields, then create a front end record selector form to pick the record you want to view or edit and then open the form using a filter or Where clause to pick the proper record.
2. Minimize the number of combo boxes and list boxes on the form, especially if they are based on another table; each combo or listbox will require Access to load one or more table pointers into memory for each table in the underlying queries. Convert combos and list boxes where possible to being based on value lists rather than queries. (See also the combo box topic on this page.)
3. Don't display OLE fields or Memo fields when the form loads. Either place these fields on another page, or cover them with a box control, and in the OnClick event of the box control, set the box's visible property to false to display the memo or OLE field. Non-visible OLE and Memo fields aren't read from the db until they are made visible.
4. Minimize the number of indexes in the underlying table, and be smart about indexes: Don't index both the Customer name and its ID number, since one is directly related to the other.
5. If you split your database between data on the server and application on the local workstation and use look up tables for combo boxes for items such as state abbreviations, keep those tables (where the data doesn't change) in the local application database, rather then on the server. This will lower network traffic and look up will be faster from the local disk.
6. For lookup forms such as Address books and forms used by individuals who only inquire about data rather than enter it, set the form to be read only, this is often overlooked and is a real boost, since it eliminates record lock requirements. (See the help file for the various versions of Access as to how to do this.)
7. Move code behind the form to general database modules, and combine where feasible this codes into one module so that all the code is loaded at the same time and ready for use. Better yet include in this code in a module has other code which is executed when the application starts (such as reattaching tables,) so that the form code is loaded when the db loads, rather than when the form loads. (If you make use of the Me object in your form module, when you move the code to a general module simply place a parameter in the Sub or Function call specifying a form object (e.g. Sub frmOrders_Order_AfterUpdate(Frm as Form), and replace Me in your code with "Frm". Call the function from the orders AfterUpdate event by specifying "Call frmOrders_Order_AfterUpdate(Me)").
8. If you use a query for the form record source or for combo or list boxes, used saved queries rather than SQL statements for the record source. Saved Access queries are "pre-optimized" by the JET query optimizer, whereas SQL statements must be optimized at run time.
How to Have Just One Delete, Save, or Record Movement Button Control Both Your Main Form and Your SubForm(s)
When you place a sub form on a main form which contains command buttons for deleting a record or moving between records, normally each time you click the command button for the desired action the action is only carried out on the main form itself, even if you are editing a record in the sub form, because the sub form looses the focus, and the main form regains the focus when you click on the command button. But you can create a "smart button" which will take the desired action on the sub form when you are in the sub form by following a few simple steps.
1. Place your subform on a separate page, or use the new tab control in Access 97 and place your subform on a different tab. (You can use the Tab ActiveX controls in Access 2 and Access 95.)
2. In the OnClick event procedure for the command button check which tab you're on, or which page of the form you are on and if its one which contains the subform, set the focus to the subform before carrying out the desired action. Here's same code for a two-tab form with a subform on tab 2 for a delete record button using Access 97 syntax: Dim tabObj as Control
Dim pge as integer
Set tabObj = Me!TabControlName
pge = tabObj.value
Select Case pge
Case 1 'Tab 2
This tactic event works with nested sub forms..... Keep in mind, that for Save Record Buttons, you don't have to do anything to check if you are on the subform because when the subform looses the focus by moving to click the save button on the main form, the subform's record is automatically saved.
Creating Your Own Automatic Spin Control Using Visual Basic.
Spin Controls on forms are useful to allow a user to increment up or down values in a field (e.g. number of products ordered etc.) Active X controls are available to provide this functionality, but rather than including another control and file in your distribution set for the application, its really rather simple to create a spin control in Visual Basic
How to use a variable to reference a table/query field or form control rather than an explicit reference.
When working with arrays of fields or controls your application may need to reference a query field, table field or form controls using a variable, rather than an explicit reference to the particular field name. Access provides a documented feature to allow you to do this, but it is often overlooked, resulting in long VBA code.
For example, you may have a form which contains controls named "RATE1", "RATE2", "RATE3", etc. which you need to fill from a recordset. If your VBA code needs to reference all three controls to set a value the standard way to do this would be to code: "Me!Rate1 = MyRecordset!Rate" etc for all the fields, which can amount to a whole lot of wasted coding.
Using the parentheses and quotation marks (" ") instead of using the ! operator allows you to use variables in a reference to a control or field. Rather than using the explicit reference to the control, change your syntax of the code to something like the following:
For i = 1 to 3
Me("Rate" & i) = MyRecordset!Rate
You can do the same thing with referencing fields in table or queries if you are working from any array of values.
Adding Zeroes to the Start of a Numeric Field
If you need a numeric field with leading zeroes, do this. (I assume integer or long integer).Change the field to a text field, the correct size. Access will convert the data from numbers to text fine.
Create an update query with the field in it. In the Update to cell, put a formula like this:
right(("0000" & [original_field_name]),4)
This assumes you want a field 4 characters in size. Modify to suit.
How do I put pictures in a database? how do I make these images show up on the form?
To put pictures in a table you add a field of types "OLE Object" - you can then paste images into individual records. You will need to view the table in design view to add a field.
To show images up on the form open the form in design view, find the "Field List" and drag your new field onto the form.
How do I loop through a table when programming in Access 97 and 2000?()
To get to the tables you need to obtain a "recordset" for the table. A recordset is a cursor - it points to a particular row, you can read and write individual fields and move the cursor through the table. The following will set the first name of all students to "Bruce". student is a table, FirstName is a field of t he table.
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("student")
While Not rs.EOF
rs.Fields("FirstName") = "Bruce"
How do publish my Access data on the web?
You use ASP. You will need an account on a machine which runs an IIS or PWS. If you have Windows 98 you already have PWS on your CD - this can be useful for development work.
Formatting Dates on Reports
Here is my favourite way of formatting a date on a report.
Create the text box and in its format property put:
mmm d, yyyy gives a date like this: Jan 24, 2000
mmmm d, yyyy gives a date like this: January 24, 2000
What about a common header like this:
"For the period of January 1, 2000 to January 14, 2000"
Create a text box and for its control source, put something like this:
="For the period of " & [Forms]![frmReport]![txtStartDate] &
" to " & [Forms]![frmReport]![txtEndDate]
Make sure the text boxes on the form are formatted to "mmmm d, yyyy" for this example.
How to Get Data from another Access Data Base
If you need to read and/or update data in a second Access database without living the first one, just link to it.
In the File menu there is a Get External Data command. Under this is a Link command. Once you have linked to a table in another mdb you can treat it as if it is in your current mdb. Add to it, delete from it, and change data in it. You just cannot change its design.
One word of caution. If the other database gets moved, the link is lost. If this happens, you can delete the link and re-build it. If you have several links from the same mdb, use the Linked Table Manager under the Tools / Add-In menu to change them all at once. If you did a default install of Access you will not have this add-in and will have to re-install.
My Order Numbers Don't Sort Properly
I dealt with a client who had started an Access database and then got overwhelmed. They asked me to come in and finish it up.
One problem they had run into was a crazy sort pattern on their purchase order numbers. Each department had an alpha character followed by a sequential number. So, for example, the warehouse POs were entered like this:
W1, W2, W3, W4, . . . W10, W11, . . .
but when listed on a report they went like this:
W1, W10, W11, W2, . . .
Access is not smart enough to handle this pattern. Because the 'W' is an alpha character the whole thing is treat that way and alpha sorts are left to right. The '1' in 'W10' comes before the '2' in 'W2'.
To fix this, I made the pattern like this, 'Wnnnn', where the 'n' is a digit. This allows for 9,999 purchase orders, plenty for this company. However, how can we get Access to automatically generate the next number? I can use ("W" & (right([ponum],4)+1)) to get the next number, but if I use that technique on 'W0015' I will get 'W16'. To fix it, use this technique."W" & right("0000" & (right([ponum],4)+1),4)
Proper Case - Capitalizing Just the First Character
You just imported a bunch of data from the client and notice that it is all in upper case. You want mixed case so that "RICHARD KILLEY" becomes "Richard Killey".
Create an update query and put the field(s) in the grid. In the update cells put commands like this:StrConv([firstname],3)
In VBA code you can use the vbProperCase constant, but I could not get it to work in the query, so I substituted the numeric equivalent of 3.
Other constants are vbUpperCase (1) and vbLowerCase (2).
Put Name of Database on Report
An Access developer has a number of versions of a particular database. As tests are done and reports printed, they need the report to say which version of the database it came from.
Step 1 Create an unbounded text box on the report, most likely in the page footer or report footer.
Step 2 Make the text box's control source equal to "= currentdb.name".
Referring to Columns in a Combo Box
First, some background. What follows is a standard use of a combo box on an Order Form .
|Table A - Customers
- customer ID
- customer name
- customer address
- etc, etc
|Table B - Orders
- order ID
- customer ID
- order date
- etc, etc
There is, of course, a one-to-many relationship from the Customers table to the Orders table.
To allow our staff to choose the correct customer for the order, on the Orders form we put a combo box bound to the customer ID in the Orders table. The row source for the combo box, though, comes from the Customers table. Here are some typical properties for the combo box.
|Row Source||SELECT [Customers].CustomerID, [Customers].CustomerName FROM [Customers] ORDER BY [Customers].CustomerID;|
Remember, the combo box is bound to the CustomerID in the Orders table. That is a big key to having this work.
Now, when the person doing the data entry clicks the drop down of the combo box, they will see the customer names, but not their codes. If you want to see the codes, change the column widths to 0.5"; 2" or similar. Also, a benefit of combo boxes is that if you know the customer's correct name, just start typing and it will auto expand to show the whole name. This is a keyboard alternative to using the drop down.
Back to the original topic of this tip.You know that you can use the value in this combo box as a parameter in a query. Since the query normally uses the CustomerID to do the criteria, we use [Forms]![frmOrders]![cboCustomer] to get the data for the chosen customer. The value passed back is the Bound Column, listed as 1 (first column) in the properties. This is the CustomerID.
What if you want to refer to the customer's name? Then, use this style.
I thought we determined that the first column is the CustomerID! I want the name this time.
Well, that's correct. When you use .column(n), Access starts with (0). So the CustomerID is the bound column, which in the properties box is "1". But Access considers that .column(0), and therefore, the CustomerName is the 2nd column, or, .column(1).
I know, clear as mud.
This is a typical use of a combo box. You will have many of these basic one-to-many relationships in your use of Access, and each one lends itself nicely to this method.
Searching for a String that Includes a Quote
If you have data that includes single quotes, here is the way to get around problems.For example, I need to search for an entry with a value of "Richard's". Just include double double quotes. dcount("[first]","propercase","[first]=""Richard's""")
Using the Format Function
The format function is a very useful addition to your Access knowledge. See the help system for more details, but here are some ways that I use it.
format(date(),"mmmm d, yyyy") gives a string like "January 9, 2000"
format(now(),"mmm d, yyyy h:mm AMPM") gives a string like "Jan 9, 2000 2:15 PM"
Sending Data to Excel
A recent project for a client required a variety of "reports" that could be easily sent to a wide variety of people. Since most of the employees did not have MS Access on their computers, we had to find an easy way of getting the data to them.
A report viewer is available, however, this option was quickly dismissed. Since almost every employee has MS Excel on their computers, I suggested that we get the Access program to create output in Excel format. The Excel workbook could then be e-mailed to the appropriate people and they could view and/or pri nt the "reports".
The following is some sample code to open and populate an Excel workbook. As usual, I am not suggesting that I have discovered the best code, but it works. Let me know if you have found a "slicker" way to do this.
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim rst As Recordset
Dim iRow As Integer
'--- open the workbook
Set objXL = New Excel.Application
objXL.Visible = True
Set objWkb = objXL.Workbooks.Open("c:\excel_templates\template_A.xls")
'--- I always name my worksheets something specific, and this next line refers
'--- to a specific worksheet (Excel names the first one "sheet 1" by default)
Set objSht = objWkb.Worksheets("output")
Note: I use a preset workbook so that headers and footers are already configured to the project's standards, and in some cases, the headers for columns are already configured, along with column widths, etc. You could just as easily use a template that contains a plain, default worksheet.
' --- since this particular worksheet has various built in headers and column titles,
' --- the actual data starts at row 10
iRow = 10
Set rst = CurrentDb.OpenRecordset("SELECT * FROM myTable " & _
"WHERE ((somefield)= 'somevalue') " & _
"order by field1, field2;")
Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!field1
objSht.Cells(iRow, 2).Value = rst!field2
objSht.Cells(iRow, 3).Value = rst!field3
iRow = iRow + 1
When the program reaches the end of the above code, you will be looking at the finished spreadsheet on your monitor. You can now make any minor modifications to it before printing it, saving it to disk, and then e-mailing it as an attachment.
NOTE: This code will only work if you have Excel references turned on. The References section is found under the Tools menu in the VBA editor.
p.s. The above code was based on VBA from an Access 2000 project that uses DAO references. The code was modified for use in this article and was not tested in Access. For example, I removed all error checking portions of the code. This code should also work in Access 97.