Despite temptations to use Excel instead and offerings from other database competitors, Access still remains widely used as, it does what it says on the tin – it enables the storage, retrieval and reporting on, data, simply.
Here are 5 essential tips for Microsoft Access that will make your experience nice and smooth.
1. Import from Excel to Access
Importing data from Excel is a useful function to be aware of since so much of data management is carried out using both Excel and Access. The format of Excel is recognised by Access 2010 whereby the organisation into rows and columns are easily mapped to records and fields. A good tip when doing this is to ensure that the Excel data is presented in the form of a list with column titles in the first row. They should be formatted into the same data type, ideally and, your Excel data shouldn’t contain any blank rows or columns.
After that, simply click the ‘External Data’ tab on the Access ribbon and select ‘Excel’. You will be guided through a wizard and as a useful time saver, you get the option to save your import steps for later use.
2. SQL for Queries
Using SQL for queries in Access 2010 is a useful way to have complete control and is a function that is apt for intermediate to advanced users. If you are familiar with using SQL for queries in previous versions of Access, be prepared for a slightly inefficient way to do this in the 2010 version.
The main problem is that the SQL view is not immedeately present and even when you enable it, the toolbar is not visible. The way to resolve this is to click the ‘Create’ tab on the Access ribbon and select ‘Query Design’. This automatically pops up a tables window which you can close upon which, you will see the SQL view available in the ribbon.
Finally you can simply insert your SQL instructions into Access and hit ‘Run’.
3. Change the default format
As a default, Access 2010 uses the ACCDB format which was first introduced by Access 2007. It is a format that supports certain functions that are not available in the previous MDB formats like, integration with SharePoint and Outlook, the ability to use attachments in the database and so on.
Despite its advantages, you may be keen on changing this format and in order to do this in Access 2010 you select ‘Options’ from the ‘File’ menu. From here you need to go to the ‘Creating databases’ section and refer to the ‘Default file format for Blank Database’ from which you select the version of Access that you want to save your databases in.
Note, that all databases will now be created in this format unless overridden.
4. Timestamp Records
In Access 2010 there is an easy way to time or date stamp the creation of a record, a useful tool for monitoring and regression checks. A good way to do this in Access 2010 is by using the Now( ) function for which you simply start by selecting the table that you want to insert this into. Ensuring your are in Design View, click in the Field Name column of the first row and type a name for that column (if it is not already present). In the ‘Text in the Data Type’ column of the row choose Date/Time from the drop down menu and type ‘Now()’ into the Default Value box of the Field Properties pane.
It is useful to bear in mind that the Now() function adds the current date and time but you can use the Date() function if you simply want to enter the date only.
5. Use Templates
Access 2010 has welcomed even the most novice of users with open arms through its provision of a variety of templates which are, already created databases complete with things like reports, that are ready for you to tailor. If you can’t find the sort of template that fits your requirements then you can search for any additional templates in the Backstage View.