Editing Hyperlink Address Manually

Introduction.

Hyperlinks are everywhere on the Internet, and Microsoft Access supports them, too. In Access, hyperlinks can be used to open objects—such as Tables, Forms, Reports, or Queries—with a simple click, without writing a single line of VBA code or creating a Macro. They can also be created in Tables or Forms to build menus that open various database objects. Beyond Access, hyperlinks can be used to launch external files such as Word documents, Excel worksheets, PDFs, text files, and more.

In Access, hyperlinks can be created or edited manually by entering their different segments in the correct order, either in a table field or on form controls. More commonly, editing is done through the Edit Hyperlink dialog box, which appears when you right-click on a hyperlink control. 4 Segments of Hyperlink-Address in MS-Access.

The Hyperlink address line is divided into four segments:

1. Display Text: the text that appears in a field or control, indicating the hidden link's purpose
2. Address: external file's path-name
3. Sub-Address: Internal Object Name, to open Form, Report, etc.
4. 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.

1. Create a table with a single field and data type Hyperlink.
2. Save the table with a name.
3. 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

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

5. Move the mouse pointer over the field, and 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.

6. Close the Table for now.
   • Click on the 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.
7. 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 the 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.

• Control Screen Design
• Control Screen Menu Design
• Startup Screen Design
• Double Action Command Button

Read More

Alphabetized Customer List

Introduction.

If you are new to Microsoft Access report design, this simple tutorial on creating an alphabetized customer list will give you a clear understanding of the basics. It provides valuable insight into the steps involved in designing a report. We will need the following steps to complete our task:

1. Prepare the Customers' source data in a SELECT Query for the report.

2. Open a new report in Design View.

3. Insert the SELECT Query name into the Record Source Property of the Report.

4. Use the Data Grouping and Sorting option of the Report to organize and display A, B, C, etc., in the Group Header.

5. Design the Report.

6. Preview the Report.

A Sample Report.

Sample alphabetized list of customers. Report Preview is given below:

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

Designing A Report.

Get Some Sample Data.

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

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

1. Click on the External Data Menu.

2. Click on the Access Tool button to display the Import control dialog box, to specify the Source and destination of data.

   

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

3. 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 the default to import one or more required Access Objects from the selected Access database.

4. Click OK to open the selected Access Database and expose its Tables, Queries, Reports, etc.

5. 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 the Source.

6. Click on the Create menu and select Query Design from the Other group.

7. Click the Close button to close the Show Table dialog box, without selecting any object from the displayed list.

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

9. 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];
   

   In the SQL string shown above, we are working with only two columns of data. The first column, named Alpha, contains a single character from each row—the leftmost character of the customer’s first name—extracted using Microsoft Access’s built-in string function Left(). Access also provides other useful string functions in this category, such as Right(), Mid(), and more.

   The second column, named CName, contains the customer’s full name, created by joining the first and last names together with a space in between. When building query expressions like this, it is always a good practice to assign simple and meaningful names to the calculated columns. This makes it much easier to remember and reference them later in reports or forms. If you don’t provide explicit names, Access will automatically assign generic names such as Expr1, Expr2, and so on, which can be confusing when working with queries.

   In the ORDER BY clause of the query, both columns are sorted in ascending (A-Z) Order, first on the Alpha column, and then on the CName column.

10. Save the Query with the name Customer ListQ.

11. 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:

    

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

The Design Task.

Let us design the Report.

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

2. Select the Data Tab on the Property Sheet.

3. Click on the Record Source property and click on the drop-down list at the right end of the property.

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

5. Click on the Group & Sort Toolbar button from the 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.

6. Click on the Add a Group control displayed in the Group, Sort, and Total shown below the empty report.

7. Click on the Alpha column name displayed in the Query columns list.

   You can see that 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 as well to make them appear in proper order on the Report as well.

8. Click on Add a Sort control and select CName from the list.

   Now, let us create the Heading of the Report, Group headings (A, B, C, and so on), and a customer names list to appear under each group.

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

10. Select the TextBox control and draw a text box on the Alpha Header Section of the report.

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

12. Select the child label of the text box and delete it.

13. Create another text box in the Detail Section of the Report, below the Alpha Header control.

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

15. Reduce the Detail Section height by dragging the Page-Footer section bar up to touch the text box's edge.

16. Save the report with the name: Customer List.

    Print Preview the Report.

17. Open the Customer List report in Print Preview to view.

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

• Printing MS-Access Report from Excel
• Hiding Report Lines Conditionally-3
• Network and Report Page Setup
• Highlighting Reports

Read More

Archiving Master Data

Introduction.

Over time, the Master Table in a database can grow substantially, containing thousands—or even millions—of records. Since the maximum logical size of a Microsoft Access database is only 2 GB, routine repairing and Compacting may not be enough to maintain optimal performance. As the table grows, the processing time for queries and report generation will continue to increase.

Most of the older records in the master table are not required for daily or monthly reporting and querying. However, they may still be needed for year-end processes, historical analysis, or setting business targets for the upcoming year.

Typically, older records are marked with an archived flag and retained in the master table, while active records are filtered for routine reports and queries to monitor ongoing business activities. As the table size increases, this filtering and sorting process can become slower, impacting overall database performance.

Maintaining Data of the Earlier Period.

Removing old data from the main table and storing it in a separate database will significantly improve the performance of the active database. The archived data, stored in a database such as Archive.accdb, can still be accessed whenever needed for year-end processes or historical analysis.

There is no need to permanently link the archived tables to the front-end database. Instead, you can directly reference the archived tables in a UNION query, combining them with the active master table only when required. This combined dataset can then serve as the source for year-end processing. (More details on creating UNION queries can be found [here].)

Before we get to that, let us first see how to safely transfer old records from the master table (tblMaster) into the archive database (Archive.accdb). For this example, we will assume that the database is configured in a Front-End/Back-End setup.

The Prelude of our Action Plan.

We need the following steps to complete the process:

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

   Note: If you are working on a shared network server, create (or use an existing) folder where you have the necessary access rights and save the Archive.accdb there. Databases stored on the server are usually included in the administrators’ daily backup routine, ensuring that your archive remains safe and can be recovered if needed. For more details, see: [Database Daily Backup].

2. Close archive.accdb database.

3. Open the Back-End Database.

4. Transfer only the Structure of tblMaster into the Archive.accdb

5. Create a SELECT Query on tblMaster with an appropriate criterion to select the old data.

6. Open the Select Query in Datasheet View, take the total record count, and note it down.

7. Change the SELECT query into an Append Query.

8. Save and run the Append Query to transfer the selected data directly to the archive.accdb database into the table tblMaster.

9. Close the BE database and open the Archive database.

10. Open tblMaster in the archive database and check the count of records that matches the count taken earlier. If not, investigate the cause and redo from step 5 onwards, after deleting the wrong data in the tblMaster of the Archive.

11. Close the Archive and open the BE database.

12. Create a Delete Query that uses tblMaster with the same criteria you have used in the Append Query.

13. Open the Delete Query in Datasheet View and take the count of records, and ensure that it matches the count you have taken earlier.

14. Run the Delete Query to remove the records from the tblMaster table from the BE database.

15. Run the Repair and Compact option to reduce the database size.

16. Close the BE database and open the FE database.

    Linking Old Data to Front-End (FE) Database.

17. Create a Union Query to combine data from tblMaster in BE and from tblMaster in Archive.

Let us execute the above-defined Plan.

Steps 1 to 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 the Definition Only option to transfer the tblMaster Table Structure into the Archive.

Step-5: 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 an 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 the Append Query in Datasheet View and check the record count with the count you took 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 the tblMaster table and to append it to the archive.accdb tblMaster table.

• Click the 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.

Technorati Tags: Archive, Union Query, Databases, Backup

• Database Open/Close Event Alert
• Opening External Data Sources
• Opening Database Files directly
• Link external Tables with VBA
• CHDIR and IN Clause of Access Query

Read More