Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Opening External Data Sources

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.

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.

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 the Global 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 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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Passing Two Dimensional Array to Function

First of all, I have some good news for you, our website: LEARN MS-ACCESS TIPS AND TRICKS has been selected by https://blog.feedspot.com pa...

Labels

Blog Archive

Recent Posts