Microsoft Access Database: Delete Record or Not to Delete Record, That's the Microsoft Access Question

Should you delete records in Microsoft Access?

When using an Access database table which is very similar to the look of an Excel spreadsheet, you can edit and carryout basic formatting in this view including the ability to delete whole records.

Working in this view is therefore potentially dangerous and is not normally the correct view to be working in the first place; there are Continue reading “Microsoft Access Database: Delete Record or Not to Delete Record, That's the Microsoft Access Question”

Microsoft Access Database VBA Programming – 3 Ways How You Can Automate Your Access Application

Microsoft Access database icon - blue
Microsoft Access VBA

I haven’t really talked about Microsoft Access database VBA (Visual Basic for Applications) before as this is not for beginners!

However, I do get asked about how to program with Microsoft Access using VBA and in some cases macros too. I intend to do two things in the near future; write an eBook for beginners and create a dedicated section for Access VBA – keep an eye out!

 

In the meantime, the following article will give you some ideas on how and when to use VBA Continue reading “Microsoft Access Database VBA Programming – 3 Ways How You Can Automate Your Access Application”

Access Database 2007: Assigning a Primary Key to a Table Microsoft Lesson 2.7

Microsoft Access Databases must really have primary keys applied to each core table to help manage relationships and keep data integrity 100% enforced to avoid those unexpected errors.

Take a look at this quick tutorial on How To Set the Primary in Microsoft Access

httpv://www.youtube.com/watch?v=h3sYgmcRamQ&feature=related

My Comments on Microsoft Access Database Primary Keys

I’m not a fan of mutiple fields to act as a group for the primary key as discussed in this video tutorial. I believe there should only be one field which is unique (primary) and have other fields set as secondary keys (foreign keys) in your Access database.

I use the method as shown and then check the property ‘Indexed‘ in the lower half view (Properties) for the selected field to check whether it’s set correctly i.e. ‘Duplicates OK’ or ‘No Duplicates’.

I have eBook tutorials and detailed explanation and one in particular is recommended should you need to know How to Relate and Understand Relationships with Microsoft Access Datbases. Great value and comes with a money back guarantee too!

Microsoft Access Database: Use the Attachment Data Type in Access 2007

New to Microsoft Access 2007 & 2010 is a data type known as ‘Attachment‘ which allows users to add multiple files to a record and is better than using the earlier data type option of ‘OLE Object’ which was limited to just the one file pointer.

Take a look at this video tutorial which is using Microsoft Access 2007

httpv://www.youtube.com/watch?v=INeXrbXXyWw&feature=related

Microsoft Access Database Tutorial – My points

Try and keep the file size as small as possible to help with the database file size as it will start to slow down your Access database application due to the lack of indexing.

It’s a very neat new data type but the more advanced developer tends to use VBA code and other related tables to handle external files taking control of indexing and thus performance.

Please feel free to add comments and tell me how you use this new data type within your Microsoft Access database system.

Microsoft Access Database: Building Access Reports with Sub-Reports

Microsoft Access Database: Building Access Reports with Sub-Reports

In a nutshell, a sub-report is a report embedded into another report and normally has a relationship to the main (parent) report.

In essence, you can have more than one sub-report with the Microsoft Access database object linking to the parent report or even as a nested sub-sub-report!

Sub-reports however do not have to be related Continue reading “Microsoft Access Database: Building Access Reports with Sub-Reports”

Microsoft Access Database: Archive System in Access using Queries

There are four ACTION Queries that can be used in your Microsoft Access database; Make-Table, Append, Update and Delete.

    This video tutorial covers two of them; Append and Delete wrapped with a macro to similuate how to delete a record but first archive the record elsewhere.

    Microsfoft Access Video Tutorial: Archive System in Access using Queries

    httpv://www.youtube.com/watch?v=mlzKsRWiCJ4&feature=related

    Access Database Tutorial – My Additional Points

    As the author of this video correctly points out, it’s not a perfect way to archive record as there is room for mis-use and errors and coupled in a macro only highlights the inperfections.

    This example also uses a Parameter query which is a very popular query in Microsoft Access and makes this more flexible but again the discipline is with the user making sure they enter a valid and correct ID or code value as it has no error checking functionality.

    The ultimate solution in Microsoft Access?

    Learn to use VBA code – a programming language which is not difficult but requires a little effort. Here, you can control logical workflows and handle unknown records and errors.

    What else do you need to know in your Access Database?

    How to build queries whether it’s a collection of ACTION queries or other powerful and essential queries that is required for your Microsoft Access database application.

    My two eBooks; “How to Access Database Queries” and “More Access Database queries” covers all the queries you will need to know. The added benefit with my eBooks (which there are more!) is that they come with a 30 day free email support to help you as you learn Microsoft Access plus, there’s a money back guarantee too!

    Microsoft Access Database: Create a macro that runs when you open an Access database

    Microsoft Access Database – AutoExec Macro

    Using the AutoExec Macro in Microsoft Access is one of the special reserved macro keyword names that in this case will trigger when you load your database.

    It is called before any other macro or VBA code procedure and is used to load forms, resize windows and check settings in your application.

    This feature goes way back Continue reading “Microsoft Access Database: Create a macro that runs when you open an Access database”

    Spotting Trends Quickly with Conditional Formatting in Microsoft Access

    Conditional formatting was introduced from Microsoft Access 2000 and has been improved for 2010.

    Users you will be more familiar with using this useful tool in Excel and the same logic and workflow applies in your database too.

    The following video tutorial is a quick overview that will give you the confidence to explore this simple but effective utility in Microsoft Access 2010

    Spotting trends quickly with conditional formatting in Microsoft Access

    httpv://www.youtube.com/watch?v=jtuCajQG8cc

    Additional points to consider with your Microsoft Access database conditional formatting tool

    This can be applied to both forms and reports for each control or a selection of controls and can be logically chosen to the fields value or an expression which is a logical formula yielding true or false.

    Earlier versions of Microsoft Access had an inferior tool with up to 3 conditions allowed per control but it still also had the added benefit of setting the ‘has focus’ option which changed its colour when the insertion point was placed in the field’s control.

    There are other ways to utilise changing colours especially for numbers in your Microsoft  Access database application but you will need to investigate this further.

    Microsoft Access Database: Ways to share an Access Database

    With a stand-alone Microsoft Access Database design approach, you have complete control over managing the data and their objects but if you are going to want to share your Access database; simply loading your .MDB or .ACCDB file on a shared network folder will cause problems further down the line!

    So sharing an Access database is a methodology often over looked and needs to be planned at the earliest opportunity leveraging your time Continue reading “Microsoft Access Database: Ways to share an Access Database”