Designing A Multi-Table Query – Part 2

To view Part-1 of this series, (by Ben Beitler ), Click here >>

accessdatabasetutorial-2Queries can be hard to comprehend and fabricate if you don’t have a smart thought of what you’re trying to search and how to find it. A one-table MS access query can be simple enough to make up as you go along. However, to construct anything more powerful, you will need to plan the query initially.

Planning a MS access query

When planning a query that uses multiple tables, follow these steps:

  1. Define precisely what you wish to know. If you could ask your database any question, what would it be? Building a MS access query is more complex than just asking a question, but knowing exactly what question you wish to answer is vital to building a suitable query.
  2. Identify each type of information you want to include in your query output. Which fields contain this data?
  3. Locate the fields you want included in your MS access query. Which tables are they contained in?
  4. Determine the criteria the information in every field needs to meet. Consider the question you asked in the initial step. Which fields do you have to look for particular data? What information are you searching for? How will you search for it?

Continue reading “Designing A Multi-Table Query – Part 2”

Microsoft Access VBA vs MACRO

Microsoft Access VBA vs MACROA common question often asked: Would it be a good idea for me to use Microsoft access macros or would it be advisable to use Microsoft Access VBA code instead?

You should base your choice to use either Microsoft access macros or MS access VBA code with two initial concerns:

  • Security
  • Usefulness that you require.

Macros are considered to be easier to learn than VBA and in most cases it’s probably faster  to create a macro than it is to write VBA code. Continue reading “Microsoft Access VBA vs MACRO”

Access VBA Events: What Are Events And How Do We Use MS Access VBA Events

Access VBA Events: What Are Events And How Do We Use MS Access VBA Events

Microsoft Access VBA events have been around forever! Well, not forever but since VBA has been (circa 20+ years) and it’s still widely unexplored beyond the basic event triggers one gets to see and know about namely the clicking of a Command Button on a form or when the form itself opens.

Many users and developers have used events in many ways which is not just exclusive to MS Access (Excel is also a very popular application) and to get a feel for how one could use an event, check out this six minute video tutorial which runs through the After Update event to calculate a total from two other field controls in a form…

The video demonstrates how you can create VBA code and attach it to predefined event signatures that each control and object hosts. One could argue however why Continue reading “Access VBA Events: What Are Events And How Do We Use MS Access VBA Events”

MS Access Databases Properties: How To Set It Using Access VBA And The DAO Object

MS Access Databases – Properties: How To Set It Using Access VBA And The DAO Object

As with most Microsoft Office applications, MS Access databases also have a properties feature that captures the basic file attributes like author, date and subject to name a few. There is also a custom tab to edit and add your own properties and is stored with the database file itself.

To know more about changing the built-in properties window, see view and change MS Office 2010 file properties. For the earlier versions (pre 2007), this feature can be found via the File menu.

ms access databases properties

In this article, I want to introduce some Access VBA code to manage and control custom properties instead which is Continue reading “MS Access Databases Properties: How To Set It Using Access VBA And The DAO Object”

Access DAO or ADO: 3 Key Points Microsoft Access VBA Recordsets

Access DAO or ADO: 3 Key Points Microsoft Access VBA Recordsets

access daoI wrote an EzineArticle this month about Access DAO (and ADO) using Microsoft Access VBA focusing on the Recordset object and just in case you missed it, here’s the first part below:

Working With Access VBA Recordsets Using DAO or ADO: 3 Key Points With VBA Recordsets

Here are 3 key points to note and be mindful of when coding with Access VBA using either DAO or ADO to connect with other databases.

There are common traps and useful tips to look out for when using Access VBA code working with Continue reading “Access DAO or ADO: 3 Key Points Microsoft Access VBA Recordsets”

Microsoft Access To Excel VBA – Automating Between MS Office Applications

Microsoft Access To Excel VBA – Automating Between MS Office Applications

The power of Microsoft Access to Excel VBA is revealed here in this quick two and half minute video demonstration of how popular Microsoft Office applications are & can communicate when using Access VBA (Visual Basic for Application code)

Microsoft Access To Excel VBA – Automating Between MS Office Applications

Now you’ve seen it done – how does it work? Using Access to Excel VBA requires some foundation VBA code knowledge and may say they are not programmers – well that’s good news!

access to excel vba

Being a programmer can hinder your approach and taking simple principles that I teach you will give you the tools and knowledge to build and automate not only Access databases but send data to and from other Office applications (i.e. MS Excel).

To start with, review some of the basics and then talk to me and see if we can work on structured coaching programme for you and focus on the real specifics that you only want to know. Give a me a call.

7 Reasons Why You Would Use Access Database VBA In Your Application – Part 2

7 Reasons Why You Would Use Access Database VBA In Your Application – Part 2

So here are the remaining 3 (of 7) reasons why you should use Access database VBA in your application.

  1. Manage more powerful procedures that Microsoft Access macros lack or is limited by creating references and links with other proccesses including custom built functions.
  2. Allows you to communicate with other applications and across platforms increasing the seamless power of Access VBA (Visual Basic for Applications).
  3. You can gracefully handle errors (if or when they happen) and will happen from time to time allowing you flow and manage unexpected exceptions (errors) allowing code to continue and flow.

To view the first 4 reasons, take a look at my previous part 1 post.

7 Reasons Why You Would Use Access Database VBA In Your Application – Part 2 of 2

You do not need to be a programmer to learn Access database VBA (but it helps if you have some knowledge).

The more important factor in order to successfully program with Access is to be knowledgeable within the framework of Microsoft Access generally (namely tables, queries, forms, reports and macros) and know its powerful features to identify where VBA should take over and improve the functionality.

I invite you to keep up to date with my articles and eBooks which cover a lot of details and you can find out the basics of Microsoft Access Database VBA Programming here.

7 Reasons Why You Would Use Access Database VBA In Your Application – Part 1 of 2

7 Reasons Why You Would Use Access Database VBA In Your Application – Part 1 of 2

Some of my subscribers send me emails regarding learning about Access database VBA and when they should use it. So I’ve written the first part (of two) given you 7 reasons why.

access database vba


First thing to note and that is it’s not an exclusive component to Microsoft Access and that is also is available and compatible with other Microsoft Office applications including Microsoft Excel, Microsoft Word and Microsoft Outlook.


Access VBA has the power to communicate with other applications beyond the Microsoft Office product range too and can Continue reading “7 Reasons Why You Would Use Access Database VBA In Your Application – Part 1 of 2”

Microsoft Access Database: Access VBA Database Refresh or Requery?

Microsoft Access Database: Access VBA Database Refresh or Requery?

access vba database

 

One of the quickest ways when using an Access VBA database and is also a shortcut to relisting and rebuilding data lists and control values is to call the Refresh or Requery command using the Me object which is very handy indeed.

Take a look at the following article…

Microsoft Access – Refresh Or Requery?

When using Microsoft Access, many novice developers have difficulty understanding the difference between Me.Refresh and Me.Requery. So, let’s Continue reading “Microsoft Access Database: Access VBA Database Refresh or Requery?”

Microsoft Access Database: Access DLookup Function Example with Access VBA

Microsoft Access Database: Access DLookup Function Example with Access VBA

Take a look at this six and half minute video of how to utilise the Access DLookup function using a little of Access VBA code too (not too difficult but necessary in this example).

Microsoft Access Database: Access DLookup Function Example with Access VBA

Of course you do not have to use Access VBA code when applying the Access DLookup function. Instead, you can populate fields by using a calculation or auto-lookup query instead.

Access DLookupWith the calculation method however, this is just an expression and will not be updatable should data in fields need to be edited.

Take a look at my eBook on More Access Database Queries to learn about the ‘Auto Lookup’ query and other more adavnced techniques.