#ms 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 sign) is the exact convention and good check to ensure it’s the right data type too (date/time in this case).\

A revised example is shown below:

If the field is a actual date/time data type, you do not need to enter the # (hash sign) as it will automatically fill this in for you when moving the cursor elsewhere in the restrain (another convention checker for you).

If however, you see double-quotes (“ “) wrapped around the date expression, this suggests either you have chosen the wrong field or the date field is actually a text data type and it will simply not work as expected.

One final point to consider and even check for you ms access query database and those

queries are the regional setting of the operating system you are using. UK versus USA setting often throws up confusion and inaccuracies to so ensure you are using the correct date/time formats.

MS Access query is very powerful indeed and there are many permutations available – take

a look at my eBook Microsoft Access 2016 Database Queries.

Do you need for eBook relating to Access Database?

When Microsoft Access first appeared in the early 1990’s it revolutionized the desktop database market. Most versions have been similar to each other. Now with Access 2007, we see the most deep-seated changes yet introduced by Microsoft. When working your way through an MS Access 2007 tutorial, a hardcore traditional user of MS  Access is either going to love or hate these new changes.

So what is different about this new version? The interface is completely different and instead of menus and toolbars, there is the Ribbon. The Ribbon is separated into sections and each section holds the relevant commands for designing and running a database.

Creating forms has become easier and there are some latest forms to choose from. One example is the split form. The database window has also been replaced with the routing pane. This offers more features and is not as straightforward to use as the old database window.

As a fraction of the MS Access 2007 tutorial, you should expect to learn about trusted  locations. When loading a database you will get a warning that it may not be trusted. You can eliminate this warning by assigning a folder to become trusted. If you lay up your database in that folder then the warning will no longer show.

Creating tables has also become easier. When creating tables as part of your MS Access 2007 tutorial course, you may use one of the easy creation methods, such as creating columns in datasheet run mode. However, it is better to look at using the design view to create tables as this will give you a better understanding of columns and data types.

The built-in wizards will only get you so far. They are great starting points, but if you want to become a master then it is better to try and create the database components manually. By doing this you will learn the intricacies of why things are done. For example, there are reasons why you need to save the ID of a drop-down list of data rather than the data itself.

Hunting around the internet for bits and pieces to help you learn is not the way to go. There has to be some structure to the learning and it is better to start from the ground up. There is little point in trying to run before you can walk.

When looking for a good MS Access 2007 tutorial, make sure it covers all you want to learn and you gain a good all round understanding of the important concepts and components to creating a working database.

Let’s recap what we’ve learned in this article:

Access 2007 has a new look interface

There are new form types available

Learning Access 2007 should be done from a structured tutorial series

To get started with an MS Access 2007 tutorial, check out the best Microsoft Access tutorial available at http://access-databases.com/ms-access-tutorial/

Article Source: http://ezinearticles.com/expert/Paul_Roger_Barnett/279463

This article gives a clear and concise overview of what’s new in Access 2007 and how the newer and richer features make it very easy to create tables, queries, forms and reports with a simple click and go actions using the predefined templates. Those migrating from earlier versions may feel a little disorientated at first especially the Ribbon Bar but it won’t take that long to familiarise yourself with this version.

Building Access forms is a time consuming aspect to front-end database designs and in this Microsoft Access tutorial, I want to add some extra aesthetics and alternative control enhancements for you.

There are many Access form controls, formats and properties that can be applied to a form over and above the basics ranging from the more advanced conditional formatting options to the custom built controls including my recent post on coloured tab controls.

So here’s some alternatives for your traditional command button…

Using the standard command button, you can still apply different format effects using the correct event with some Access VBA code or adopt a different control altogether (i.e. a Label) and make this your button click action instead.

Hyperlinks are also another way to apply actions for opening another form or running a report and can exclude VBA code (for basic actions).

Microsoft Access Tutorial: How To Build Alternative Access Form Controls – The Command Button

Let’s work on and illustrate the alternative button option using a Label and some VBA code. Take a look at the following screenshot:

When you hover over an item in the dark blue banner, the item turns a subtle white from the resting light blue resetting all other menu items including the ‘Close‘ item.

Of course, you can set any format by adding a raise, sunken effect or enhancing with underline, bold, change the background or changing font sizes – it’s how creative do you want to be?

I used a Label control and set the default to a non-selected state. In this example, I used the following:

Set the Caption property as you may want to change the caption from say ‘On‘ to ‘Off‘.

Back Style property to ‘Normal‘

Back Color property to ‘#003366‘ which reflects the dark blue banner colour though could be set as transparent.

Border Style property set to ‘Transparent‘.

Border Width property set to ‘Hairline‘.

Border Color property set to ‘No Color‘.

Special Effect property set to ‘Flat‘.

Fore Color property set to ‘#7DBEDC‘ (which is light blue colour).

You may want to look at my eBook on How to Build Access Database Forms for more detailed explanation on the above properties and general design techniques for your forms.

I would suggest you also apply some consistency to the names for your Labels that will be acting as a button as you could be clever to group and loop your Access VBA code more effectively. You could also adopt the Tag property to help separate this type of control use with others.

What about the VBA code?

Well, you will probably want a public procedure to reset all controls and individually apply format changes to the selected item.

Therefore, using the main event called ‘On Mouse Move‘ would be advisable. You will still need to add code the ‘On Click‘ event so when users click the item it will execute the action of opening a form or report or whatever it is your want to action (which has been excluded in this article).

I created a public sub procedure called ResetMenuLabels that runs the following code:

(Please ignore the word ‘Private’ in the above code snippet. It should be ‘Public’ as it should really be stored in a module (which this example doesn’t use) making this available to any form).

The above Microsoft Access tutorial code will loop through all controls for your form but only check to first the active control is a Label type and that is had the Tag set as ‘lb‘ (standing for Label Button). Providing you don’t use this convention for any other control, you are pretty safe for this form to reset all label buttons back to the resting style.

I’ve only used a simple format here for illustrative purposes and of course would expect you take it to the next step and apply your chosen styles – simple fill in between the loop structure.

The added element now is to call this procedure for the individual control’s ‘On Mouse Move‘ event with the additions for highlighting this control standing out from all others.

Here’s an example for the ‘Close‘ button label:

So now this Microsoft Access tutorial on how to use alternative buttons opens the mind a little further.

The downside unfortunately is this will add to the time consuming task for your Access form designs!

This article talks about the importance of the  Append Query and the appropriate way of creating them in Microsoft access database.

In Microsoft access database and all the other database management systems, queries as the heart of the software system which can execute numerous actions to make your database more systematized and functional. Queries prove to be useful for working on various tasks including returning record-sets in an ordered and filtered way, updating values, editing or deleting data, and even making a new table in the access database.

Action Queries can be used for adding, changing, or deleting numerous records from a table, record or field at a click of a button.

The additional advantage of an Action Query is that user can preview their query results in Microsoft access 2016 before running it.

Microsoft access database provides the following types of Action Queries:

Append

Update

Delete

Make-Table

Note that users cannot undo an action query and therefore, they must create a backup of the data that they want to update using the query.

Steps for creating an Append Query

Append query is a kind of (SQL statement) action query which lets users add records to a table. It is also called an Insert Query since it uses ‘INSERT INTO’ command as its SQL syntax. Users can use Append Query to add new tables or data to another table. It can likewise be used for fetching data from multiple tables.

To make this process extra clear, let’s use two of our existing tables — StaffID and StaffSalary.

Step 1: Open the database that has the records that you want to copy.

Step 2: Click the Create tab and select Query Design to open the query designer.

Step 3: At this point, you should be able to see the Show Table dialog box. Double-click the tables or queries that hold the records that you want to copy. For our example, we are going to use StaffID and StaffSalary. Click Close.

Step 4: Double-click each field that you want to append. Each of the fields you select will be shown in the Field row of the query design grid.

Step 5: Click Run on the Design tab to see the query result.

Step 6: Verify that the query returned the records that you want to copy. If you see the need to add or remove some fields, return to Design view and add or delete the fields.

Step 7: Convert the select query to an append query. To do this, select Design View from the View drop-down menu on the Home tab.

Step 8: Click the Append button and a dialog box will show.

Step 9: Next, you need to specify whether to append records to a table in the current database or to a table in another database. If the table is in the current database, click Current Database in the dialog box and choose the table from the drop-down list. If the table is in another database, click Another Database then click Browse to select the destination database. Once you’ve chosen the database, select or enter the name of the destination table in the Table Name box. For our example, we will use the Staff table. After selecting, click the OK button.

Step 10: You then choose the destination fields. The way you select destination fields depends on how you created your SELECT query. If you added all the fields from your source table, Access will add all the fields in the destination table to the Append to row in the design grid. If you added every field to the query or used expressions, and the field names in the source and destination tables match, Access will automatically add the matching destination fields to the Append to row in the query. If you added individual fields or used expressions, and any of the names in the source and destination tables do not match, Access will add the matching fields and leave unmatched fields blank. In this case, you can click a cell in the Append row and choose a destination field.

Step 11: Preview the query before running. Do this by switching to Datasheet View.

Step 12: After that, return to Design View and click Run to append the records.

You will then be able to see all the additional data which have been appended to the destination table.

microsoft access database .The Task Tool utility was built to demonstrate the ease of use and how MS Access VBA code can communicate with other applications (and not necessarily just with Microsoft applications).

My utility comes with instruction notes and the code is left open for you to view and edit to enhance and adapt to your own systems – Click here for the sales offer page.

You can also use the form’s design as a template for your other forms and extend the colour code (VBA) to create a more polished application.

You do not need to know any VBA code – just download and plug in to operate. If however, you have some Access code knowledge, then expand on the simple procedures and make them more globally accessible to other parts of the database system.

Don’t forget to check out my other utilities which all ship with open source code and a money back guarantee plus 30 day free email support. My other Microsoft Access database offers.  microsoft access database