Back-End to Front-End database designs are common in MS-Access . 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 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 and proved that it is possible. Check the following Blog Posts to learn this trick on different types of external data sources:
- Opening External Data Sources
- Opening dBase Files directly
- Opening Excel Database directly
- Display Excel Values directly on Form
- Database Connection String Properties
- Source ConnectStr Property and ODBC
- Link External Tables with VBA
- Lost Links of External Tables
- MS-Access Live data in Excel
- MS-Access Live data in Excel-2
As you can see from the above list that serial number 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 how to send output data into external databases without linking them to MS-Access.
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 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 dBase Application on your machine. Necessary ODBC Driver Files are already installed by MS-Office in 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 is available to other processes through this query only.
Through out the above articles we were discussing about bringing data in from external databases without keeping them linked to the Access. This time we will explore how to update or add data into the external databases.
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 current stock quantity target level is 40.
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;];
Let us examine the Property Sheet of one of the above Queries to check for any indication about 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. Below 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 correct syntax in the SQL.
You can find correct syntax for Access, Excel, Paradox and ODBC connection string for IBM iSeries machine, SQL Server etc. from the above quoted Articles.
A Word of Caution:
Even though the above methods provide some convenient way to manage external tables, without keeping them permanently linked to 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 safe keep.
What kind of 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 Server (say T:\Export\mdbFolder), leaving a copy in the old . . .\Sales\. . . folder as backup, and installed in the new . . .\Export\. . . folder. Everything found working OK without triggering any error in the new location and the Users are also happy.
Your database have few Queries with the above Connection Strings in their SQL, which never crossed into your mind of attending to them and to change the SQL to point them correctly to the databases on 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 draw-backs 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.