10 Best Microsoft Access Hacks and Tips

more images

We had already discussed the best features in Microsoft Access 2010. Now we explore the functions of Microsoft Access 2010. As you know Microsoft Access 2010 lays emphasis on web services and with SharePoint 2010, users will have much more to learn and do than the previous versions. Now the end users can work in Access program with data on a Sharepoint server using the forms and reports and VBA code. Being able to use Sharepoint, allows the database data to be accessed by a wide network. The Access web forms are an important inclusion which allows users to learn about the data macros. To make you more comfortable with Microsoft Access 2010 we sorted out 10 best hacks and tips - go on.

1. How to create an autonumber field that begins with a number greater than 1

In the Microsoft Access 2010 tables the AutoNumber field type allows you to assign a unique sequential number for each row in the table. The AutoNumber fields make the Primary Key and they are unique and permanent.

In the new tables of Access 2010 AutoNumbering begins with 1 and the increment is 1 with each new row. However, you might need to start the AutoNumber at a number higher than 1.

You can do this with a simple hack. Just create an Append query to insert the value of the ID one less than the desired starting number (or Seed value). Let’s consider that you wanna start with a Seed value 1000.

Just run this query

INSERT INTO tblName ( ID )
SELECT 999 AS Expr1

Where tb1Name is the table name and ID is the name of the AutoNumber field

Run a second query to delete the row that your inserted

DELETE
FROM tblName
WHERE ID=999;

Note: There’s also an alternative, simply open the table and manually delete the row that you have inserted

Now add the next record value to the table and assign it the value 1000

This method works with a new table, or with a table whose AutoNumber has not yet reached the Seed value. The table ID field for existing rows will not be changed, but the subsequent rows will with 1000.

2. How to change the default behavior of controls

While designing a report or form and placing a Text Box, you have a Label control attached to the Text Box. At times you might not find what you require so you have to delete the label. There may be many Text Boxes that do not need the labels. It might require a lot of clicking and deleting.

In order to avoid this you must change the default behavior of the control. Just follow the steps

Step 1: Open the Form or Report Designer.

Step 2: Select the control on the Ribbon Control tab. In the Tools tab open a Property Sheet.

Step 3: Change the Auto Label property from Yes to No and close the Property Sheet.

You are done, now you can place a Text Box on the form or report, no label will be attached.

Note: Do not forget to record default settings before making any changes, in case you might need to roll back to the default behavior.

3. How to set a ReturnVar in a Data Macro

If you are planning to use the new Access Web Forms, you need to learn the new macros that will be needed.

The ReturnVars in Data Macro are similar to values returned by functional/method calls in VBA or other programming languages. Typically they fill the gaps by domain functions as a way to get data into a UI layer.

Still what you have to learn is how to set a ReturnVar in a DataMacro and retrieve the value via a UI Macro.

Use this simple code to set a ReturnVar

This is how you would reference it in a UI macro (you need to call the data macro before getting the return value)

4. How to create a switchboard form in Access 2010

Creating a Switchboard form in Access 2010 is different from the earlier version. You can create the Switchboard form even without using the Switchboard Manager. You can create the menu structure with the help of Navigational Control as well.

In Access 2010 you can launch the Switchboard Manager in different ways

Add command to your Quick Access Toolbar

Step 1: Click the arrow on the QAT and click More Commands to open the Customize area
Step 2: Select Commands Not
Step 3: On the Ribbons and find the Switchboard Manager option. Add the command to your QAT and then launch it

Add the command to your Ribbon

Step 1: Right-click anywhere on the Ribbon and click Customize the Ribbon to open the Customize area
Step 2: Select Commands Not On Ribbons and find the Switchboard Manager option.
Step 3: Add that command to one of the existing Ribbon groups or create your own Ribbon group.
Step 4: Now launch it from there

Note: In case you want it in the immediate windows

Press CTRL+G to open the Immediate Window. Now type the following command and then press Enter:
DoCmd.RunCommand acCmdSwitchboardManager

5. How to import Worksheet from Excel 2010 to Access 2010

Step 1:Run the Access 2010, create database. Navigate to External Data tab and click Excel button.

Step 2: In the Get External Data dialog box select the how and where to store database. Create a database in which table will be created by Access automatically.

Step 3: Click Browse to select Excel worksheet you want to import and click Open. This will open up an Import Worksheet Wizards, which you can select as desired worksheet from the list you want to import and click Next.

Step 4: Enable the First row Contains Column Headings checkbox

Note: Your worksheet must have column’s label in first row

Step 5: Specify the information regarding each of the fields you are importing. Select field/column in the area below and specify corresponding Field Options. Select ID field and select Data Type as Integer (number only), and from Indexed drop-down menu chose No. However, you can also choose Yes (Indexed), or Yes (Not Indexed) from the list, depending on your situation. Click Next.
Step 6: Choose another field/column to specify Field Options
Step 7: Enable the Let Access add primary key, or choose your own primary key by selecting column/field label from drop-down box. Now select the default option, i.e, Let Access add the primary key. Click Next.
Step 8: Enable Save import to save the import steps for later use. Now click the Close

You are done the excel worksheet has been imported in Access 2010

6. Deleting records using the delete query

Delete queries are a fundamental part of relational database with which you can remove multiple records at a time. Users can specify the the records to delete by linking to data in another table. With delete queries you can

• Empty a table (delete all its records)
• Delete all records with a particular value in a field
• Delete all records with a range of values in a field
• Delete records based on criteria in multiple fields
• Delete records with matching values field(s) in a joined table

7.  How to password protect Access 2010 database

Security of the database is the prime concern for any user. Access 2010 allows users to encrypt the database with key to secure your database from the risk of prying from any unauthenticated source. Here is how you can password protect your Access 2010 file

Step 1: Go to the File menu and click Open
Step 2: Select the database file which you wanna password protect and Open options and click Open Exclusive
Step 3: Navigate to the File tab and in the backstage view click Encrypt With Password
Step 4: Set the password to encrypt database and click Ok
Step 5: After opening the database you will be asked to enter the passphrase for verification purpose
Step 6: Only the authenticated users will be able to use the database. You can open it again in exclusive mode. After opening it in the exclusive mode from the backstage view click Decrypt Database.
Step 7: Upon click, Unset Database Password dialog appears asking for password to decrypt database.

8. Use data macros to create aggregates

In Access aggregate queries are used to keep track of totals and summarize all the data in a table. With data macros you can have a new way to keep track of these types of totals traditionally done in aggregate queries or populated reports.

With the help of calc and store model, you can store the de-normalized total in a field on their table and update it with After Event data macros every time a related record is inserted, updated or deleted. Clearly based on the needs of the database, it is more efficient to calculate the totals when the data is entered using data macros rather than every time the data is queried.

We found an useful example in the msdn blog

9. Using default data context in named data macro

In data context stack enter a new data block, for instance (ForEachRecord, LookupRecord, EditRecord, and CreateRecord ). Say you are in data macro in Table 1

Suppose you create a Table1.AfterUpdate from which you call ForEachRecord Table2 and inside that loop you can call LookupRecord Table3. The data context at this innermost point contains a record for each of the 3 tables.

1: [Table3] contains the record (if any) found by LookupRecord

2: [Table2] contains the current records in the ForEachRecord loop

3: [Table1] contains the record that triggered the AfterUpdate event

When you call a name data macro from an event data macro, the entire data context is carried over. On calling RunDataMacro all the innermost data block in point 5, all 4 items would still be available inside the named data macro.

There is no data context when you call a named data macro from a user interface macro (UI macro). Any attempts to refer to a field fails unless you enter a data block. When you define parameters for data that is consumed by your named data macro, then you can also call the named data macro from a UI macro.  On viewing a form you can pass the ID of the record being viewed (or any other field in it) to the data macros and then use LookupRecord on the ID to get the same record. Then you can perform some processing, like deleting the record.  You must know that parameter values in data macros are read-only and cannot be edited.

10. How to make easy-to-use forms and maintain

To begin with you need to follow some design tips that satisfies every Access form

• Check that the name of a form is easily visible
• Name of a form should be expression of its functionality
• All mandatory fields should be visible on the first page of a form
• See that user easily identifies all mandatory (required) fields
• Design all the fields with a default value, auto number functionality, or read only property with a different background color. For instance you can use red background color, as specialBackgroundColor. Set the AutoTab property for the fields to no.
• Place all the non-mandatory fields after the first page break
• You need to know how to store color values in a table.

For comprehensive info look here