How To Create An Access Database Parameter Query in Video

What are the most popular applications are using MS Access?

Many applications are built using MS Access! Some are full featured Inventory Systems, Accounting Systems and Payroll Processing Applications.

Some are built by programmers of small to medium scale businesses. However, some large enterprises also use MS Access for their enterprise wide applications, testifying to the power and flexibility and security features of this software.

Why dont you try exploring the features and power of MS Access to build your own applications? Now is the time to reap the benefits this powerful database software that is readily available in most personal computers running MS Windows and MS Office applications.

Linking to an Oracle 10G database through ODBC

1. Click the "Tables" under the Objects tab.

2. Right click on the center of your MS Access work area and click the "Link Tables" as shown.

3. The Link dialog box would appear as shown below.

4. Click the "Files of Type" pull down menu and click "ODBC Databases".

5. The "Select Data Source" dialog box appears. Click the "New" button.

6. The "Create New Data Source" dialog box opens. The Oracle 10G datasource is already in the dialog if you have already installed it. If you have not yet installed it, download the Oracle 10G ODBC driver from the internet and install in your computer. Click the Oracle 10G driver from among the choices.

7. Type the name you want for your datasource as in shown below. You can name it according to your preferred name, then click "Next".

8. Click the "Finish" button as shown below.

9. Type the credentials required for the database you are connecting/linking to. Service Name (usually "localhost"), user name and; password. Click the "Ok" button.

10. Your datasource link has been created as shown below. Click the "Ok" button.

11. The "Link Tables" dialog opens. Choose the tables you want to connect/link to. Then click the "Ok" button.

12. You have now successfully linked a non-MS Access table to your MS Access database. You can now work on it, create queries using the linked table as if its part of your MS Access database. Practice connecting to a different type of database like MySql. The steps are the same.

MS Access as an Audit Tool part 2!

An auditor can use MS Access as an audit tool through the following ways:

1. Connecting to another database through an ODBC driver.
2. Converting data extracted by the I.T. unit into an MS Access tables.

In this post, I will explain to the reader how to connect to another data source like Oracle, MySql and others through an ODBC driver.

Knowledge on how to do this is vital if your Audit organization does not have the budget to buy expensive audit tools like ACL or IDEA that would serve as their CAATs software. If you could train your auditors who have an inclination with the workings of database softwares, you could very well create audit tools that are as powerful as ACL or IDEA and easily customizable according to your needs.

ODBC stands for Open Database Connectivity

Open Database Connectivity (ODBC) is Microsoft's strategic interface for accessing data in a heterogeneous environment of relational and non- relational database management systems. Based on the Call Level Interface specification of the SQL Access Group, ODBC provides an open, vendor- neutral way of accessing data stored in a variety of proprietary personal computer, minicomputer, and mainframe databases. ODBC alleviates the need for independent software vendors and corporate developers to learn multiple application programming interfaces. ODBC now provides a universal data access interface. With ODBC, application developers can allow an application to concurrently access, view, and modify data from multiple, diverse databases. - Microsoft Help and Support, Article ID: 110093 - Last Review: March 29, 2007 - Revision: 1.5
ODBC--Open Database Connectivity Overview.

In order to connect to another data source or database, you need an ODBC client and an ODBC driver. The client is the Microsoft Access since it is an ODBC enabled front-end and the one which the user sees on the screen. While your ODBC driver is the one that handles the communication between your client or front end and another database. Microsoft Access has a couple of ODBC drivers already built in its system. However, if MS Access is not pre-loaded with the ODBC driver you require, you may download it from the internet where most ODBC drivers are readily available for download, you may do so and install it in your system.

For example if your back end database is Oracle 10G, download the ODBC driver for the Oracle 10G database from the Oracle website and install the driver in your operating system. Be sure to download the version applicable to your operating system.

--- See next blog post for an example of connecting to an Oracle 10G database from MS Access ---

Flash MS Access Tutorials!

Sample of my flash movie tutorials!

How to create a new MS Access 2003 database

How to open an MS Access 2003 database

How to load MS Access 2003 database

Using MS Access Queries to retrieve and display data

Using SQL VIEW or DESIGN VIEW to display, delete, update or insert data.

a.1 SELECT statement

Selecting All Columns

SQL script: Type the sql script in the SQL view of a query as in the
lines below.

Select * from tablename

--- or ---

Go to query design view as shown below.

Microsoft Access Design View:

1. Drag the asterisk of the selected table in the table or query area to
the design grid. Click Run button or View button.

See picture below.

Selecting Columns and Changing Column sequence

SQL script: Type the sql script in the SQL view of a query.

Select columname, columname, columname from tablename

Microsoft Access Design View:

1. Drag the column names of the selected table in the table or query area to the design grid according to your preferred sequence. Click Run button or View button.

Manipulating Column Names

Select columname as name, columname as name from tablename

In design view:

Change the name of the column by typing the preffered name before the column name as in the example: Accountnumber: acct_no

Accountnumber is the preffered name

acct_no is the fieldname or column name in the database

a.

Create an MS Access table from scratch

Introducing you to Access data types:


Text

Use for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes.
Stores up to 255 characters. The FieldSize property controls the maximum number of characters that can be entered.

Memo

Use for lengthy text and numbers, such as notes or descriptions.
Stores up to 63,999 characters.

Number

Use for data to be included in mathematical calculations, except calculations involving money (use Currency type).
Stores 1, 2, 4, or 8 bytes; stores 16 bytes for Replication ID (GUID). The FieldSize property defines the specific Number type.

Date/Time

Use for dates and times. Stores 8 bytes.

Currency

Use for currency values and to prevent rounding off during calculations. Stores 8 bytes.

AutoNumber

Use for unique sequential (incrementing by 1) or random numbers that are automatically inserted when a record is added. Stores 4 bytes; stores 16 bytes for Replication ID (GUID).

Yes/No

Use for data that can be only one of two possible values, such as Yes/No, True/False, On/Off. Null values are not allowed. Stores 1 bit.

OLE Object

Use for OLE objects (such as Microsoft Word documents, Microsoft Excel spreadsheets, pictures, sounds, or other binary data) that were created in other programs using the OLE protocol. Stores up to 1 gigabyte (limited by disk space).

Hyperlink

Use for hyperlinks. A hyperlink can be a UNC path or a URL.
Stores up to 2048 characters.

Lookup Wizard

Use to create a field that allows you to choose a value from another table or from a list of values using a combo box—-choosing this option in the data type list starts a wizard to define this for you.
Requires the same storage size as the Primary Key that corresponds to the Lookup field ---- typically 4 bytes.


Steps in Creating a Table in Design View

1.Click the table tab in the Groups menu bar.
2.Click “New” in the Toolbar pane to load the New Table dialog box.
3.The New Table dialog box choices are the following: Datasheet View, Design View, Table Wizard, Import Table and Link Table.
4.Choose “Design View” and click “OK”.
5.In the Design View, the user specify the field name and data type of the field.


Exercise in creating a table:

Table Name: DisbVoucher
Fields/Attributes:
DVNumber (number, primary key, length=user defined)
PayeeName (text, length = 60)
Particulars (memo)

Table Name: DVDetails
Fields/Attributes:
DVEntryID (number, primary key, length=user defined)
DVNumber (Lookup to DVNumber field of DisbVoucher table)
Debit (currency, decimal=2, format=standard)
Credit (currency, decimal=2, format=standard)


Linking your tables or creating relationships

Lookup Wizard – creates a field limited to a list of values based on other fields from other tables or you can type the valid values.


Using the Lookup Wizard:

1. Choose the Lookup Wizard from the data type choices.
2. Choose to Lookup from other tables or queries or choose to type the valid values.
3. If you choose to Lookup from other tables or queries, choose from among available queries or tables presented by Access.


Using the Relationships Window
1. Click the Tables tab in the Group menu bar.
2. Click “Relationships” to load the table relationships which looks like the one one shown below.