Opening External Data Sources

Introduction.

Linking external data sources, such as dBase, Excel, or Tables from another Microsoft Access database, in a Back-End, Front-End setup, is one of the most common and efficient ways to work with data outside the current Database. Once these Data Sources are linked to an Access database, you can easily build queries, design custom Reports, or Design Forms to add or modify data directly within Access, and the source data can remain in its original Application for updates and maintenance.  Linked tables function almost like internal Access tables, with the primary limitation being that their structure cannot be modified from within Access.

To link a Table manually, highlight Get External Data in the File Menu and select Link Tables from the displayed menu. The Common Dialog Box will be displayed, and you can browse to the location of another MS-Access Database, dBase File, or Excel File. Once selected, the available objects will be displayed, and you can link them to your Access database as needed.

If the source data is an Excel database, it is advisable to assign a Range Name (using Insert → Name → Define) to the entire data area before linking it to an MS Access database.

One common issue with Excel-based data sources is that when data is copied and pasted from other applications, such as Word or text files, the column data types can become mixed. As a result, when the table is opened in Access, those columns may display #Error in affected cells due to incompatible data types.

Although Excel provides Data Validation options for manual data entry—such as allowing only integer values in a column, setting character limits (e.g., a maximum of 25), or restricting entries to dates or times—these features are rarely utilized in practice.

So far, we have discussed how to work with external data tables by manually linking them to MS Access. Later, we will explore how to link external files programmatically using VBA, without relying on the menu options mentioned above.

However, if a permanent link to the external table is not required, but the data is still needed temporarily, you can open and work with it directly from Access without creating a link.

Opening a second Access Database

To begin with this method, let us try opening another Microsoft Access database and reading data from one of its tables using VBA code. Later, we will explore similar examples for opening dBase files and Excel databases to retrieve data from those sources as well.

Normally, after launching Microsoft Access, we work with only one database at a time, which remains visible on the application surface—unless it is hidden through the Startup Options. As we know, Microsoft Access is a component-based application consisting of several objects, all organized in a hierarchical structure.

At the top of this hierarchy is the Application object, which we typically open manually whenever we start working in Access. However, it is also possible to create an Access Application object from other systems, such as Visual Basic, and interact with Access databases programmatically.

Next in the hierarchy is the Data Access Objects (DAO) library, which serves as the foundation for working with data. DAO encompasses several key components, including:

• DBEngine (commonly known as the Jet Engine)

• Workspaces collection

• Databases collection

• User Groups and Users

• Containers and Documents

• Table Definitions and Query Definitions

The following diagram illustrates the hierarchical structure of these key components. We will refer to some of them as we proceed to open a secondary database and read the contents of a table from it.

Right-click to open Large Image in New Tab/Window.

The Jet Database Engine is the core component that powers our work with Microsoft Access databases. It supports multiple Workspaces, and by default, we load or create our databases within Workspace(0).

Database security information—such as User Groups, Users, Personal IDs (PIDs), and Passwords—is managed by Data Access Objects (DAO) and stored in a separate Workgroup Information Database with the mdw file extension.

The name of the active Workgroup file associated with the current Workspace can be verified by checking the DBEngine.SystemDB property. The DBEngine object also maintains the Default User ID and Default Password associated with the active Workspace.

Displaying Workgroup Information File Name.

Type the following command in the VBA Debug Window:

? DbEngine.SystemDB

Sample Output: C:\mdbs\System.mdw

Databases in WorkSpace(0)

Let us return to the topic of Workspace(0) and continue from there. To open a second Microsoft Access database within the same workspace and read or write data to its tables, we must set a reference to DBEngine.Workspaces(0)—the workspace in which our current database resides. You can also modify the table structure, such as by adding a new field, if required.

To demonstrate this with a simple example, we will open the Employees table from the sample Northwind.mdb database and display the contents of the LastName field in a MsgBox using just a few lines of code:

Public Sub OpenSecondDatabase()
Dim wsp As Workspace, db As Database
Dim rst As Recordset, msg As String

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("c:\Program Files\Microsoft Office\Office11\samples\Northwind.mdb")
Set rst = db.OpenRecordset("Employees", dbOpenDynaset)

With rst
msg = ""
Do While Not .EOF
    msg = msg & ![LastName] & vbCr
    .MoveNext
Loop
   .Close
End With
MsgBox msg

Set rst = Nothing
Set db = Nothing
Set wsp = Nothing
End Sub

Copy and paste the above code into a Standard Module in your database and save it. Place the cursor anywhere within the code and press F5 to execute it. The LastName field values from the Employees table in Northwind.mdb will be displayed in a MsgBox after the database is closed.

The records are read in a loop to display all nine records available in the table. If you are working with a different table that contains a large volume of records, you can limit the reading cycle by modifying the condition in the Do While Not.EOF statement. This can be achieved with the help of a variable, as shown in the following code snippet.

With rst
msg = ""
X=1
Do While X < 10
    If Not.EOF then
        msg = msg & ![LastName] & vbCr
       .MoveNext
    End If

    X=X+1
Loop
   .Close
End With

The VBA Code.

The reading statements are placed within an If...Then block so that the program cycles through the loop and terminates gracefully without triggering an error if your table contains fewer than nine records. The last three statements release the memory occupied by the objects.

The first line of the code sets a reference to the Workspace(0) object, while the second line opens the Northwind.mdb database within the same workspace. Although this secondary database is not visibly displayed, you can imagine it as being loaded alongside your current database within the same workspace.

The file path shown in the code corresponds to the Microsoft Access 2003 version. In later versions of Access, the path structure may vary slightly after the \Microsoft Office\ directory, but the file will still reside within the \Samples folder. Locate the correct path for your version of Access and update the pathname in the code accordingly.

Next, we will see how to open a dBase File directly and work with it.

• ListBox Items and Date: Part-1
• Create a ListBox from another one
• Selected List Box Items and Dynamic Query
• Database Daily Backup
• Days in Month Function