LEARN MS-ACCESS TIPS AND TRICKS

MS-Access Live Data in Excel

a.p.r. pillaiAugust 22, 2008External Links, MsaccessLinks 2 comments

MS-Access Live Data in Excel.

We have already explored several techniques for accessing and working with external data sources—such as Access tables, dBase files, and Excel worksheets—without permanently linking them. These included both VBA-based and query-based approaches.

However, we have not yet experimented with using an ODBC connection string in the SourceConnectStr property of a query to open external data files. While linking external data to Access is straightforward, we will later explore how to perform this linking dynamically through VBA.

Before diving into that, let’s look at something slightly different—a reversal of the usual “one-way traffic” of data flow from external sources into Access. Interestingly, the data flow isn’t truly one-way: we can also update external data directly from Access, whether or not those data sources are permanently linked.

When we use queries configured with SourceDatabase and SourceConnectStr properties to open external data, the resulting datasets are fully updatable. Any changes made to these records within Access are automatically reflected in their original parent applications.

Live MS-Access Data in an Excel Sheet

In this section, we will explore how to link live Microsoft Access data into Excel so that any updates made in Access are automatically reflected in Excel. In this setup, Access functions as the server application, while Excel acts as the client.

There is, however, one important distinction from our earlier methods: although you can edit the linked data in Excel, those changes will not be written back to Access.

So, what’s the purpose of having Access data in Excel? You can use it to create charts, perform calculations, or prepare analytical reports—especially if you’re more comfortable working in Excel. You can also create links (using Copy → Paste Special → Paste Link) to reference this live data in other parts of the workbook. This way, any updates in Access will automatically appear in your reports or charts within Excel, ensuring your information always stays current.

To bring Access data into Excel, we use Microsoft Query, which serves as the intermediary between the two applications. The Query Wizard will guide us through the necessary steps to connect Excel with the Access database.

For our example, we’ll use the Categories table from the Northwind.mdb sample database.

Step through the following procedure.

Open a new MS Excel workbook.
Select Cell A1 on Sheet1.
Point to Import External Data in the Data Menu.
Select New Database Query from the displayed menu.
Now, the Microsoft Query Wizard opens up and displays a Dialog Box. It displays the Database Sources list in the Databases tab, which you can link to MS Excel. This is a combined list of items appearing in the ODBC Dialog Control User DSN, System DSN, and File DSN Tabs.
Right-click to open Large Image in New Tab/Window.
Select the MS-Access Database* from the list and click OK.
The Common Dialog Control opens up, allowing you to browse to the Location of the MS-Access Database and select it. Find the sample database C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb (MS-Access 2003; you can drop 11 from Office11 in the location address for Access 2000), select it, and click OK.
Select the Categories Table from the Available Tables and Columns in the Query Wizard and click on the > symbol to select all the Fields of the Categories Table into the Columns in your Query Control. If you don't need all the fields from the Source Table, then expand the Categories Table by clicking on the + symbol to display all the Fields and select only those you need and move them to the right side panel.
Right-click to open Large Image in New Tab/Window.

Before you move the field to the right, you can preview the Field contents by clicking the Preview Now button below. Memo Field or OLE Object field contents cannot be previewed this way.
After selecting the Fields, click Next. Here, you can define Filter conditions.
Right-click to open Large Image in New Tab/Window.
Click Next to proceed to the Sort options.
Right-click to open Large Image in New Tab/Window.
Click Next to move to the Finishing point.
Here, we have the option to save the selected settings in a Microsoft Query (which is an external File) at the location C:\Documents and Settings\User\Application Data\Microsoft Queries\. If we need any changes in the data selection options, then we can open this saved file in Microsoft Query and edit the Query Definition in the SQL Window.
Right-click to open Large Image in New Tab/Window.
See the Radio Button set on Return Data in Microsoft Office Excel and click Finish.
In the next Dialog Control, you can select the Location on the Excel Sheet where you want to insert the data from Access. Since we have already selected Cell A1 as the target location in Step 2 above, this will appear as the default location in the control; click OK without change.

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

The records from the Categories Table will be inserted in the Excel Worksheet, starting from the range address A1.

It was a long journey from Access to Excel. Bringing Excel data into Access needs only two property changes in an MS Access query, and now you know how simple it is.

Refreshing Updates from Access Table.

Now that we have successfully brought Access data into Excel, let’s perform a few simple experiments to confirm that it is indeed live data—directly linked to the Access database. We’ll also observe how any changes made in Access are automatically reflected in Excel, demonstrating the dynamic connection between the two applications.

There are two methods to refresh Access Data in Excel: Manual and Automatic.

Keep the Northwind.mdb sample database open so that we can make changes in the linked table in Excel or in Access and check the results of the change in both Applications.

Open the Categories Table of the Northwind.mdb Database.
Add Crabs and Lobsters in the Description field of the last record. Or add a new record with some Category Name and Description.
Minimize MS Access and display the Excel Window, and check whether the change has taken place immediately in the linked data in Excel. You may not find any change on the Excel side. We have to refresh the data in Excel to reflect the changes.
Click anywhere within the data Area.
Select Refresh Data from the Data Menu.
Now, any changes you make in Access will automatically be updated on the Excel side as well. Moreover, you can configure Excel to refresh the linked data automatically at regular intervals, eliminating the need to perform manual updates.
Right-click anywhere within the linked table in Excel and select Data Range Properties from the shortcut menu.
In the dialog box that appears, you will find several options to manage the linked data, including the name of the query that retrieves data from Access into Excel. Under the Refresh Control section, select the Refresh every option and set the interval to 1 minute. This allows you to observe the automatic refresh in action without waiting too long.
Next, switch to the Access window and either undo the earlier changes made to the Categories table or make new edits that will be easily noticeable in Excel once the data refreshes.
Return to Excel and wait for the refresh to occur. You should soon see the updates reflected in the worksheet—Excel will continue to refresh the data automatically at one-minute intervals.

If you have made any changes to the data on the Excel side, those modifications will be lost during the refresh process.

When you close and reopen the Excel workbook, a prompt will appear asking whether Excel should automatically refresh the linked data. You can choose to enable or disable this feature according to your preference.

We will continue our discussion on this topic in the next post, rather than crowding all the details here.

Database Connection String Properties

a.p.r. pillaiAugust 15, 2008External Links, MsaccessLinks 1 comment

Database Connection String Properties.

Opening external data sources such as dBase files, Excel sheets, or tables from another MS Access database directly through VBA code is just one of the available options. For those who are not comfortable working with VBA, a better alternative allows you to access external data without permanently linking it to your Access database.

In either approach, one thing remains essential: you must know how to correctly reference different external data sources using the appropriate Source Database Path and Connection String values. Fortunately, these methods are quite straightforward to learn.

While understanding the syntax for ODBC (Open Database Connectivity) connection strings can be slightly challenging, there are some helpful shortcut techniques to determine them. However, we will set aside the ODBC discussion for now and revisit it later with a relevant example.

As demonstrated earlier, we can display values from Excel cells or ranges directly on MS Access form controls.

In the previous examples on opening Access tables, dBase tables, and Excel tables directly through VBA, you may have considered copying the SQL strings from the VBA code into queries and testing them there. If you haven’t done so yet, now is a good time to try—it’s a great way to understand how they work and to explore a few important details about their usage.

Open Recordset in Select Query, from External Access Database

Let us try to open an MS Access table from another Database directly in a Select Query.

Open one of your Databases or create a new one.
Select the Query Tab and Click the New Button on the Database Menu. Select the Design View from the displayed Options and click Close without selecting any Table from the list.
Click on the SQL Toolbar Button or select SQL View from the View Menu.
Copy and Paste the following SQL String in the SQL Editing Window, and Save the Query with the Name EmployeesQ.
```
SELECT Employees.* 
FROM Employees IN 'C:\Program Files\MicrosoftOffice\Office\samples\Northwind.mdb';
```
Note: If you are using MS-Access 2003, then change the Path to . . .\Office11\Samples\.
Open the Query in Datasheet View (View -> Datasheet View) to display the Employees Table contents from the Sample Database Northwind.mdb.

In the SQL String, the IN Clause points to the correct database path, and the entire Path Name is put in quotes and ends with a semicolon, indicating the end of the SQL String, which is applicable for all Queries.

A Different and Simple Method.

There’s another way to accomplish this. The first part of the statement can be written directly in the SQL window, while the IN clause can be added separately in the Source Database and Source Connection String properties of the query. This approach eliminates the need to remember where to place the word IN, or where to insert opening and closing quotation marks, freeing you from such syntax concerns.

Create a New Query and open the SQL editing window following Steps 1 to 3 explained above.
Write the SQL string SELECT Employees.* FROM Employees;
Select Design View from the View Menu to change the Query from SQL View to Design View. We are now in the normal Query Design View. You will find the Employees Table object appearing in the Query Design without any Field Names showing in it.
Select Properties from the View Menu to display the Property Sheet of the Query.
Click on an empty area of the Query surface, to the right of the Table Name, to display the Query level Property Sheet correctly.
There are two properties on the Property Sheet that we are interested in.
- Source Database
- Source Connect Str
Here, we will be using only the Source Database Property for the external MS Access Table.
Enter the following Path Name in the Source Database Property, overwriting the text (current).

C:\Program Files\Microsoft Office\Office\samples\Northwind.mdb
Or with the change explained above for MS-Access 2003 cases.
Select Datasheet View from the View Menu.

You will now see the same result as the first Select Query we tried. If you change the Query into its SQL View and inspect the SQL String, you can see that the IN Clause is formed using the Source Database property Value.

Source Connect Str for dBase and Excel.

You can use the same method for a dBase Table and an Excel Table (Named Range). In these cases, we need to use the Source Connect Str Property.

Example: opening the dBase Table directly.

Table Name: Customer.dbf

SQL String: SELECT Customer.* FROM Customer;

Source Database Property Value = C:\mydBase

Source Connect Str Property Value = DBASE IV; (check the semicolon at the end)

Replace the Customer table name and the Path C:\mydBase location address with your own dBase File Name and Folder name, where the dBase Table is located.

Example: opening an Excel Table (Named Range) directly.

Table Name: Categories

SQL String: SELECT Categories.* FROM Categories;

Source Database Property Value = C:\My Documents\Products.xls

Source Connect Str Property Value = Excel 5.0;

(note the semicolon at the end)

Replace the Categories table name and the Path C:\My Documents\Products.xls Excel file with your own.

NB: You must first define the Name Categories in the Excel Table Range using Insert -> Name -> Define before attempting to use the Name Categories in MS-Access Queries.

Next: MS-Access Live data in Excel

Earlier Post Link References:

Opening Excel Database directly

a.p.r. pillaiAugust 08, 2008External Links, MsaccessLinks 1 comment

Opening an Excel Database directly.

In the world of Personal Computers, many applications emerged and disappeared. One of the very popular applications was WordStar (Word Processor), which used the operating systems CP/M-80 (Control Program for Microcomputers) and MS-DOS (Disk Operating System) till 1992. There were dedicated Word Processing Machines in those days, using WordStar and competing Software WordPerfect.

The first spreadsheet program, VisiCalc, was released in 1979 and made a groundbreaking impact. It soon inspired several successors—SuperCalc, Multiplan, Lotus 1-2-3 (1983), and eventually Microsoft Excel (1985)—each adding more power and functionality than the original VisiCalc. (Source: Wikipedia)

Like Microsoft Access, Microsoft Excel also includes powerful automation capabilities that make spreadsheet-based computing both flexible and engaging.

Spreadsheet programs are designed for analytical tasks that involve a chain of calculations, with all relevant data visible on the sheet. A small change made at the beginning of a calculation can trigger a cascading effect through dependent cells, instantly updating the final results. This makes Excel an invaluable tool for “what-if” analysis. And when it comes to creating graphical charts, Microsoft Excel remains the first name that comes to mind.

Excel Table

Although Excel includes limited database-like features, such as built-in functions, filters, and sorting capabilities, it can still be used effectively for structured data management. This brings us to our next example—creating a table in Excel that can be read from or updated by Microsoft Access.

While Excel provides the tools needed to follow general database principles when creating and maintaining a table, these rules are often overlooked. Users are free to design their worksheets in any manner, even mixing regular data and database-like tables side by side on the same sheet.

In contrast, Access enforces strict data integrity rules—for example, text cannot be entered into numeric fields, field names must be unique, and data cannot exceed defined field sizes. The same principles technically apply in Excel, but violations often go unnoticed because Excel doesn’t always flag them. As a result, the worksheet may not function as intended when used as a data source.

Moreover, not all Access data types exist in Excel—for instance, the True/False field type in Access has no direct equivalent, although Boolean values like TRUE and FALSE are still valid entries in Excel cells.

Before we create a database-style table in Excel and open it directly in Access (as we did with a dBase file), let’s explore how to set data validation rules in Excel to restrict cell entries. You may be surprised by how powerful these validation features can be.

Validation Settings.

Example: Accept only values between 25 and 100 in a Cell or Cells.

Open Excel and select a Cell in Sheet1.
Select Data -> Validation ... -> Settings.
Select Whole Number in the Allow control.
Enter 25 in the Minimum Control and 100 in the Maximum Control.
Select the Input Message Tab. Enter Age in the Title Control and type Enter Value between 25 and 100 in the Input Message.
Select the Error Alert Tab and type Value Error in the Title Control.
Type Valid Value between 25 and 100 in the Error Message Control and click OK to close it.

Try entering a value less than 25 or greater than 100 in this cell, and it will display the Error message that you have set up in the Validation Control.

You can apply the same rules quickly to other cells. Copy the Cell, highlight the Range of Target Cells, and select Edit -> Paste Special -> Validation. If you paste it over existing data, it will not validate the field contents if the wrong value is already present in the Cell. The validation check is performed only when you manually key in values.

Following the same procedure, try setting up a validation rule in Excel to accept 15 characters or fewer in a cell. Try entering 16 characters or more into that Cell.

When planning to create a database in Excel, begin by defining short, meaningful headings in the top row—these will serve as your field names. Next, apply data entry rules (as explained earlier) to each column so that only valid values can be entered. Excel also provides a built-in Data Entry and Search Form, similar to the one available in Microsoft Access.

If you already have a data table in Excel, simply click anywhere within the table and select Form from the Data menu. This will open a Data Entry/Search Form. By clicking the Criteria button, you can switch it into search mode, where you can enter specific criteria in any field to locate a record.

You can experiment with this feature after we create an Excel table, which we’ll later open directly in MS Access through a VBA program.

Preparing for a Trial Run.

Open Microsoft Excel (if it is already closed).
Open the NorthWind.mdb sample database. Check the link Saving Data on Forms not in Table for location references if you don't know where this file can be found.
Open the Categories Table in Datasheet View.
Right-click on the top left corner of the Datasheet View and select Copy from the shortcut menu.
Click on the Excel Icon on the Taskbar to open it and select Cell A1.
Select Paste from the Edit Menu.
While the highlighting is still on the pasted Table, select Insert -> Name -> Define and type Categories in the Names in Workbook control; click the OK button to close it.
Save the Workbook with the name: C:\My Documents\myData.Xls, and close Microsoft Excel and close the Northwind.mdb sample Database.
Open any one of your Databases or create a new one.

Copy and paste the following code into the Global VBA Code Module of your Database and keep the Module open. You may save the Module by selecting the Save Toolbar Button or with the File -> Save option.

Public Sub OpenDirectExcel()
'-----------------------------------------------------
'Open Excel Table directly
'Author : a.p.r. pillai
'URL    : www.msaccesstips.com
'All Rights(c) Reserved by msaccesstips.com
'-----------------------------------------------------
Dim db As Database, rst As Recordset
Dim strSql As String, i As Integer
Dim msg As String

strSql = "SELECT Categories.* FROM Categories IN 'C:\My Documents\myData.xls'[Excel 5.0;];"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

i = 0
With rst

msg = ""
Do While Not .EOF And i < 5
   msg = msg & ![Category Name] & vbCr
   If ![Category Name] = "Confections" Then
      .Edit
      ![Category Name] = "Chocolates"
      .Update
   End If
   i = i + 1
   .MoveNext
Loop

.Close
End With

MsgBox msg, , "Product Categories"

Set rst = Nothing
Set db = Nothing

End Sub

Click in the Code and press F5 to run it. Displays the Product Category Names of the first five Records from the Categories Table in C:\My Documents\myData.xls in a Message Box.

Note: In the VBA Code, we have tested the Category Names Field for the value Confectionaries, and updated the Value Chocolates back into the Excel Cell, overwriting the word Confectionaries.

Check the SQL Syntax in the Code that pulls the data directly from the Named Range Categories in the C:\My Documents\myData.xls file.

Next: Database Connection String Properties

Earlier Post Link References:

Display Excel Value Directly on Form

a.p.r. pillaiAugust 01, 2008External Links, MsaccessLinks 2 comments

Display Excel Value Directly on Form - MS Access 2003.

In the earlier post, Opening dBase Files Directly, we learned how to open and work with dBase files in MS Access using VBA. However, there are several alternative methods—other than linking—to handle dBase files within Access. We’ll explore those options in detail later.

This post, however, was originally meant to demonstrate how to open an Excel-based data table directly and work with it in VBA. We’ll cover that in the next section. But before diving into that, let’s take a short detour to try out some simple yet fascinating Excel tricks.

I say “simple” because you won’t need to wrestle with any complex VBA code this time. And I know you’ll appreciate that little break from my so-called spaghetti code!

As the title suggests, we’re going to explore how to display values from an Excel worksheet directly on Microsoft Access forms—no heavy coding required.

The Demo Run Preparation

Open Microsoft Excel.
Enter your name in cell A1 on Sheet1, then minimize Excel—but don’t close it. You may save the workbook with a suitable name. Keep in mind that if the AutoSave feature is turned on, Excel may prompt you to save your changes.
Open any one of your Microsoft Access Databases or create a new one.
Open a New Form in Design View.
Select the Text Box Tool from the Toolbox and draw a Text Box on the Detail Section of the Form.
Click on the Text Box and display the Property Sheet (View -> Properties).
Write the following expression in the Control Source Property of the Text Box:

=DDE("Excel","Sheet1","R1C1")
Save the Form with the name of your choice.
Open the Form in Normal View. You will see your Name written in an Excel Cell appearing in the Text Box on the Form.

The DDE() Function stands for Dynamic Data Exchange. The first two Parameters are the Excel Application and the worksheet name Sheet1, and the third one is the Cell Reference where your name is written in R1C1 (Row-1 Column-1 or Cell A1). The cell reference must be used in this way rather than the A1 style.
Do the following if not successful.
But you may end up with nothing showing in the TextBox. In that case, you have to see if a particular Option is set correctly on the General Tab of Options in the Excel Tools Menu.
Click on the Excel icon on the Taskbar to open it. Click on Tools -> Options, and select the General Tab.
Remove the check mark (if it is set) from the Ignore Other Applications option in the Settings Options.
Minimize Excel, close the MS Access Form, and open it again. Now you must see your name in the Excel Cell on the Form. Don't close the Form.
Maximize Excel, make some changes to the Name, and minimize again. The change may not reflect immediately on the Form. You can update the Form value manually without the closing and opening steps.
While the Access Form is in Normal View, select OLE/DDE Links from the Edit Menu.
The Links Dialog Box will open up. Select DDE Links in the Links Group control. The Links list will appear below. Select the Link and click on the Update Now Command Button. Now the change on the Worksheet will reflect the Form Value.

Microsoft Access also includes option settings similar to those in Excel. Generally, instead of manually refreshing changes—as we did earlier—the updates should occur automatically at fixed intervals according to the settings under Tools → Options in Microsoft Access. However, in my experience, this feature hasn’t worked reliably on my system so far.

Select Tools → Options → Advanced tab. Ensure that the Enable DDE Refresh option is checked if it is not already enabled. In this section, you can also view the default settings for automatic refreshing of Dynamic Data Exchange (DDE) and ODBC links — including the number of retry attempts and the wait time before Microsoft Access tries again in case of a failure.

Display Value in Combo Box and Option Group.

You can display Values from Excel in two more Access Controls: ComboBoxes and an Options Group Control. See the sample image given below.

The Combo Box Method

Minimize Access and Maximize Excel.
Enter a few Names of people, books, or anything else in Cells A9 to A17.
Enter Value 2 in Cell C1 and minimize the Excel Application Window.
Maximize MS Access and open the Form in Design View.
Disable the Control Wizards Button on the Toolbox. Select the Combo Box Tool and draw a Combo Box Control on the form so that it looks like a List Box. See the sample image given above.
Display the Property Sheet and write the expression
```
=DDE("Excel","Sheet1","R9C1:R17C1")
```
In the Control Source Property of the Combo Box.
You may give a Name to the Range: R9C1:R17C1 and use that Name in the expression, replacing the Range Address. To name the Excel Cell Range, highlight the Range, select Insert -> Name -> Define, and type the name, say List, in the Names in Workbook control. Replace the third Parameter R9C1:R17C1 with the Range Name List in the DDE Function.
Open the Form in Normal View. The Names from the Excel Range will appear in the Combo Box.

Note: You cannot select any of these values and insert them into the Text Box portion of the Combo Box and use it in a data field.

The Option-Group Option.

We will try one more example with the Options Group Control.

Turn On the Control Wizard in the Toolbox. Select the Option Group Tool from the Toolbox and Draw an Option Group Control on the Form. Refer to the example given above.
Type three Labels in the Wizard: Data View, Print Preview, and Print, or anything else you prefer, on the control, and click Finish.
Click on the outer frame of the Options Group and display the Property Sheet (View -> Properties).
Write the following expression in the Control Source Property:

=DDE("Excel","Sheet1","R1C3"

The selection of items on the Options Group will be based on the value given in Excel Cell C1. Now the value in Cell C1 is 2. The second item in the Options group will now show as selected. Change the Value in Cell C1 to 3 and refresh the Form as explained above; the option will change to 3.

Note: If the Excel Application is not active when you open the Form with the DDE() Function, then Access will show the following Error Message:

"You tried to open a form or report that includes a DDE or DDESend function in a calculated control that specifies an OLE server application."

"Do you want to start the application in Excel?"

If you respond with Yes, then the Excel Application will be started with Blank Sheets. You must open the Excel Workbook that provides information for the DDE() function manually, and display the values in the Access Form.

Next: Opening an Excel Database directly.

Opening dBase Files Directly

a.p.r. pillaiJuly 25, 2008External Links, MsaccessLinks No comments

Opening dBase Files Directly

In the earlier article “Opening External Data Sources,” we learned how to open another Microsoft Access database and work with its tables using VBA code. I have revised that code from the previous post to include an additional feature — it now displays the database names loaded in Workspace(0) at the top of the list of employee names in the message box.

The revised code is provided below. You can copy it, replace the earlier version, and try it out.

Revised VBA Code.

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

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

dbcount = wsp.Databases.Count - 1

msg = ""
For x = 0 To dbcount
 msg = msg & "Database(" & x + 1 & ") " & Dir(wsp.Databases(x).Name) & vbCr
Next
msg = msg & vbCr
With rst
x = 1
Do While x < 6 And Not .EOF
    msg = msg & ![LastName] & vbCr
   .MoveNext
   x = x + 1
Loop
   .Close
End With
MsgBox msg

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

The statement wsp.Databases(x).Name Returns the full path of the database file. To make the display in the message box more concise, I have wrapped it in a Dir() function, which extracts only the file name from the full path. The Dir() function also checks for the existence of the specified file in the given folder, and if it is found, returns just the file name.

Opening dBase Table

Opening the dBase File is comparatively a simple operation. Create an SQL string with a reference to the dBase Database Folder, the Table Name, and the dBase Version (dBase III, IV, or 5.0) of the Table, and open the Recordset directly. The sample SQL String is given below:

strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];"

If you don't have a dBase file on your Machine to try this out, you can export one of your own Microsoft Access Tables to dBase III, IV, or 5.0 Versions.

I have used the exported Employees Table from the NorthWind.mdb sample database in our example. To try the Code given below without change, you may export the Employees Table from the Northwind.mdb sample database. If you don't know where to find this file, visit the link Saving Data on Forms Not in Table for location references.

Exporting Employees Table as a dBase Table

Create a Folder on your Disk C:\MydBase.
Open the Northwind.mdb database.
Select the Employees table.
Select Export from the File Menu.
Select dBase III or dBase IV, or dBase 5 in the Save as Type Control in the Common Dialog Box.
Browse to the Folder C:\MydBase.
Type the File Name Employee in the File Name Control and Click Export.

Note: dBase Application File uses only 8 characters for the name and 3 characters for File Name Extensions. When you export the Employees Table, it will shorten the name to 8 characters and save it as a dBase table Employee.dbf. The exported Table's Field Names will also be truncated after the 10th character if they are longer.

When the Employees Table is exported in dBase format, several files are created in the output folder depending on the Version of dBase (III, IV, or 5.0) you have selected. The list of files will look like the samples given below:

EMPLOYEE.INF (contains the Index File Details)
EMPLOYEE.DBF (the data, except for the Memo Field Values)
EMPLOYEE.DBT (the Memo Field contents)
LastName.NDX (LastName Field Index information if saved as dBase III)
Postalco.NDX (PostalCode Field Index information if saved as dBase III)
PRIMARYK.NDX (PrimaryKey Index information if saved as dBase III)

If you export a table in dBase IV or 5.0 format, the information contained in the last three files will be consolidated into a single multiple-index file with the MDX extension. The export, import, and link operations are managed by the dBase driver, known as ISAM (Indexed Sequential Access Method), a standard mechanism used by database systems such as dBase and FoxPro (up to version 3.0).

When you attempt to link a dBase table to your MS Access database, Access will look for all the associated files to load the information correctly. For example, if the file EMPLOYEE.DBT has been deleted from the folder, the table import or link operation will fail with the error: “Cannot locate the XBase memo file.”

You can export the Employees table to dBase IV or 5.0 formats to experiment with SQL syntax for these versions. However, dBase III syntax can also be used to open tables from the later versions.

Having covered all the necessary fundamentals of dBase files, it’s now time to open and work with the data. Copy and paste the code below into a Global Module in your database and save it via File → Save. Place the cursor inside the code and press F5 to run it.

A message box will appear displaying the contents of the LastName field from the Employee.dbf file. If you are using your own dBase file, modify the code to reference your table and field names accordingly.

VBA Code for Opening dBase Table

Public Sub OpenDirectDBF()
'Open DBase File directly and read contents
Dim db As Database, rst As Recordset
Dim strSql As String, i As Integer
Dim msg As String

strSql = "SELECT Employee.* FROM Employee IN 'C:\MydBase'[DBASE III;];"

'Syntax for dBase IV & dBase V
'strSql = "SELECT Employe4.* FROM Employe4 IN 'C:\MydBase'[DBASE IV;];"
'strSql = "SELECT Employe5.* FROM Employe5 IN 'C:\MydBase'[DBASE 5.0;];"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSql, dbOpenDynaset)

i = 0
With rst
msg = ""
Do While Not .EOF And i < 5
   msg = msg & ![LastName] & vbCr
   i = i + 1
   .MoveNext
Loop
MsgBox msg
.Close
End With

Set rst = Nothing
Set db = Nothing
End Sub

Tips: Even if the table name length is more than 8 characters (for example, Employees with 9 characters), the SQL syntax will ignore any characters beyond the eighth, and the file will still open correctly.

You can enable the SQL statements in the code for other dBase versions by removing the single quote (') at the beginning of the line and then running the code.

If you have FoxPro version 2.5 or 3.0 installed on your machine, replace it [DBASE III;] with [FoxPro 2.5;] or [FoxPro 3.0;] to work with those files. Note that later versions of FoxPro require a DSN-based syntax.

Displaying Excel Value directly on the Access Form is next.

Earlier Post Link References:

Opening External Data Sources

a.p.r. pillaiJuly 18, 2008External Links, MsaccessLinks No comments

Opening External Data Sources.

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.

PIE Chart Object and VBA

a.p.r. pillaiJuly 11, 2008Graph Charts, msaccess graphs 4 comments

PIE Chart Object and VBA.

This post continues our discussion on working with the Chart Object in VBA. In the earlier article, Working with Chart Object in VBA, we created a small table and a report containing a Chart Object, and we explored a few basic property modifications to prepare for the upcoming VBA demonstrations.

Before proceeding, please review Steps 1 to 7 outlined in the earlier post by following the provided link, and then continue from here.

You will find it helpful to read the related topics, Column Chart and VBA, for additional context and practical examples.

PIE Chart Options.

If you have already tried the code examples from the earlier posts, you should find most of the segments here familiar and easy to follow. The main differences in this section involve the formatting of individual pie slices and a few property adjustments.

In this example, we will experiment with three types of Pie Charts:

3D Pie
3D Exploded Pie
Pie of Pie

Each variation demonstrates a different way of projecting data segments and controlling the visual layout of your chart through VBA.

Copy and paste the code given below into a Global Module in your Database.

Public Function PieChart(ByVal ReportName As String, ByVal ChartObjectName As String)
'---------------------------------------------------
'Author : a.p.r. pillai
'URL    : http://www.msaccesstips.com
'Date   : July-2008
'Customized Source Code : from Microsoft Access Help
'---------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer, chartType(1 To 5) As String
Const twips As Long = 1440

On Error GoTo PieChartChart_Err

chartType(1) = "3D Pie Chart"
chartType(2) = "3D Pie Exploded"
chartType(3) = "Pie of Pie"
chartType(4) = "Quit"
chartType(5) = "Select 1-4, 5 to Cancel"

cr = vbCr & vbCr
msg = ""
For j = 1 To 5
  msg = msg & j & ". " & chartType(j) & cr
Next

ctype = "": typ = 0
Do While typ < 1 Or typ > 4
 ctype = InputBox(msg, "Select Chart Type")
 If Len(ctype) = 0 Then
    typ = 0
 Else
    typ = Val(ctype)
 End If
Loop

Select Case typ
    Case 4
        Exit Function
    Case 1
       lngType = xl3DPie
    Case 2
       lngType = xl3DPieExploded
    Case 3
       lngType = xlPieOfPie
End Select

DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName)

grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource

If Len(recSource) = 0 Then
   MsgBox "RowSource value not set, aborted."
   Exit Function
End If

'get number of columns in chart table/Query
'if Table or SQL string is not valid then
'generate error and exit program
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close

're-size the Chart
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5 * twips
    .Height = 4 * twips
End With

'activate the chart for modification.
grphChart.Activate

'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .chartType = lngType
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = chartType(typ) & " Chart."
    .HasDataTable = False
End With

'format Pie slices with gradient color
With grphChart.SeriesCollection(1)
    .HasDataLabels = True
    .DataLabels.Position = xlLabelPositionBestFit
    .HasLeaderLines = True
    .Border.ColorIndex = 19 'edges of pie shows in white color
    For j = 1 To .Points.Count
        With .Points(j)
            .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
            .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
            .DataLabel.Font.Name = "Arial"
            .DataLabel.Font.Size = 10
            .DataLabel.ShowLegendKey = False
            '.ApplyDataLabels xlDataLabelsShowValue
            .ApplyDataLabels xlDataLabelsShowLabelAndPercent
        End With
    Next
End With

'Chart Area Border
With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With

'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 17
    .BackColor.SchemeColor = 2
    .TwoColorGradient msoGradientHorizontal, 2
End With

'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 6
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 1
End With

grphChart.Deselect

DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview

PieChart_Exit:
Exit Function

PieChart_Err:
MsgBox Err.Description, , "PieChart()"
Resume PieChart_Exit
End Function

Press Ctrl+G to display the Debug Window (Immediate Window) in the VBA Editing View, type the following command in the Debug Window, and press Enter:

PieChart "MyReport1", "Chart1"

Sample run: an image of Option-1:

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

Sample run: an image of Option-2:

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

Sample run: an image of Option-3:

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

Run it from the Command Button Click.

You can run this program from the On_Click() Event Procedure of a Command Button on your Form. The program will:

Open myReport1 in Design View.
Resize the Chart1 Chart Object to a size suitable for printing or viewing.
Format the chart elements.
Save the report and reopen it in Print Preview.

Key points about PIE Charts:

The analysis of values in a Pie Chart differs from other chart types, which is why Pie Charts accept only one set of values.
For example, if four people decide to buy a Cake worth $100, and their shares are $60, $20, $14, and $6, the Pie Slices will reflect the proportion of each share relative to the Total Cost.
Unlike Bar or Column Charts, where formatting can be applied to an entire data series using grphChart.SeriesCollection() - Pie Charts require you to access individual slices through grphChart.SeriesCollection().Points() - This allows you to apply different gradient colors or formatting to each slice separately.

Tip: You can use the same Points() method to format individual bars of a Bar Chart, but you must remove the Pie-specific properties from the code to avoid errors.

.DataLabels.Position = xlLabelPositionBestFit
.HasLeaderLines = True
.Border.ColorIndex = 19 'showing edges of Pie slices with white color
.ApplyDataLabels xlDataLabelsShowLabelAndPercent

Chart Formatting.

In Pie Charts, the X-Axis and Y-Axis titles are not used, so the corresponding code segments have been removed from the program. The Chart Area and Plot Area are formatted using a different set of light colors, distinct from the previous examples.

You can also use the Rnd() function to generate different color sets for each run, applying gradient formatting. However, the resulting color combination may not always be ideal for viewing or printing. If you are not satisfied with the current color scheme, you can experiment with different color values from the Color Chart provided in the first post, Working with Chart Object in VBA.

Download the Demo Database.

Download Demo Database

Column Chart and VBA

a.p.r. pillaiJuly 04, 2008Graph Charts, msaccess graphs 1 comment

Column Chart VBA.

In our earlier example, working with Access Graph Chart in VBA, we prepared MS Access Charts with VBA and ran sample tests for three types of charts: Clustered Column, Line, and Pie. If this is your first time here, refer to the earlier post by clicking the link above. Follow Steps 2 to 7 to complete the preparations before trying the code presented here.

Previously, we were able to change the chart type in code to any of the three types while applying common property settings. However, some properties, such as Chart Axis Titles, were skipped conditionally for the Pie Chart. We will handle Pie Charts separately later, including formatting individual pie slices and modifying other settings.

Here, we will focus only on Column Charts and run the code for four different designs. Column Charts, with vertical bars, are the most commonly used type and are often loosely referred to as bar charts. The category labels for the plotted values appear horizontally along the X-axis below the chart, while the chart scale is calculated automatically and displayed vertically along the left side (Primary Y-Axis). This scale can also be set manually if needed.

When small values are plotted alongside large values, the differences in the small values may not be clearly visible. In such cases, you can plot the small value series on the Secondary Y-Axis (right-side vertical axis) to make the variations more noticeable. Adding data labels further enhances the readability and analysis of the chart.

For 3D Column Charts, the Y-axis is replaced by a Z-axis to display tick labels and axis titles. Note that horizontal bar charts are the true representation of bar charts.

Sample Demo Run Code

We have created options in the Code to run for the following Options:

Column Clustered.
Column Clustered (Reverse Plot Order - flipped upside down)
3D Column Clustered.
3D Column Stacked.

There are optional properties in this Code that you can use to change the shape of the Bars to a Cone or a Cylinder.

In this example, we enable the HasDataTable property of the Chart Object, which displays the source data table directly on the chart. The X-Axis category labels, Qrtr1 to Qrtr4, become part of this data table display. Note that if you try to modify the X-Axis Tick-Label properties, such as Font or Font Size, while the data table is visible, you may encounter errors. We have included this code conditionally rather than removing it completely, so you can observe the difference.

The DataLabels Orientation property allows you to tilt the data labels to a specified angle in degrees, in addition to the standard horizontal or vertical display.

To try out these examples, follow the steps below:

The VBA Code.

Copy and paste the following code into the Global Module and save it. If you have already gone through the earlier Post and gone through steps 2 to 7, then you are ready to run the Code.

Public Function ColumnChart(ByVal ReportName As String, ByVal ChartObjectName As String)
'---------------------------------------------------
'Author : a.p.r. pillai
'Date   : June-2008
'URL    : http://www.msaccesstips.com
'Customized Source Code : from Microsoft Access Help
'---------------------------------------------------
Dim Rpt As Report, grphChart As Object
Dim msg As String, lngType As Long, cr As String
Dim ctype As String, typ As Integer, j As Integer
Dim db As Database, rst As Recordset, recSource As String
Dim colmCount As Integer, chartType(1 To 6) As String
Const twips As Long = 1440

On Error GoTo ColumnChart_Err

chartType(1) = "Clustered Column"
chartType(2) = "Reverse Plot Order"
chartType(3) = "3D Clustered Column"
chartType(4) = "3D Stacked Column"
chartType(5) = "Quit"
chartType(6) = "Select 1-4, 5 to Cancel"

cr = vbCr & vbCr
 msg = ""
For j = 1 To 6
  msg = msg & j & ". " & chartType(j) & cr
Next

ctype = "": typ = 0
Do While typ < 1 Or typ > 4
 ctype = InputBox(msg, "Select Chart Type")
 If Len(ctype) = 0 Then
    typ = 0
 Else
   typ = Val(ctype)
 End If
Loop

Select Case typ
    Case 5
        Exit Function
    Case 1,2
       lngType = xlColumnClustered
    Case 3
       lngType = xl3DColumnClustered
    Case 4
       lngType = xl3DColumnStacked
End Select

DoCmd.OpenReport ReportName, acViewDesign
Set Rpt = Reports(ReportName)

Set grphChart = Rpt(ChartObjectName)

grphChart.RowSourceType = "Table/Query"
recSource = grphChart.RowSource

If Len(recSource) = 0 Then
   MsgBox "RowSource value not set, aborted."
   Exit Function
End If

'get number of columns in chart table/Query
Set db = CurrentDb
Set rst = db.OpenRecordset(recSource)
colmCount = rst.Fields.Count
rst.Close

With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 0.2917 * twips
    .Top = 0.2708 * twips
    .Width = 5 * twips
    .Height = 4 * twips
End With

grphChart.Activate

'Chart type, Title, Legend, Datalabels,Data Table
With grphChart
     .chartType = lngType
     If typ = 3 Or typ = 4 Then 
' for 3D Charts only
       .RightAngleAxes = True
       .AutoScaling = True
     End If
    .HasLegend = True
    .HasTitle = True
    .ChartTitle.Font.Name = "Verdana"
    .ChartTitle.Font.Size = 14
    .ChartTitle.Text = chartType(typ) & " Chart."
    .HasDataTable = True
    .ApplyDataLabels xlDataLabelsShowValue
End With

'apply gradient color to Chart Series    
For j = 1 To grphChart.SeriesCollection.Count
      With grphChart.SeriesCollection(j)
        '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175))
        .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
        '.Barshape = xlCylinder 
' xlCylinder, xlConeToPoint, xlBox, xlPiramidtoMax
        If typ = 1 Then
            .Interior.Color = msoGradientVertical
        End If
        .DataLabels.Font.Size = 10
        .DataLabels.Font.Color = 3
        If typ = 1 Then
            .DataLabels.Orientation = xlUpward
        Else
            '.DataLabels.Orientation = xlHorizontal
            .DataLabels.Orientation = 45 'titlted angle in degrees
        End If
        End With
    Next

'Y-Axis /(Z-Axis for 3D)Title
With grphChart.Axes(xlValue)
   If typ = 2 Then
    .ReversePlotOrder = True
 'flips upside down
   Else
    .ReversePlotOrder = False
   End If
    .HasTitle = True
    .HasMajorGridlines = True
    With .AxisTitle
        .Caption = "Values in '000s"
        .Font.Name = "Verdana"
        .Font.Size = 12
        .Orientation = xlUpward
    End With
End With

'X-Axis Title
With grphChart.Axes(xlCategory)
    .HasTitle = True
    .HasMajorGridlines = True
    .MajorGridlines.Border.Color = RGB(0, 0, 255)
    .MajorGridlines.Border.LineStyle = xlDash
    With .AxisTitle
        .Caption = "Quarterly"
        .Font.Name = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
        .Orientation = xlHorizontal
    End With
End With

'Primary Y/Z Axis values label's font size
With grphChart.Axes(xlValue, xlPrimary)
     .TickLabels.Font.Size = 10
End With

'X-Axis category Labels (Qrtr1, Qrtr2...)
If grphChart.HasDataTable = False Then
    With grphChart.Axes(xlCategory)
        .TickLabels.Font.Size = 8
    End With
Else
    grphChart.DataTable.Font.Size = 9
End If

'Chart Area Border
With grphChart
    .ChartArea.Border.LineStyle = xlDash
    .PlotArea.Border.LineStyle = xlDot
    .Legend.Font.Size = 10
End With

'Chart Area Fill with Gradient Color
With grphChart.ChartArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 19
    .TwoColorGradient msoGradientHorizontal, 2
End With

'Plot Area fill with Gradient Color
With grphChart.PlotArea.Fill
    .Visible = True
    .ForeColor.SchemeColor = 2
    .BackColor.SchemeColor = 42
    .TwoColorGradient msoGradientHorizontal, 1
End With

grphChart.Deselect

DoCmd.Close acReport, ReportName, acSaveYes
DoCmd.OpenReport ReportName, acViewPreview

ColumnChart_Exit:
Exit Function

ColumnChart_Err:
MsgBox Err.Description, , "ColumnChart()"
Resume ColumnChart_Exit
End Function

Preparing for Demo Run.

Insert a new MS-Office Graph Chart Object on a new Report, change the basic property values as given in the earlier Article (Working with Graph Chart in VBA), and save the Report with the name myReport2. We can use the same Table that we have created for earlier examples.
NB: Always create a new Chart Object for a new set of examples rather than using the same Chart created earlier. Some property changes were found to be shown incorrectly when reused for different Chart Types.
The Code can be tested either by running from a Command Button Click Event Procedure or directly from the Debug Window (Immediate Window). Press Alt+F11 to open the VBA Window, press Ctrl+G for the Debug Window, type the following command, and press the Enter Key.
```
ColumnChart "myReport2", "Chart1"
```
A menu will appear with four Column Chart Options. Type 1 in the Text Box and press the Enter key to run the first option.
The Program Opens the Report myReport2 in Design View, modifies the Chart Properties, saves the Report with the Chart, and then re-opens it in Print Preview. A sample run image is given below.

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

Run the program a second time, and this time select Option number 2.

In this run, the ReversePlotOrder property is set to True to flip the Chart upside down. The sample image is given below.

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

Every time you run the Code, the Colors are selected in Random order from the Color Chart given in the earlier Post (Working with Graph Chart in VBA) and will display the Chart Bars differently.

Saving the Report with the Chart.

If you plan to save the Chart and don't like the Color combination currently displayed, run the option more than once till you get the color combination to your liking. If you want to save the current run of the Chart, make a copy of the Report or Export the Report into MS-Access Snapshot Viewer Format (MS-Access 2000 or later).

Chart Area and Plot Area are assigned a light Gradient Color, better when printed.

Sample Run of Option 3.

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

Sample Run of Option 4.

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

In Options 3 and 4 Runs, you can see that the Plot Area of the Chart is extended to display the Data Table and X-Axis Title. But, in the first two runs, these are appearing outside the Plot Area.

Saving PowerPoint Presentations.

You can copy and paste the finished Chart into PowerPoint Presentations. Before transporting the Chart to the PowerPoint page, copy and paste the Values from the Table into the Chart's Data Grid so that you can edit the values in PowerPoint itself if needed.

Double-click on the Chart Object in Report Design View to activate the Chart and to display the Data Grid.
Open the Table in Datasheet View, and click on the top left corner of the Table to highlight all the records.
Select Copy from the Edit Menu.
Right-click on the top-left corner cell of the Data Grid and select Paste from the displayed Shortcut Menu.

Download the Demo Database.

Download Demo Database

MS-Access Live Data in Excel.

Live MS-Access Data in an Excel Sheet

Step through the following procedure.

Refreshing Updates from Access Table.

Database Connection String Properties.

Open Recordset in Select Query, from External Access Database

A Different and Simple Method.

Source Connect Str for dBase and Excel.

Earlier Post Link References:

Opening an Excel Database directly.

Excel Table

Validation Settings.

Preparing for a Trial Run.

Earlier Post Link References:

Display Excel Value Directly on Form - MS Access 2003.

The Demo Run Preparation

Do the following if not successful.

Display Value in Combo Box and Option Group.

The Combo Box Method

The Option-Group Option.

Opening dBase Files Directly

Opening dBase Table

Exporting Employees Table as a dBase Table

VBA Code for Opening dBase Table

Earlier Post Link References:

Opening External Data Sources.

Opening a second Access Database

Displaying Workgroup Information File Name.

Databases in WorkSpace(0)

The VBA Code.

PIE Chart Object and VBA.

Sample run: an image of Option-1:

Sample run: an image of Option-2:

Sample run: an image of Option-3:

Run it from the Command Button Click.

Chart Formatting.

Download the Demo Database.

Column Chart VBA.

Sample Demo Run Code

The VBA Code.

Preparing for Demo Run.

Saving the Report with the Chart.

Saving PowerPoint Presentations.

Download the Demo Database.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Popular Posts

About Me

Blog Archive

Labels

Statcounter