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”

Access Analyzer: know More About The Microsoft Access Table Analyzer Wizard Tool

Access Analyzer: know More About The Microsoft Access Table Analyzer Wizard Tool

You may not be aware that Microsoft Access has a collection of tools to help build, maintain and optimize your Access database and one feature is the Access Analyzer (or also known as the Analyze Table wizard).

Take a look at this well delivered video tutorial which is just short of 6 minutes and covers the power behind this wizard tool…

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

Access Analyzer: know More About The Microsoft Access Table Analyzer Wizard Tool

This video explains some of the pre steps used when importing external data into Microsoft Access. The Access analyzer tool then steps your through to create the table joins it suggests creating not just primary keys (for the unique ID – ‘one’ side) but foreign keys (for the ‘many’ side of a table join).

Once you have completed the wizard and included or excluded exceptions for mis-matched values, you can now manually add and tidy up additional tables maintaining RDBMS (a relational database management system) taking advantage of Microsoft Access’s rich features.access analyzerLearning about the Access analyzer tool will take away the guessing games that inexperienced Access users may find when creating relational databases.

To learn more about Access relationships and other eBooks that I offer, check out my eBooks page. You may want to download my FREE eBook on how to import data into Microsoft Access to get your started.

Access Requery Or Not To Requery – That Is The Question

Access Requery Or Not To Requery – That Is The Question

The following four methods can be found in Microsoft Access and have different uses:

  1. Access Requery
  2. Access Refresh
  3. Access Repaint
  4. Access Recalc

They can be split into two parts where the first two points handles records (the data) and the latter two the objects and their components (no data). Therefore you can start by answering the first simple question to help navigate to which part is best to use:

“Do I want to update data or components in my Microsoft Access database?”

access requeryAccess Requery Or Not To Requery – That Is The Question: The Definitions

Access Requery

Using the Requery method you are calling a complete reloading of all records from the underlying table or query, This means Continue reading “Access Requery Or Not To Requery – That Is The Question”

Access Subforms – Using The Simple Wizard Tool To Create MS Access Subforms

Access Subforms – Using The Simple Wizard Tool To Create MS Access Subforms

I was going to create a quick video tutorial about Access subforms but you know there are already many videos out there and to begin with I recommend the following 3 minute one below using MS Access 2007.

httpvh://www.youtube.com/watch?v=cIsvsAfBxoA

Access Subforms – Using The Simple Wizard Tool To Create MS Access Subforms

The author takes you through the easiest approach of creating MS Access subforms using the built-in wizard tool. You just need to make sure you have a common field that will be the join between the main form and the sub-form in otherwords, have your relationships ready.

You can nest (subforms inside subforms) Access subforms too but keep it simple, you do access subformsnot want to over develop the Access form making it too busy looking and also slowing down the performance if not designed with care.

To learn more about Access relationships, Access forms and much more, take a look at my eBook bundle offerit’s a popular seller!

Microsoft Access Random Number: Setting As The Default Value

Microsoft Access Random Number: Setting As The Default Value

How do you generate Access random number and make it also unique so it can act as your primary key?

The first point to make is you do in fact have a ‘Random’ option (via the ‘New Values’ property) when setting a field to an ‘AutoNumber’ data type but you cannot edit this value (should the need arise) and the sequence is completely, well random!access random numberI use my own random series but still have control should I need to edit the field and Continue reading “Microsoft Access Random Number: Setting As The Default Value”

MS Access Autonumber – How To Take Control And Change The Starting Number

MS Access Autonumber – How To Take Control And Change The Starting Number

Using the MS Access AutoNumber data type field is the first step to keeping the data integrity of your Access database under control stopping end-users from mismanaging key records.

However, when setting the MS Access Autonumber data type to increment, it starts with the value of 1 incrementing by one for each new added record. But what if you wanted to start at 1000? Instead of me creating a quick video tutorial to show you how, let’s ask Paul to demonstrate it below in this 3 minute video…

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

MS Access Autonumber – How To Take Control And Change The Starting Number

So you now know a little more about MS Access Autonumber data types unique properties having seen it demonstrated in this video. You will need to know something about append queries which is one of the four action queries Microsoft Access offers.

There is another property value available other than ‘Increment’ option which is required for this technique to work and that is called ‘Random’.

Look out for my next post on how to create your own random unique value which can be edited and controlled using the ‘Default’ property for a new record.

In the meantime, I welcome your comments below and any questions you may have.

Microsoft Access Icons: How To Build Your Own Application Access Icon

Microsoft Access Icons: How To Build Your Own Application Access Icon

Before you distribute your pride and joy Access database, there a few final properties to consider one being the Application Access icon which replaces the standard Microsoft Access icons.

The first thing to know about uploading and setting the Access icon is that it will take either a bitmap (.bmp) or icons (.ico) file formats. So if you have a .png or a .jpg then what?

The other question is if don’t have an icon and want to build your own then breathe a sigh of relief as I will now show you how you can easily build your Access icon for free!

Microsoft Access Icons: How To Build Your Own Application Access Icon – The Steps

Let’s assume you do not have or can find a personalised icon for your database but you have an image you would like to adapt as the Access icon.

Here’s a great free resource that will let you build icons: http://www.favicon.co.uk/

access icons

  1. Get your image (most graphic formats are good) ready
  2. Go to the website where you will clear see the area to browse for your image file.
  3. Personally, I would choose the icon size 32 x 32 though you can play with this.
  4. Click the ‘Generate FavIcon’ button and you will a link to ‘Download Your Favicon Here’
  5. Now in Microsoft Access, open your database and navigate to the Options screen choosing the ‘Current Database’ section.
  6. Browse for you new .ico file and click OK.

There you have it, a professional touch to your Microsoft Access database which can make the difference between sloppiness and perfectionism and I think Access icons is a step closer to the latter.

If you are looking to build an Access database whether you are a beginner or wish to extend your knowledge, then my online coaching may be the answer – give it a try 🙂

Microsoft Access Input Mask: Getting To Know The Input Mask Code

Microsoft Access Input Mask: Getting To Know The Input Mask Code

“Square pegs in round holes” as the saying goes is not a natural fit leaving unwanted gaps if it can fit at all. Microsoft’s Access input mask utility is designed to make data entry fit properly into a field and avoid creating data leaks maintaining data integrities.

Ideally, this should be set to selected fields in a table (and it can be set at the form level too). I mention ‘selected fields’ as you do not want to over use this feature and restrict or frustrate end-users when entering new records.

access input maskMicrosoft Access Input Mask: Getting To Know The Input Mask Code

When you want to create an Access input mask, in the design view of the table locate the ‘Input Mask‘ property and click the elipses icon (to the immediate right) to launch the Input Mask Wizard tool.

This wizard has a few examples to try out but is not the full list of options open to you. You can build you own codes too but this will require some more knowledge on the code markers around.

Try this example: >L<?????????????? which means the first character will be capitalised followed by upto 14 characters in lowercase. The code > represents the uppercase format which mweans the < means lowercase. The letter L represents a mandatory letter followed by ? meaning optional letters.

Other code markers change the rules which is explained in more detail from the Microsoft help files in Access or by searchiung online for “Access Input Mask“.

These are some of the codes you can use:
0, 9, #, L, ?, A, a, &, C, >, <, !, \, “”*, . , : ; – /

Anyone have some great examples of Access input mask codes they would like to share? Why not reply on the comment box below.

Data Types In Access Databases: Knowing More About Date/Time

Data Types In Access Databases: Knowing More About Date/Time

This quick 80 second video simply shows you how to set data types in Access and this example is the Date/Time data type. It’s not that difficult to create but just in case you weren’t sure – it’s below here for your information before I explain a little background to the Date/Time data type.

httpv://www.youtube.com/watch?v=3rwvgz7bTZI

Data Types In Access Databases: Knowing More About Date/Time

How are dates stored in Microsoft Access? Knowing more about data types in Access will give better control over how you handle different values in a field especially when calculating with them.data types in accessWith Dates (and Time), Access stores a date in the format we use and understand but in fact Microsoft Access uses a simple incremental unique value starting with a 1 representing 31st December 1899. Therefore, this week we had a leap year date (29th February 2012) which equates to 40,968 days that have lapsed since the 31st December 1899.

So in essence, you now calculate with the underlying values and subtracting two dates is the same as subtracting two values showing the balance of days between to dates.

With Microsoft Access, you can actually store dates from the 1st January 100 (yes, year 100) which has a unique value of -657,434 to 31st December 9999!