Bute Tipster - Microsoft Access
Bute Tipster is a Knowledge Database giving information, hints and tips on Microsoft Office applications and Personal Skills. Bute Tipster is updated regularly and you can follow the entries on Twitter , Facebook and LinkedIn .
Microsoft Access
Introduction to VBA
21 May 2010
Microsoft Access contains two different programming tools – macros and Visual Basic for Applications (VBA). While macros provide a good, quick and easy way to automate routines within a database, VBA provides a fully fledged programming language with virtually endless possibilities.
VBA is a derivative of Visual Basic which has been a popular Microsoft programming language for some time. It is a programming language that is found in most Microsoft desktop applications such as Microsoft Word, Microsoft Excel, Microsoft Powerpoint, and the like.
VBA, or Visual Basic for Applications, is a specialised edition of Microsoft’s popular programming language called Visual Basic. The VBA derivative has been adapted so that it can work with most of the Office applications and has been provided with extra functionality so that it can be used to program documents, workbooks, and databases.
VBA Versus Macros
Microsoft Access is the only Office application that provides you with two different programming environments – macros, and VBA.
Macros were the original programming language in Access and are retained largely to ensure that the huge effort people may have made in the early days to automate their applications is not lost. Macros are not as extensive and powerful as VBA so they can be a little easier to learn to use.
However, as macros really only apply to Microsoft Access, it is generally considered a wiser use of your time and effort to learn how to use VBA because most of what you learn in Access can then also be used to automate the other applications such as Word and Excel.
VBA
VBA is an object-oriented programming language. Like macros, it is normally associated with and works on objects such as forms, fields, labels, buttons, and the like.
Unlike macros where coding is placed in a macro sheet and runs sequentially down a column, VBA coding is placed into modules. Modules can be thought of as documents into which you type your programming code. There are two main types of modules: class modules and standard modules.
Class modules can be quite complex, but suffice for this introduction to VBA, to say that class modules are generally associated with forms and contain programming code that responds to an event triggered on or by a form. For example, clicking a button will activate an OnClick event and this event can be programmed through VBA to perform certain tasks.
Standard modules are standalone modules that are independent of a form. They are used to house VBA programs that may be able to be used on several forms, or in a wider context across a database.
Procedures and Sub-Procedures
When you write VBA code you can have a few modules or a great number – the way you organise your code is entirely up to you. While VBA is generally accepted as an object-oriented language which responds to objects and events, the code that is placed into a module generally follows a sequence of steps and is procedural. The idea is that you click on a button (an object) which triggers an event (OnClick) and VBA goes and works through the relevant code associated with that event in the module.
A module, as mentioned above, is like a document in which you write your code. Each module can contain code that does lots of different things. The coding in modules is organised into procedures (sometimes also known as sub-procedures – just to confuse you!).
Each procedure will do a different thing or perform a different task. So that VBA knows where a procedure in a module begins and ends the start of each procedure commences with the word Sub, and ends with the words End Sub (the word Sub here is an abbreviation for Sub-Procedure which is shortened in common parlance to Procedure!). Even if you have just one procedure in a module it must begin and end this way.
Modules are created and edited in a special word processor built into VBA known as the Interface Development Environment (IDE). This dire sounding name is really only programming talk for a simple word processor for writing VBA code.
To review the complete Microsoft Access
Introduction to VBA
for 2007:
Click here
.
Adding records to a Table
20 May 2010
Once tables have been created in a database file the next step is to add the data to the tables. Each line that is added to a table is known as a record. Each record is a single instance of a particular entity, such as a person, an event, or an object.
The easiest way to enter data is directly into an open table. When you open a table you are shown the Datasheet view. Access always displays the records in the table plus one additional row at the bottom of the table. This is the new record row where a new record can be entered. The total number of records, shown in the status bar, will increase as you add record data.
Handy to Know…
• Long data entries will appear truncated on the screen, but the complete data is stored in the field.
• Records are saved automatically when you move to the next field or record.
• To correct an error simply type over it.
For Your Reference…
To add a record to a table:
1. Double click on the table in the Navigation pane to open it in Datasheet view
2. Type the data into each field pressing 'Enter'to move across fields
To review the complete Microsoft Access Adding records to a Table for 2007: Click here .
Validation Rules and Text
19 May 2010
There is a saying in computer circles: garbage in, garbage out.
One of your primary concerns with your system should be to ensure that, to the best of your ability, you'll always have accurate and relevant information in the system. The saying above is generally correct when it comes to databases – you won’t be able to produce meaningful information from the database if the data that is entered into it in the first place isn't worth a pinch of salt.
That is why in good database software such as Microsoft Access there are plenty of little features and facilities that can help to reduce the likelihood of incorrect data entering the system. Access allows you to make changes to the field properties of a table. Properties are attributes that control the way that an object either works or looks. There are several field properties that can be used to check what has been typed and to restrict errors and unwanted data being entered into the table.
Ensuring the accuracy of the data is known as validation and is an important aspect of any system design. With some fields it’s easy to anticipate what data would normally be entered into them. When this occurs you can create a default value that automatically appears in the field whenever a new record is accessed. In our Employees table most new employees are full time and work a 35 hour week. These values can be made the default value when new records are created value when new records are created in the table.
Handy to Know…
The Validation Text appears when the user types a value in the field that doesn’t match the expression. In our example above, if the user types a date that the expression calculates as making the employee younger than 18, the text will appear in a box on the screen and the user must correct the date.
For Your Reference…
To assign a validation rule to a field:
1. Open the table in Design View
2. Click on the desired field
3. Click in the Validation Rule property and type the rule as an expression (formula)
To review the complete Microsoft Access Validation Rules and Text for 2007: Click here .
Parameter Query
18 May 2010
A query selects data based on a field list and criteria that are entered into the query grid in design view. This is fine if the criteria and field list never change. However, you may find that you need to run a query on a regular basis with different criteria depending on particular situations or events.
A parameter query can save you the hassle of entering the design view and manually changing the criteria of a saved query. A parameter in computer language refers to some information or data that is passed from one object to another. In a parameter query, the parameter is simply the example that needs to be entered into the criteria cell of the query grid. When you run a parameter query, you are prompted for the criteria by a user-friendly dialog box. The criteria you type are placed temporarily in the query grid to create the query. A parameter query is created in the same way as a normal query, except that you place square brackets around text in a criteria cell in lieu of an example of the search data. Access uses this text as a prompt in a dialog box that appears when the query is run. Whatever the user types as a response to this prompt then becomes the criteria (passed as a parameter) in the query.
Handy to Know… You can enter parameters for more than one field if you wish. However, Access will stop the query for each one and prompt for criteria. Too many stops and prompts can become very frustrating! As an alternative you can create a form to capture the data required for the query.
For Your Reference… To create a parameter query: 1. Create the query in the normal way 2. Type a prompt enclosed in square brackets in the criteria cell that will be used for the query.
To review the complete Microsoft Access Parameter Query for 2007: Click here .
Designing a Relational Database
17 May 2010
Creating a database file with table structures in Microsoft Access is relatively easy to do. As a consequence there is an inclination amongst the uninitiated to jump straight in and create a database file without giving much thought to the overall objective and design of the application. This is not the right way to approach database development.
Planning, Planning, and More Planning… Seasoned database developers know that the more time and effort that they put into the overall planning of the database the less hassle they will face later in the actual use and operation of it. So we will now stand on our soap box and reiterate the immortal words of database development: "The process of database development includes planning, planning, and more planning." The chorus of database development is "know what you want to do before doing it".
There are many ways of approaching the development of a database system, and these ways vary depending upon the complexity of the system. Most developers use a top down approach – starting with the overall concepts, and then identifying the components required.
The Three Steps of Database Design There are three main steps to designing an effective relational database:
Scope the System Scoping is all about determining what the system should be able to do. You begin by establishing the overall aim of the system. Ask yourself one fundamental question: “What is the database that I will construct supposed to do?”
Determine the Inputs and Build A Prototype Table Structure Once you have an idea of what information is required from the system you can determine what data needs to be entered into it. Here you determine the data that will be required to be entered into the system to achieve what the system is supposed to do. Once the inputs have been determined you can construct a test table or tables that will be used to store the data. This prototype table is often drawn on paper.
Normalise the Data Arguably the most academic of the steps, normalising involves applying a set of database design rules to the prototype table. As you apply the rules you adjust the table or tables until you have arrived at a network of tables that ensure efficient data storage and eliminate all possible examples of redundancy (repetition).
To review the complete Microsoft Access Designing a Relational Database for 2007: Click here .
Introduction to VBA
Microsoft Access contains two different programming tools – macros and Visual Basic for Applications (VBA). While macros provide a good, quick and easy way to automate routines within a database, VBA provides a fully fledged programming language with virtually endless possibilities.
VBA is a derivative of Visual Basic which has been a popular Microsoft programming language for some time. It is a programming language that is found in most Microsoft desktop applications such as Microsoft Word, Microsoft Excel, Microsoft Powerpoint, and the like.
VBA, or Visual Basic for Applications, is a specialised edition of Microsoft’s popular programming language called Visual Basic. The VBA derivative has been adapted so that it can work with most of the Office applications and has been provided with extra functionality so that it can be used to program documents, workbooks, and databases.
VBA Versus Macros
Microsoft Access is the only Office application that provides you with two different programming environments – macros, and VBA.
Macros were the original programming language in Access and are retained largely to ensure that the huge effort people may have made in the early days to automate their applications is not lost. Macros are not as extensive and powerful as VBA so they can be a little easier to learn to use.
However, as macros really only apply to Microsoft Access, it is generally considered a wiser use of your time and effort to learn how to use VBA because most of what you learn in Access can then also be used to automate the other applications such as Word and Excel.
VBA
VBA is an object-oriented programming language. Like macros, it is normally associated with and works on objects such as forms, fields, labels, buttons, and the like.
Unlike macros where coding is placed in a macro sheet and runs sequentially down a column, VBA coding is placed into modules. Modules can be thought of as documents into which you type your programming code. There are two main types of modules: class modules and standard modules.
Class modules can be quite complex, but suffice for this introduction to VBA, to say that class modules are generally associated with forms and contain programming code that responds to an event triggered on or by a form. For example, clicking a button will activate an OnClick event and this event can be programmed through VBA to perform certain tasks.
Standard modules are standalone modules that are independent of a form. They are used to house VBA programs that may be able to be used on several forms, or in a wider context across a database.
Procedures and Sub-Procedures
When you write VBA code you can have a few modules or a great number – the way you organise your code is entirely up to you. While VBA is generally accepted as an object-oriented language which responds to objects and events, the code that is placed into a module generally follows a sequence of steps and is procedural. The idea is that you click on a button (an object) which triggers an event (OnClick) and VBA goes and works through the relevant code associated with that event in the module.
A module, as mentioned above, is like a document in which you write your code. Each module can contain code that does lots of different things. The coding in modules is organised into procedures (sometimes also known as sub-procedures – just to confuse you!).
Each procedure will do a different thing or perform a different task. So that VBA knows where a procedure in a module begins and ends the start of each procedure commences with the word Sub, and ends with the words End Sub (the word Sub here is an abbreviation for Sub-Procedure which is shortened in common parlance to Procedure!). Even if you have just one procedure in a module it must begin and end this way.
Modules are created and edited in a special word processor built into VBA known as the Interface Development Environment (IDE). This dire sounding name is really only programming talk for a simple word processor for writing VBA code.
To review the complete Microsoft Access Introduction to VBA
Once tables have been created in a database file the next step is to add the data to the tables. Each line that is added to a table is known as a record. Each record is a single instance of a particular entity, such as a person, an event, or an object.
The easiest way to enter data is directly into an open table. When you open a table you are shown the Datasheet view. Access always displays the records in the table plus one additional row at the bottom of the table. This is the new record row where a new record can be entered. The total number of records, shown in the status bar, will increase as you add record data.
Handy to Know…
• Long data entries will appear truncated on the screen, but the complete data is stored in the field.
• Records are saved automatically when you move to the next field or record.
• To correct an error simply type over it.
To add a record to a table:
1. Double click on the table in the Navigation pane to open it in Datasheet view
2. Type the data into each field pressing 'Enter'to move across fields