Microsoft Access Developer Challenges When Inheriting Databases

Microsoft Access Developer Challenges When Inheriting Databases

I recently visited a prospect client to assess and evaluate a mix of Access databases and Excel spreadsheet files which constituted in the company side operational database system only to discover as a Microsoft Access developer my worst fears of:

  1. More than one sensible database application was being used.
  2. There were different instances stored locally with very little workgroup control.
  3. Excel files were acting as a relational database files (very badly).
  4. No documentation to be seen at all.
  5. Even had two different versions of MS Access (2003 and 2010) running.

microsoft access developmentI didn’t even need to know much of the history, development cycle or background here as I already knew most of the answers including that fact over time there had been two or more developers (or more accurately; Access power-users) who had arrived and then disappeared leaving their foot-print in an organisation with no structure or workflows in place.

So where do you start?
The simple and quick answer would be to carry out an ‘audit’ of the current systems and gather as much information as possible to propose a plan going forward.

Microsoft Access Developer Challenges – Taking Over A Project

Before I even consider this project, the reasons why many organisations have a mixture or systems within the Microsoft realm is because most Continue reading “Microsoft Access Developer Challenges When Inheriting Databases”

How To Concatenate Access Fields In Queries

How To Concatenate Access Fields In Queries – Simple Really!

To join or not to join, that’s concatenation for you! How do you join or concatenate Access fields together for your queries or other objects (including forms and reports) is a matter of mastering the rules and knowing the operators.

There are plenty of simple demonstrations and video tutorials around on the web showing you how to combine and joins fields from tables in a calculation using the concatenate principle.

Most Access developers and users will use the & (ampersand) operator which will join the fields together – like the glue in between two fields. For example:

Fullname: [First name] & " " & [Surname]

However, have you seen this example?

Fullname: [First name] + " " + [Surname]

The above will provide the same results – or does it?

How To Concatenate Access Fields In Queries – Knowing The Difference

So the difference between using the & (ampersand) and the + (plus sign) will sometimes show Continue reading “How To Concatenate Access Fields In Queries”

How To Backup Access Database Files

How To Backup Access Database Files – The Conventional Way

With most versions of this database application, you have a built-in utility to backup a database (mdb or accdb) file and just in case you were not aware of how to backup Access database files here’s a 30 second video I found showing you how:

httpvh://www.youtube.com/watch?v=J1U-1tt-noE&feature=related

This of course takes all your objects, settings and other attributes of the database file and makes a straight forward backup copy of the file which is the same as going to the Windows Explorer and making a file copy there instead – nothing special then!

You may want to review Access 2007 backup demonstration instead which was another post back from June last year.

How To Backup Access Database Files – Alternative Ways

But if you want Continue reading “How To Backup Access Database Files”

Microsoft Access Programming – VBA InputBox Function

Microsoft Access Programming: Learn How to Use The VBA InputBox Function

Let’s get some prospective here regarding the VBA Inputbox function by first identifying if there is a real need to use this function at all considering we can build your own from scratch and take full control by using MS Access forms – right?

This is true but when using VBA and learning about Microsoft Access programming, procedures which may require some parameter value to be passed internally or as part of the workflow and rely on an outside form (as user’s can build and define) this could be ‘clunky’ to use.

The inputbox function can be used in a module (using VBA code), called in a macro in an expression or simulated as a custom built form. But in this Continue reading “Microsoft Access Programming – VBA InputBox Function”

Using The Message Box in VBA And Access Macros

Using The Message Box in VBA And Access Macros

If you know anything about Access databases and that data is automatically bound to either a table or a simple query meaning data is saved to disk then on some occasions you may want to control this action. Using the message box in VBA or as a macro procedure can intercede and prompt users before committing to disk.

This is just one example of a prompt you can add and call to your database which will help those workflows and the smooth running of the database application. Having a message prompt appear at strategic moments will add the extra layer of control.

Using The Message Box in VBA And Access Macros – Two approaches…

Users can create a message box in VBA (known as MsgBox) or as a macro which is a predefined alert prompt with a series of Continue reading “Using The Message Box in VBA And Access Macros”

Indexing In Access – Setting More Than One Index To A Table

Indexing In Access – Setting More Than One Index To A Table

Learning about Microsoft Access and applying the primary key to a table is the least you must add to your table designs. Most database developers would have used this concept and know you can have only one primary key to a table which in itself is a form of indexing in Access.

Take a look at the quick video tutorial (less than 3 minutes) which demonstrates using version 2010 how to set more than one indexed field to a table using the indexing dialog box.

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

Indexing In Access – Setting More Than One Index To A Table

Unlike primary keys, you can optionally set these indexed fields to a ‘Null’ value (a value with no value to it, which is left empty) and Continue reading “Indexing In Access – Setting More Than One Index To A Table”

Adding A Parameter In Access Query: Using A Union Query

Adding A Parameter In Access Query: Using A Union Query

If you know anything about what a parameter query is then the only added benefit and knowledge you need is knowing what and how to build a union query and apply adding a parameter to the Access query.

As a reminder a parameter is a prompt for the user to enter a value which is then passed (as a variable – varying value) into the query as it’s criteria. You can have more than one prompt but you can imagine the frustration if you have too many where the end-user will simply not bother to use it at all!

The convention for Continue reading “Adding A Parameter In Access Query: Using A Union Query”

Using Access Forms VBA: Passing Values Using OpenArgs

Using MS Access Forms VBA: Passing Values Using OpenArgs

Just getting to grips with Access Forms VBA? A very popular keyword is the DoCmd object and this command calls one of several actions within the database application including how to open a form using the sub keyword OpenForm.

For example, having a command button on one form to open another form called frm_RecipientForm would look like:

DoCmd.OpenForm "frm_RecipientForm"

On closer inspection to the above example VBA code, you will find more optional parameters (also known as arguments) that Continue reading “Using Access Forms VBA: Passing Values Using OpenArgs”