Switched Keys

I'm constantly coming up with new ideas for projects, articles, and ways to do things. One way I tried to keep up with that was to create text files with those notes. Many years ago I used Microsoft's cardfile.exe but it no longer exists in current versions of Windows. Then, a few years ago I tried out the AZZ Cardfile program. Shortly after that I created my own card file database similar to AZZ Cardfile, named simply Simple Cards. The cards are listed by title on the left and the content of the selected card shows in the middle content area. The cards are categorized, sortable, and searchable. In order to make the application work I had to develop a number of routines to make sure the list and cards were synchronized, including when searching and filtering, when adding or deleting cards, and when resorting. Work still needs to be done to smooth the transitions between screens, being able to select and delete multiple cards, and exporting a category of cards to an external file. A programmer's work never ends.

As in AZZ, you can also create external data files of cards in Simple Cards. The external files are actually separate databases. Ignoring the general development of Simple Cards for now, this article shows how to enable external table data in an Access program, first with an explanation and then some code samples.

When Access displays a form, it relies on a source file for the data, either directly from a table, or from a query based on one or more tables. Typically, the only time Access uses an external table is if the database is split into front end/back end, but it is possible to access external data as a record source for a form or report, without technically splitting the database and creating links to external tables. The best way to do that is to use a query instead of a table as record source for a form.

If the form is based on a query the tables are normally from within the database. However, if you open the query for editing and access the properties of the query, you can see that there is an option to specify the database to use under Source Database. If the query was created with internal tables, it will have "(current)" in gray. If you change that to refer to a different database, it will load data from that external file instead. After specifying an external database, you can view the SQL code for the query and notice that an additional qualifier, 'IN', is used in the query with the file path of the external file.

In my project, however, I wanted the user to be able to optionally access one of several different databases for the form. So the user doesn't have to go to the query and enter a long file path to do this, Application.FileDialog is used to select the database. The problem is, outside of Microsoft, there is no direct way to change the Source Database of the query. I've searched, and asked, and searched, and I cannot find a way to access those properties. In order to change that, you have to change the SQL property of the query. To load an external file, you have to build the SQL statement; and to read the filespec of a loaded file, you have to search the SQL statement.

In developing Simple Cards I also had to incorporate some user messages so the user would know what file is being used. In addition, I change the caption to show the file being used if it's an external file, both when loading external files, and when restarting with an external file. Finally, in order to make this work with less confusion for the user, instead of saving external databases with the normal extension, I used the "crd" extension. Access doesn't care, and the files won't be associated with Access.

Following are the steps I took to enable this feature.

First, I create the form to open the data using a query (qryCardFile) so I can change the query in order to change data source. On the main form is a button that enables the user to open an external file, with the following code to change the SQL statement.

Notice that I change the query by adding IN and the name of the file selected. You will also notice that I use an extra parameter (FileSelected) in the OpenForm command. The extra parameter is OpenArgs which enables you to pass the filespec to the form open subroutine. And, of course, you notice that I use this to open a different form. Using form frmReopen is a tidy way to close and reopen the main form without confusion for the user.

Following is the relevant code in frmReopen

This simply closes the main form and then reopens it with the new external data table. The change in the query's SQL in the prior code sample is what enables the loading of the external data. The Me.OpenArgs parameter is only used to update the form caption.

Incidentally, this technique could be used to view the content of any external table since the query can use the global qualifier, although additional work is required to get a list of tables in that external database.

The following code is what actually changes the caption

The following code (GetSourceDB) is what gets caption information from the SQL statement of the query

If this code cannot find 'IN' in the query, then the query must be using the internal table.

Of course, the user will not want to manually create external databases, so an option to create one is made available from a button on the form. This simple code snippet shows how to create a database and copy the structure of the CardFile table to that database. Then it uses a modified version of the ChangeCards subroutine to load that blank external file.

While the whole concept of using external files may be hard to grasp at first, with a logical study of the flow of data and order of instructions in the code it's possible to create routines to do things like this and make it look simple.

Note that you may have to set the relevant Office references for your version of Access in order to use the FileDialog routines. Some word-wrapping may have occurred in code sections presented but marking and copying usually preserves underlying formatting.

P. S.

One issue with using external database files in an application is that Microsoft Access processes queries before processing forms. As a result, if the query fails to load, the form fails to load. This could be due to an invalid card file, or a missing card file. In order to avoid this unpleasant situation, you have to error check (not shown in the code clippings above) for the file when loading the form. You can’t error check in the main form itself. In this case, the check is done in the Splash screen, or Reopen form, when it opens the main form.