June 2015 ~ LEARN MS-ACCESS TIPS AND TRICKS

Editing Hyperlink Address Manually

a.p.r. pillaiJune 25, 2015How Tos No comments

Introduction.

Hyperlinks are everywhere on the Internet. We have them in Microsoft Access too. It can be used to open Objects (Tables, Forms, Reports, or Queries) by clicking on a hyperlink, without writing a single line of VBA code or Macro. Hyperlinks can be created in Tables or Forms. You can create menus to open Forms, Reports, etc. You can open external files, like Word, Worksheets, .PDF, text, and others with the use of Hyperlinks in Microsoft Access.

Hyperlinks in Access can be created or edited manually by entering their link segments in proper order, in the table field, or on Form controls. Normally, editing can be done through a dialog box, displayed when right-clicking on the hyperlink control.

4 Segments of Hyperlink-Address in MS-Access.

The Hyperlink address line is divided into four segments:

Display Text: the text that appears in a field or control, indicating the hidden link's purpose
Address: external file's path-name
Sub-Address: Internal Object Name, to open Form, Report, etc.
Screen-tip: the text displays as a tooltip.

Hyperlink syntax is as follows:

Display Text#Address#Sub-Address#Tool-tip text

The Hyperlink Data type field can store a maximum of 2048 characters.

You can find more details and usage of Hyperlinks in the earlier Post: Open Forms with Hyperlinks in Listbox.

Editing Hyperlink Manually.

Let us get into the manual editing of the Hyperlink topic:

We will create a sample table with a Hyperlink field to try out the manual editing exercise.

Create a table with a single field and data type Hyperlink.
Save the table with a name.
Open the table in datasheet view.
If you know how to enter Hyperlink data into the field manually, you may do so, by following the hyperlink syntax shown below.
Example: My Excel File#C:\\\My Documents\Workbook1.xls##Click
After entering the sample hyperlink in the field, open the table in the datasheet view.
You will see that the hyperlink field is active and the only text showing is the Display Text (My Excel File) part of the hyperlink information.
Move the mouse pointer over the field, it changes the Mouse-Pointer to a hyperlink pointer (a Hand with an index finger).
If you click on the field to edit the hyperlink information, it will only open the document specified in the second segment.
If you right-click on the field the shortcut menu, which carries the hyperlink editing options in a dialog box is displayed. You may key-in values at appropriate controls to change the hyperlink values. You cannot type values directly into the hyperlink field.
Simple Trick to Edit Hyperlink Manually.
But, we can manually edit the field with a simple trick.
Close the Table for now.
- Click on Office Button.
- Select Access Options.
- Select Advanced.
- Find the Behavior Entering Field options.
- Select Go to end of the field option.
- Click OK to close the Access Options control.
Open the Table after completing the above steps.

You can see that the insertion point is positioned at the end of the field value, exposing the hyperlink information. Move the insertion point back to the location where you need to edit the field.

If you have more than one record to edit then change them and reset the Behavior Entering Field option to Select Entire Field.

TIP:

If you would like to show the full Pathname of the File as display text, then copy the second segment hyperlink value and paste it into the display text position also.

Stand-alone Label Controls Have Hyperlink Properties.

The Label control on Form can be used as a hyperlink control. The label control has Hyperlink Address & Hyperlink Sub-Address properties, use the Caption property to set the Display Text value of the hyperlink. Set the Special Effect property value to Raised, to make it look like a Command Button.

Alphabetized Customer List

a.p.r. pillaiJune 17, 2015Reports No comments

Introduction.

If you are new to Microsoft Access Report designing, then this simple Alphabetized Customer List preparation Tutorial will give you enough insight into what it takes to design a simple report.

We will need the following few steps to complete our task:

Prepare Customers' source data in a SELECT Query for the report.
Open a new report in Design View.
Insert the SELECT Query name into the Record Source Property of the Report.
Use the Data Grouping and Sorting option of the Report to organize and display A, B, C, etc., in the Group Header.
Design the Report.
Preview the Report.

A Sample Report.

Sample alphabetized list of customers Report Preview is given below:

Designing A Report.

Get Some Sample Data.

But, first, we need some ready-made sample data for our Report

Let us start by importing Customers Table from the Northwind sample database.

Click on External Data Menu.
Click on the Access Tool button to display the Import control dialog box, to specify the Source and destination of data.
Click on the Browse... Button to locate the Northwind sample database, Select the file and click Open.

The selected file pathname is inserted into the File Name control in the dialog box.
The first option is already selected as default, to import one or more required Access Objects from the selected Access database.
Click OK to open the selected Access Database and expose its Tables, Queries, Reports, etc.
Click on the Tables tab, select the Customers table, and click OK to import the selected table.

The next step is to create a SELECT Query using the Customers table as Source.
Click on the Create menu and select Query Design from the Other group.
Click the Close button to close the Show Table dialog box, without selecting any object from the displayed list.
You will find the SQL View option on the extreme left of the Toolbar and select it to display the Query's SQL editing window.

You will find the SQL statement SELECT; in the window.
Copy the SELECT Query Code given below and paste it into the SQL window, overwriting the existing SELECT; statement.
```
SELECT Left([First Name],1) AS Alpha, [First Name] & " " & [Last Name] AS CName
FROM Customers
ORDER BY Left([First Name],1), [First Name] & " " & [Last Name];
```
As you can see in the above SQL string we have only two columns of data. The first column has only one character in all rows, the leftmost character taken from the First Name of the customer. The first column name of the query is given as Alpha.
We have used MS-Access built-in string function Left() for this purpose. There are other useful functions in this category, like Right(), Mid(), and others.
The second column of data is the name of the Customer, with the First Name, and Last Name joined together, separated with a space. The second column's name is given as CName. When you use an expression to create the query column data it is better to give a simple name to the column so that it is easy to memorize and use on the Report or Form. If you don’t give a suitable name, then MS-Access will give names like Expr1, and Expr2 (Expression 1, Expression 2, and so on) by default.
In the ORDER BY clause of the query, both the columns are sorted in ascending (A-Z) Order, first on the Alpha column, and then on the CName column.
Save the Query with the name Customer ListQ.
Open the Customer ListQ in the datasheet view and check the data.

A sample image of what we are going to create is given below for reference:

The Design Task.

Let us design the Report.

Select Report Design from the Create menu.
An empty Report is open in Design View, with its Property Sheet. The first priority is to define our Customer ListQ Query as the Record Source of our report. If the property sheet is not displayed then click on the Property Sheet toolbar button to display it.
Select the Data Tab on the Property Sheet.
Click on the Record Source property and click on the drop-down list at the right end of the property.
Find Customer ListQ Query (use the slider, if necessary) and select it from the drop-down list, to insert it into the Record Source property.
Click on Group & Sort Toolbar button from Group & Totals Group under Design Menu, if it is not already in the selected state, to display the Group and Sort controls under the Footer of the Report.
Click on the Add a Group control displayed in the Group, Sort, and Total shown below the empty report.
Click on the Alpha column name displayed from the Query columns list.

You can see the Alpha Group Header is now appearing between the Page Header and Detail Sections of the empty report.
We must sort the customer names appearing under a particular character group (say A, B, C group) so that they will appear in proper order as well.
Note: We have sorted the data in the Query by defining the ORDER BY clause, but we must define Grouping and Sorting on the Report also to make them appear in proper order on the Report as well.
Click on Add a Sort control and select CName from the list.

Now, let us create the Heading of the Report, Group Heading (A, B, C, and so on), and customer names list to appear under each group.
Click on the Label control to select it and draw a rectangle, wide enough to write the heading text "CUSTOMER LIST", select Bold and Italic formatting styles, and change the font size to 16.
Select the TextBox control and draw a text box on the Alpha Header Section of the report.
Click on the Data Tab on the Property Sheet and select Alpha from the Control Source drop-down list. Change the font style to Bold and character size to 16.
Select the child label of the text box and delete it.
Create another text box in the Detail Section of the Report, below the Alpha Header control.
Select the CName column name from the drop-down list in the Control Source property under the Data Tab on the property sheet of the text control.
Reduce the Detail Section height by dragging the Page-Footer section bar up to touch the text box's edge.
Save the report with the name: Customer List.

Print Preview the Report.
Open the Customer List report in print preview and see how it looks.

If the Heading, Group heading, and customer list are not properly aligned to the left in your report, as shown in the first image on the top, try to align all the controls to the left.

Archiving Master Data

a.p.r. pillaiJune 03, 2015Accesstips No comments

Introduction.

Over a period of time, the Master Table of our database can grow into large volumes, having thousands or millions of records. The maximum logical size of a Ms-Access Database is only 2GB. Everyday Repairing and Compacting may not improve the performance of the database. The processing time of reports or running time of Queries will keep on increasing.

The majority of old records in the main table may not involve in daily or monthly querying or report preparation processes. But, old records may involve in year-end processes, like history analysis, next year's business target setting, and so on.

Normally, old records of the master tables are set with a flag indicating as archived and retained in the master table itself. Active records are filtered from the master table for reports or querying to monitor the current activities of business functions. The filtering and sorting process may take more time, every time, due to the large volume of data in the main table.

Maintaining Data of Earlier Period.

Removing the old data from the main table and keeping them in a separate database will improve the active database’s performance. The older data is in the archive.accdb (a name for reference) can be easily made available to the Front-End database for year-end processes.

You don't need to link and keep the archived table in the Front-End database permanently. You can combine the archived data with the active master table in a Union Query, by using direct references to the archived table, rather than keeping it linked to the FE database. The Union Query having combined data of both the tables can be used as a source for year-end processes. You can find more details on Union Queries here.

But first, let us see, how we can safely transfer the old data from the master table (tblMaster) to a new archive database: Archive. Here, we assume that we have a Front-End, Back-End configuration of databases.

The Prelude of our Action Plan.

We need the following steps to complete the process:

Create a new Access Database: Archive in location C:\mdbs or in a location of your preference.

Note: If you are connected to a common Network Server then create a folder there/use an existing folder, where you have access rights, and save the archive.accdb there. The database will be backed-up regularly, by the Network Administrators and will be safe for recovery if the need arises. Click on the link to learn more about Database Daily Backup.
Close archive.accdb database.
Open the Back-End Database.
Transfer only the Structure of tblMaster into the Achive.accdb
Create a SELECT Query on tblMaster with an appropriate criterion to select the old data.
Open the Select Query in Datasheet View, take the total record count, and note it down.
Change the SELECT query into an Append Query.
Save and run the Append Query to transfer the selected data directly to the archive.accdb database into table tblMaster.
Close BE database and open the Archive database.
Open tblMaster in the archive database and check the count of records that matches with the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in tblMaster of the Archive.
Close the Archive and open the BE database.
Create a Delete Query that uses tblMaster with the same criteria you have used in the Append Query.
Open the Delete Query in Datasheet View and take the count of records and ensure that it matches with the count you have taken earlier.
Run the Delete Query to remove the records from the tblMaster table from the BE database.
Run Repair and Compact option to reduce the database size.
Close BE database and open the FE database.

Linking Old Data to Front-End (FE) Database.
Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.

Let us Execute the above-defined Plan.

Step-1 to step-3 are self-explanatory.

In step 4: Right-click on tblMaster to display a shortcut menu.

Highlight the Export option and select Access Database from the displayed menu.
Click on the Browse button and select the archive.accdb database and click Save to come back to the Export dialog box.
Click OK to open up the Export Options dialog box.
Select Definition Only option to transfer the tblMaster Table Structure into Archive.

Step-5: The select Query Code:

SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<Dateserial(1981,1,1)));

The above criteria will select all the records of 1980 and the earlier period.

Step 6 is self-explanatory.

Step 7: Open the Query, created in step 5, in the design view.

Click on the Append Query button on the Toolbar.
Select tblMaster from the Table Name drop-down control in the dialog box.
Select Another Database Radio Button.
Click on Browse… Command Button to find the archive.accdb database, select it and click OK to come back to the dialog box in Query Design View.

Click OK on the dialog box to change the Select Query to Append Query. The Sample append query SQL is given below for reference.

INSERT INTO tblMaster IN 'C:\mdbs\archive.accdb'
SELECT tblMaster.*
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Open Append Query in Datasheet View and check the record count with the count you have taken earlier.
If both counts match, then save the Append Query.

Step-8: Right-Click on the Append Query and select Open to Run the Query to extract selected data from tblMaster table and to append them in the archive.accdb tblMaster table.

Click Yes Command Button, on the warning message control, to reconfirm the action.

Step-9 to Step-11: Self-explanatory.

Step-12: Sample Delete Query SQL is given below:

DELETE tblMaster.*, tblMaster.mstDate
FROM tblMaster
WHERE (((tblMaster.mstDate)<DateSerial(1981,1,1)));

Step-13 to Step-16: Self-explanatory.

Step-17: Sample Union Query SQL is given below:

SELECT tblMaster.* 
FROM tblMaster
UNION ALL SELECT tblMaster.*
FROM tblMaster in 'C:\mdbs\archive.accdb';

Save the Union Query with the name tblMasterQ. Use tblMasterQ as Source Data for all year-end processes or wherever you need all the data together. For other purposes, your database will run faster.

You can continue to transfer data when they become old, into the Archive and delete them from the BE database. No other change is required anywhere.