Advanced Microsoft Access Tips

To do this, start off by creating a new table with an ‘AutoNumber’ field but do not enter any records in it. We need one more table having a single field and with the same name as ‘AutoNumber’ field; make it of ‘Long Integer’ type.

Now, just enter one record in the second table. It will be the required start number – 1 for the ‘AutoNumber’ field in the first table.

The final thing we need is an ‘append query’. It is required for appending the record in the second table to the first table. Run the query when it is ready. The second table can be deleted and we can now start entering data into the first table.

There isn’t any method offered by MS Access that allows for deleting duplicate records in a table. A good ‘workaround’ is to create a new table that will hold the same records but not include duplicate entries.

To manually do this, follow this easy method:

We need to create a ‘make-table’ query that will include all the fields that are in the original table. Make sure that the ‘Unique Values’ property of this query is set to ‘Yes’.

When the query runs, a new table without duplicate records will be created. The records will be unique because we set the ‘Unique Values’ property to ‘Yes’.

Want to work on your .doc, .xls or .ppt document from Access? Do you need to embed other MS Office documents in Access? This method will allow you to work on Office documents from Access, it will not, however, match all the formatting capabilities.

First of all, create a new table having a field of ‘OLE Object’ type. Now, to insert a document, open the table then right-click on the field and chose ‘Insert Object’. This can be done either in Datasheet or Form View.

Select the ‘Create New’ option and then choose the item type you want to insert. If the file already exists, then choose ‘Create From File’ and browse for the file on your disk.

On the same window, you will see a ‘Link’ option. Check it if you want to create a link so that if you make any changes to the file in MS Word, the changes will also be made in Access.

Finally, press the OK button and you’re done. Any sort of changes you make to the object in Access will be the same as is done through the Word, Excel, or PowerPoint application.

Access doesn’t come with an option allowing us to create a blinking label. There is, however, a nice method to make your label blink. Let’s see how:

1. Create a 'textbox' on a form.

2. Double click the label for the text. In the property box, name it ‘blinkLabel’.

3. Open the property sheet of the form. Set the ‘Timer Interval’ value to 400.

4. Now, click on the ellipse (“…”) of the ‘On Timer’ event and then choose ‘Code Builder’.

5. Insert the following code :

With blinkLabel

.ForeColor = IIF(.ForeColor = 0, 255, 0))

End With

Open the form in ‘Form’ view. You will see the label is nicely blinking.

• If the table has a primary key, consider an index on a second field only when you are working with large amounts of data or if the field contains mostly unique values.

• Use the appropriate size for the field. For best results, the smallest data type and field size should be used that can accommodate the greatest value in the field.

• White spaces in field names are acceptable by Access. However, it is a good practice to avoid them. It is somewhat difficult to work with fields having spaces, especially in VBA code and SQL statements. Also, if wish to upgrade your database to SQL Server, those white spaces will cause problems.

• 

  click to enlarge
  Complicated table design can be confusing. Access has two tools that can help you fine-tune your design. 'Table Analyzer' reviews a table and recommends appropriate changes. Another good tool is 'Performance Analyzer' as it can be helpful in reviewing overall database design and it gives suggestions for improving it.

• Many people use indices for sorting purposes. It should be noted that an index is not created for this job. Using them to sort something might result in something that we don’t need. Therefore, doing this should be avoided.

Converting MS Access reports to PDF format

We often need to have an MS Access report produced in PDF format, but it is impossible with MS Access since it does not come with a conversion mechanism. We have a good solution to this though – we will make use of ‘Universal Document Converter ‘. These simple steps will show you how to print an MS Access report in PDF format.

1. Download and install ‘Universal Document Converter’ .

2. Open the MS Access report and choose ‘Print’ from ’File’ Menu.

3. You will see a new window - select ‘Universal Document Converter’ from the list of printers then click on ‘Properties’.

4. From the list shown, select ‘Document to PDF, Color, Multipage’

and press OK. Your report will be converted to PDF now and saved in C:\UDC Output Files folder.

Saving changes manually

MS Access saves any changes to a form or table when we move to another record or close the window. Wouldn’t it be easier if we could be able to save changes ourselves whenever we want? To save any changes we have made to the current record without moving to a different one, press SHIFT + ENTER together.

Navigating through a form

These tips are good for those who enter lot of data into an Access form.

To move from one field to the next one, press either ‘Enter’ or ‘Tab’. Remember, reverse of this move could be achieved by adding the ‘Shift’ key to the keys combination.

To go to the last field in the current record, press ‘End’. Likewise, to go the first one, press the ‘Home’ key.

To reach the last field in the last record, press ‘Ctrl+End’ and press ‘Ctrl+Home’ to go to the very first field in the first record

Microsoft Access is more than just a database application. It also allows us to create high-quality stand-alone applications. It comes with great features than enthusiastic programmers can use to create various types of applications using VBA.

Remember, there is more to come in the future on MS Access tips, so keep checking back on Bright Hub!