Opening External Data Sources ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

Linking external data sources like dBase, Excel, or Tables from some other Microsoft Access Database, in a Back-End, Front-End scenario, are the most common and efficient method to work with data, besides Tables from within the Database. If such Data Sources are linked to the Access database, then it is easy to build queries on them to process and prepare customized Reports, or Design Forms to Add/Modify Data from within Access. The Source data can remain in the parent Application for updating information. We can work with the linked Tables with the same ease as using Tables from within except for modifying the structure of the linked Table.

To link a Table manually, highlight Get External Data in 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 and select it. They will expose Objects that can be linked to the MS-Access Database.

If the source data is an Excel Database then it is better to give a Range Name (using Insert- -> Name - -> Define Option) to the whole range of Data Area before attempting to link with MS-Access Database. The main issue with the Excel-based data sources is that if the data is copied and pasted from other sources like Word or Text files then the Data Type will get mixed up in columns and the data will show #Error in those cells when we open it in Access.

Even though Excel Cells can be set with Data Validation Options for manual data entry, like accepting only integer values in Cells in a Column, or set the limit for accepting the number of characters in Cells in a Column, like maximum to 25, or accept Date or Time only and so on, but these are seldom used in Excel.

So far we were discussing working with external data tables after manually linking them to MS-Access. We will see later how to Link external files using VBA without using the Menu Options highlighted above.

If we don't need a permanent link with the external table but we need data from it then we can open it directly from Access and work with the data.

Opening a second Access Database

To start with this method, we will try to open another Microsoft Access Database and read data from a Table with Code. We will see later examples of opening dBase File and Excel Databases to read data from these data sources as well.

Normally, after launching Microsoft Access we will open only one Database and that is always visible on the Application Surface unless we hide it through the Startup Option. We already knew that Microsoft Access is a component-based Application and it consists of several Objects and all of them are organized in the hierarchical Order.

Application Object is the topmost one and we are opening it manually every time when we want to do something in Microsoft Access. We can create Microsoft Access Application Object from other Systems like Visual Basic and work with Microsoft Access Databases.

The second in command is the Data Access Objects (DAO) at the top of all the other Objects like DBEngine (known as Jet Engine), Workspaces Collection, Databases Collection, User Groups, Users, Containers, Documents, Table Definitions, Query Definitions, and so on. Let us look into a graphical representation of the arrangement of some of these components, a few of the topmost ones because we are going to use them to open our second database and to read the contents of a Table from it.

The Jet Database Engine is the driving force behind our work with MS-Access Databases and it supports several WorkSpaces. We normally Load or Creates our Database(s) in Workspace(0).

The Database Security details like UserGroups, Users, PersonalIDs (PIDs), and Passwords are maintained by Data Access Objects (DAO) in a separate Workgroup Information Database with .mdw Extention. The active Workgroup File Name associated with the current Workspace can be checked by reading the DBEngine.SystemDB property. The DBEngine holds the Default UserID 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)

We will go back to the topic of the Workspace(0) and proceed further. We must set a reference to DBEngine.Workspaces(0), where our current database resides, to open a second Microsoft Access Database within the same Workspace to read/write data from/to its Tables. If you want to add another field to the Table you can do it as well.

To demonstrate a simple example we will open the Employees Table from the sample NorthWind.mdb Database and display the LastName Field contents in a MsgBox control with the following 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 of your Database and save it. Place the cursor anywhere within the Code and press F5 to execute the Code. The LastName Field values of the Employees Table from Northwind.mdb will be displayed in MsgBox control after closing the database. The reading of records is put in a loop to read all the nine records in that table. If you are using a different table with more records then limit the reading cycle by changing the condition in the Do While Not .EOF statement with the support of a Variable like 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 Statement so that the program will cycle through the Loop and terminate normally without running into Error if you have less than 9 records in your Table. The last three statements release the Memory occupied by the Objects.

The first line sets a reference to the Workspace(0) Object and the second line opens the Northwind.mdb Database within the same workspace. Even though this is not visible we can visualize now that there is a partner database within our own Workspace, side by side. The location of the database shown above is for the MSAccess2003 Version. Other Versions may have some difference in location addresses after the \Microsoft Office\ part, but it will be in the \Samples\ Folder. You may find the correct location of your Version of this file and change the Pathname given above.

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