Adding Data directly into External Databases ~ LEARN MS-ACCESS TIPS AND TRICKS

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.