LEARN MS-ACCESS TIPS AND TRICKS: database

Introduction.

During the installation of Microsoft Access 2003 or earlier versions, the NorthWind.mdb sample database also will be installed on your computer automatically. The sample database is very helpful for those who are starting with their self-learning practices of Microsoft Access.

Those who are migrating from Microsoft Excel Worksheets and tables created in line with what they have practiced in Worksheets find themselves faced with a lot of issues. In fact, Excel itself has database management support, even though they are not as flexible as Microsoft Access. Excel Users can browse for database basics in Excel Help documents to set up a database in Excel and learn to organize information by following the database management rules and retrieval methods, like sorting, searching, filtering, and so on. This helps to learn database management system rules, and it will help you to organize Excel worksheets or databases in such a way that they can be easily linked/imported into Microsoft Access if the need arises at a later stage.

Coming back to the sample database creation in Microsoft Access 2007, it doesn't create a sample database (Northwind) during the installation of MS Office on your computer. But, you can create one yourself from the sample Templates provided. It contains all the tutorial materials you need to learn Microsoft Access.

How to Create NorthWind.accdb database.

Open Microsoft Access 2007
You will find several Database Template Categories displayed on the left panel and the Featuring category, is selected by default. In the middle window, the template Blank Database is displayed, and other templates are designed for specific purposes.
On the left panel find the Sample Template category and select it.
Click on the Northwind 2007 Template.
The database will be saved in the active folder by default. You may change the folder by clicking on the folder icon to the right of the database file name.
Click Create Button to create the sample database in your preferred folder specified.

Always use the sample database as a reference point for all your issues regarding Table designing, setting up Relationships, Queries, Forms, Reports, and Macros are designing. Do a few trial and error practices on the specific task you are trying to accomplish, with whatever knowledge and ideas you have. You will get a better insight into the task you are trying to design and you can find solutions in most cases yourself. If you are not able to do it then with the trial-and-error background you will be in a better position to explain your specific issues and seek help from other sources.

Access Users Forums.

You can search this Website for topics that you are interested in, or post your queries and get help from experts in Microsoft Access Users' Forums on the Internet. Links to some of the popular Forum Websites are given below:

Introduction.

The Back-End, Front-End database designs are common in MS-Access. The back-end can be MS-Access, dBase, SQL Server, Excel, or Paradox databases with their linked Tables. Once the tables are linked they function as if they are the native tables of the Access Database. You can design Queries, Forms, Report on them and manage them from FE.

But, can we manage without directly linking them to the FE? For example; can we create a Query in the current database using an external Table (not a linked table) from another MS-Access database?

This topic we have already discussed earlier proved that it is possible. Check the following Blog Posts to learn this trick on different types of external data sources:

As you can see from the above list that serial numbers 1 to 6 methods are used for bringing external data into Access in different ways without keeping them linked to the Access Database. When working with dBase or FoxPro Tables, the path to the Folder, where the dBase/FoxPro Table is located stands as the database name.

If you have gone through the second Article Opening dBase Files directly, then you already know what we are going to explore here and have a general idea by now as to how to send output data into external databases without linking them to MS-Access.

Sample SQL for External dBase Table.

Before going into that, let us take a closer look at the sample SQL that brings in data from a dBase Table through a Query, without linking the table into the MS-Access database.

NB: If you don’t have a dBase Table, to try out these examples, you can create dBase Tables by exporting one or more of your Access Tables into a separate folder on your disk. You don’t have to install a dBase Application on your machine. Necessary ODBC Driver Files are already installed by MS Office on your machine.

SELECT Employees.* FROM Employees IN 'C:\MydBase'[DBASE IV;];

The SELECT Query will return the entire records from the Employees.dbf Table from the dBase database ‘C:\MydBase’. The text [DBASE IV;] is the database type and version indicator. The SQL IN 'C:\MydBase'[DBASE IV;]; clause creates a direct link to the Employees.dbf Table without a physical link. That means the Employees.dbf data are available to other processes through this query only.

Throughout the above articles, we were discussing bringing data from external databases, without keeping them linked to Access. This time we will explore how to update or add data to the external databases.

Updating Data into External dBase Table.

A sample SQL that updates an external dBase Table is given below:

UPDATE Products IN 'C:\MydBase'[DBASE 5.0;] SET Products.TARGET_LEV = 45
WHERE (((Products.TARGET_LEV)=40) AND ((Products.REORDER_LE)=10));

With the above SQL, we are updating the Products stock Target level to 45 from 40, for items with Re-order Level (Minimum Stock Level) is 10 and the current stock quantity target level is 40.

Appending Data into External dBase Table.

Let us append some data from Products_Tmp Table from the current MS-Access Database to the Products.dbf Table of C:\MydBase dBase Database. The sample SQL is given below:

INSERT INTO Products
  SELECT Products_Tmp.*
  FROM Products_Tmp IN 'C:\MydBase'[DBASE 5.0;];

IN Clause and Query Property Setting

Source Database and Source Connect Str Properties.

Let us examine the Property Sheet of one of the above Queries to check for any indication about whether the SQL IN Clause setting is in there or not.

Open one of the above Queries in Design View.
Display the Property Sheet of the Query. Press F4 or ALT+Enter to display the property sheet and make sure that it is the Query Property Sheet. Under the Title of the Property Sheet, there will be a description: Selection Type Query Property.
You may click on an empty area to the right of the Table on the Query Design surface to make sure that the Property Sheet displayed is Query's Property Sheet, not the Table or Query Column Property Sheet. Check the sample image given below.
Check the Source Database and Source Connect Str Property Values. If you find it difficult to memorize the correct syntax of the IN Clause in the SQL then you can populate the respective values in these properties of the Query as shown. This will automatically insert the Connection String with the correct syntax in the SQL.

You can find the correct syntax for Access, Excel, Paradox, and ODBC connection string for IBM iSeries machine, SQL Server, etc., from the above-quoted Articles.

Caution:

Even though the above methods provide some convenient way to manage external tables, without keeping them permanently linked to the MS-Access database, extensive use of this method can lead to issues at a later stage if you are not careful. It is important that you maintain some form of documentation of these Queries for safekeeping.

Constant Location Reference Issues?

Let us take the example of an external MS-Access database itself. The SQL given below appends some data into the Employees table in another Microsoft Access database on LAN Server directly. This is a routine process done daily or weekly etc.

INSERT INTO Employees IN 'T:\Sales\mdbFolder\Database1.accdb' 
SELECT Employees_tmp.*
FROM Employees_tmp;

Everything works just fine and you forgot about this specific Query or other Queries similar to this one. After about six month’s time you thought of shifting or copying the databases from the current location into another Folder on the Server (say T:\Export\mdbFolder), leaving a copy in the old folder as a backup, and installed in the new . . .\Export\ folder. Everything was found to work OK without triggering any error in the new location and the Users are also happy.

Your database has a few Queries with the above Connection Strings in their SQL, which never crossed your mind of attending to them and changing the SQL to point them correctly to the databases to the new location. The Queries will keep on servicing the Table in the old location . . .\Sales\. . . , instead of the Table in . . .\Export\. . . Location. The data missing problem when reported by the user may not trigger the Query IN Clause button in your mind immediately and you may be pulling your hair to find out what went wrong, wasting hours or days, till you arrive at the accident spot.

In spite of these drawbacks, it is a good way to use the external databases when needed only, if the frequency is minimal, rather than keeping them always attached to the FE.