Access Database Tutorial

Learning about ms access query is the means to a good database management system as it is the heart of any database application. There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay the dividend and avoid silly mistakes, the illogical recordset results, and even errors. One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and its conventions. Take a look at the ms access query below-showing orders before the year 2016 and the intend of the query which suggested a date range from 1st January 2016 to 31st December 2016. The criterion for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range). It’s missing the ‘And’ operator and better still adding the ‘Between’ hand will capture the correct range too. The # (hash...

The heart of any MS Access database system resides with the queries and I’m often asked to give new users a quick overview of this powerful application –  here’s an example… A Microsoft Access Database is a desktop application which stores large amounts of data and is able to recognize relationships between those data. It consists of data and tools that enable users to manipulate that data.  One of these tools are the queries.  Microsoft Access Queries  are used specifically to organize data on the Access database. A database can hold multiple tables. Each of the tables can have hundreds of thousands of records. How the user extracts a specific section of the table or tables is by using the queries. Displaying data in simple or complex ways is the typical function of a query. One simple way is by getting the names of all the customers in a table. But you can modify your query to include only a list of customers residing in London sorted by last name, and...

Timing is very important in everything we do in life, in this post you will find out tricks to easily optimize your speed when working with MS Access database, this tips will also help you manage the time while creating access database. Each table should have primary keys:  when creating access database in MS access make sure, each and every table in your access database should have primary keys. This allows the system to quickly locate and connect records with other tables, which are normally known as a secondary index on a connecting table’s field along-side the primary key field. Eliminating Subdatasheet’s:  when working with MS access database, it creates unnecessary Subdatasheet’s between the related tables, this has a huge performance hit if the tables are open, it’s advisable you set this property none when working with MS access in other to save more time and optimize your speed. Don’t apply the index to the field with identical data:  if you have a number...

Continuing with the  Microsoft Access training  series,  building Access label reports  is pretty straight forward (especially with the built-in wizard tool). As a snapshot and quick peek into my  Access database eBook , here’s some more information to help establish your options. This type of report can be used for printing label address information to fit a pre-defined or custom defined label sheet (normally an adhesive or plain paper sheet). It can be based on a Table, Query or another external data source altogether (i.e. Excel or Outlook). It can also be used for creating badges, product labels, category name cards and other simple types of data layout reports. Some users will use Microsoft Word’s Mail Merge feature instead of the basic Access Label report where data from an Access database can support other Office applications. If you are going to take advantage of the ‘one-stop’ solution, then keeping it all within the Access database environm...

Here’s an extraction  taken from one of my eBooks , Here’s a quick overview to get you started and apply some if not all the above concepts discussed (from earlier chapters). Creating a new database in Access Step 1 –  Start Microsoft Access and you are presented with an interface where you can choose from a number of different templates. The screen shot presented below shows a few template examples: If you are going to select one of the options (refer to  Options ), you will be taken to an interface (refer to  Categories ) where you’ll see the templates that are specific to that category. Categories Step 2 –  You will need to give your database a name. To do that, click on file (located on the top left of the interface) and then click ‘save as’. You will then see two command options to ‘ Save Database As ’ or ‘ Save Object As ’ as shown in the image below. You then need to select a file type. The default file type is ‘accdb’ or you can sel...

You maybe starting to think that Microsoft Access is a buggy and is a ‘full of bugs’ application becoming very unstable indeed but the complexity and the power of what MS Access provides out ways some minor and silly errors that can occur. Error code 3047 refers to records being too large for a database file and typically triggered when attempting to import data from another external application. The common method you will see this is when you use the DoCmd. TransferSpreadSheet   method which is a VBA command to import and export datavia typically Excel. Ironically, Microsoft Access is a large database management tool but still has issues handling the wrong size type of data and there are size restrictions (even for the latest 2016 version). As a reminder, take a look the   specification sheet for Microsoft Access   to know what limits are set so you can determine the correct methods for data migration and population – it may help and avoid the time-wasting err...

There are various formatting choices that give flexibility and make reports more efficient.  Microsoft Access 2016  has a  report Wizard  (as a starter point) that walks you through the process of creating a report. The report look and feel will greatly depends on the user’s requirements. The more complex it gets, the higher the degree of customization is needed. The greatest strength of any report is in its structure. If the report is informative, it will surely stand out. You can improve your reports by inserting headers and footers, adding logos, and changing color combinations. In this blog, you will learn how to insert time and date into the header and footer sections of your  Microsoft Access 2016    report. Header and Footer Sections The header section...

7 Reasons Why Microsoft Access Database Should Be Used First of all, let’s clear up what is actually a Microsoft Access Database library. Simply put, it’s a code library or code database when programming your Access database (using VBA code) and is used to manage and access other objects from other applications and sources that are deemed external to MS Access. Therefore, to communicate with say Microsoft Excel or Microsoft Outlook, you would need to first enable and allow permission to use their respective library files before VBA could talk and work with these applications and is provides a layer of security and control how objects are utilised. So, we are talking about programming Microsoft Access Database using VBA code and it’s not uncommon to have to enable add references to other applications and objects. By doing so, you are really just re-using existing and predefined code to manipulate objects in a certain way and help to quickly build powerful procedures...

Microsoft Access Database : The mindset of the VBA programmer From my new VBA book due out soon, here’s an extract about the mindset of the  Microsoft Access  database VBA programmer… We need to start by establishing a frame of reference as to what we should expect when we are writing VBA code. Consider the scenario where you command a child to “ open the door .” Think about all the individual actions that it takes to accomplish that one statement. The child has to approach the door, align the hand with the door knob, move the hand and grasp it around the door knob, and then twist and pull the door knob while swinging the arm in an arc motion. We don’t have to command each specific step, and indeed life would be very tedious if we constantly had to issue commands at such a micro level of detail. However, somewhere along the line, the child had to learn what it meant and all the steps in the process to open a door. The child didn’t have to be told for each step and pr...

Learning about ms access query  is the key to a good database management system as it is the heart of any database application . There are many ways and questions to ask a database using queries and mastering the special conventions and criteria will pay dividend and avoid silly mistakes, illogical record set results and even errors. One of the more commonly used criteria and prone to errors (if misunderstood) is the date/time data type and it’s conventions. Take a look at the ms access query below showing orders before the year 2016 and the design of the query which suggested a date range from 1 st January 2016 to 31 st December 2016. The criteria for the above shows >=#01/01/2016 < #31/12/2016# which will not actually return the correct range and instead show what’s known as logical errors (dates outside the range). It’s missing the ‘And’ operator and better still adding the ‘Between’ operator will capture the correct range too. The # (hash sign) ...