Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening External Data Sources

Introduction.

Linking of external data sources like dBase, Excel or Tables from another Microsoft Access Database, in a Back-End, Front-End scenario, is the most common and efficient method to work with data, besides Tables from within the Database. If such Data Sources are linked with 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 of using another Table of MS-Access except 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 file 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 accept only integer values or values within a Range, or set the limit for accepting number of characters in a Cell maximum to 25, or accept Date or Time only and so on, but these are seldom used.

So far we were discussing about 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 hierarchy Order. Application Object is the top most 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), 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 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 into 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 releases 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 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.

Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports msaccess tips Accesstips Downloads Objects Property Collection Object Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Dictionary Object Form Report Calculation Command Button Data Emails and Alerts Query RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

Opening Access Objects from Desktop

Frequently Used Methods. Set the Form Name in Display Form Option of Current Database in Access Options . BIG DEAL!, this is the first t...

Labels

Blog Archive

Recent Posts