Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Live Data in Excel-2

This is the continuation of the earlier Post Access Live Data in Excel. Please go through the earlier Article before continuing.

If you want to make changes to the Query that you have created for bringing Access Data into Excel you may do so.

  1. Click in a cell within the Data Area on the Worksheet.
  2. Point to Import External Data (Get External Data in MS-Office2000) in Data Menu.
  3. Select Edit Query from the displayed Menu. The Wizard will guide you through the earlier selections and you can modify them before saving the Query.

Alternatively you can open Microsoft Query Program (C:\Program Files\Microsoft Office\Office11\MSQRY32.EXE in MS-Access 2003) and open the saved Query (Refer earlier Post Access Live Data in Excel for Query File's default location) from File Menu, edit the SQL String and view the Output data in the Query Editor before saving the changes.

When you open the Query that you have created and saved earlier the Source Data will be displayed in Datasheet View. Click on the SQL labeled Toolbar Button or select SQL. . . from View menu.

You will find the SQL String like the sample given below:

SELECT Categories.CategoryID,
 Categories.CategoryName,
 Categories.Description,
 Categories.Picture
FROM `C:\Program Files\Microsoft Office\Office11\samples\Northwind`.Categories Categories

The normal SQL terminator character semicolon (;) is not present. The FROM Clause in the SQL is written differently without the use of an IN Clause, which we have seen in the MS-Access Query to Open Excel or Dbase Tables directly using Source Connect Str Property. The Categories Table Name is attached to the sample Database Path Name with a dot separator and the Table Name is repeated with a space in between. The .mdb file extension to Northwind is also omitted from the database file name specification.

If you Copy and paste the above SQL String into a MS-Access Query and change into Datasheet View it will display the data correctly. No errors will be displayed except some Column headings may appear as Expr3, Expr4 and so on, if you have changed the Query into Design View first and then switched into Datasheet View.

This is the time to learn the usage of two more Properties of MS-Access Query.

  1. Copy the above SQL String into a new MS-Access Query SQL window.
  2. Select View - -> Datasheet View to display Records from the Categories Table from Northwind.mdb database.
  3. Select View - -> Design View to change the Query into Design View.
  4. The Table Object is already visible on the Query Design surface, but the Field Names are absent. Click on the Title area of the Table to select it.
  5. Display the Property Sheet (View - -> Properties. The Alias and Source Properties of the Query are displayed.
  6. The Table Name Categories is loaded in the Alias Property and the Path Name of the NorthWind database is appearing in the Source Property without the .mdb extension.
  7. Change the Table Name appearing in the Alias Property into something different, say myCategories.
  8. Add .mdb extension at the end of Path name string in the Source Property.

The Table Name is now appearing as myCategories on the Title of the Table. If you change the Query into SQL View you can see that at the end of the SQL String the reference to the Table name .Categories Categories (appeared twice earlier) has now changed to .Categories As myCategories.

We must qualify each data field with the Table Name myCategories due to the Alias Name change. Change the SQL String as shows below qualifying each Field with the Alias Name. Enclose the Database Path Name in Square Brackets ([]) in place of single quotes.

SELECT [myCategories].[CategoryID],
 [myCategories].[CategoryName],
 [myCategories].Description,
 [myCategories].Picture
FROM [d:\Program Files\Microsoft Office\Office\samples\Northwind.mdb].Categories AS myCategories;

The manual change is necessary because we are referencing an external data source and no way MS-Access can guess the name. If we are using a Table from within the Database or a linked Table then the Alias Name change will automatically take effect on all the fields. You may try this experiment with one of your own Table from within the Database or with a linked Table.

Note: The Source Database and Source Connect Str Property Values are taken into the Query Syntax with an IN Clause to identify the external Application.

Alias Property is initially set with the Table Name and accepts changes to the Table Name through this Property.

Source Property accepts the external Database reference, either direct Path Name or an ODBC Connection String and the SQL Syntax is different in the FROM Clause of the Query definition.

Share:

MS-Access Live Data in Excel

We have already learned tricks to open and work with external data sources like Access Table, dBase Files and Excel Table without linking them permanently. We have tried VBA and Query based methods too.

We have yet to try the ODBC connection string in Source Connect Str Property in a Query to open external data files with this method. Even though we can link external data into MS-Access manually we will be trying the VBA based method for linking external files later.

But, before going into all that we will try a different trick, for a change in the one way traffic, so to speak, of data flow from external data sources into Access. But it is not exactly one way data flow into Access we can update external data from within Access, with or without direct links.

The Data Sources that we have opened after setting Source Database and Source Connect String Properties in Queries are updatable. The changes which you make on these data sets from within MS-Access will be updated back in their respective parent Applications.

Here, we will try linking live MS-Access Data in Excel and see that it stays live reflecting changes made in MS-Access. MS-Access will function as the Server Application and MS-Excel will be at the Client side.

There is only one difference, when compared with the earlier methods we tried, the changes that you make on Access data in Excel, which you are allowed to do, will not be updated back in Access.

Then what do we do with the Access Data in Excel? Well, you can create Charts or do calculations or whatever you like if you are more comfortable with Excel based tasks. You can create links to the data (Copy- -> Paste Special- ->Paste Link) from other Part of the Workbook and use the data for Charts or Reports, instead of making direct change on the linked source data. The changes that takes place in Access will automatically reflect on the Report or Chart in Excel.

For brining Access Data into Excel we need another Application, Microsoft Query, as a go between, to connect to Access Table. The Query Wizard will guide us through various options to link with Access, when we start with the Data options in Excel.

We will use the Categories Table from Northwind.mdb sample Database for our example.

  1. Open a new MS-Excel Workbook.
  2. Select Cell A1 on Sheet1.
  3. Point to Import External Data in Data Menu.
  4. Select New Database Query from the displayed Menu.

    Now, Microsoft Query Wizard Opens up and displays a Dialog Box. It displays the Database Sources in the Databases Tab that 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.

  5. Select MS-Access Database* from the list and click OK.
  6. The Common Dialog Control opens up allowing you to browse to the Location of 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 location address for Access 2000), select and click OK.
  7. 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 you can expand the Categories Table by clicking on the + symbol to display all the Fields and select only those you need and move it to the right side.

    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.

  8. After selecting the Fields Click Next. Here, you can define Filter conditions.
  9. Click Next to proceed to the Sort options.
  10. 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 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 SQL Window.

  11. See the Radio Button is set on Return Data in Microsoft Office Excel and click Finish.
  12. 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 target location in Step-2 above, this will appear as default location in the control, click OK without change.

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

It was a long journey from Access to Excel. Bringing Excel data into Access only need two property changes in MS-Access Query and you know now how simple it is.

Since, we are successful in bringing Access data into Excel let us do some experiments to prove that it is really live data from Access and how the changes made in Access reflects here.

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.

  1. Open the Categories Table of Northwind.mdb Database.
  2. Add Crabs, Lobsters in the Description field of the last Record. Or add a new record with some Category Name and Description.
  3. Minimize MS-Access and Display Excel Window and check whether the change has taken place immediately in the linked data in Excel. You may not find any change in Excel side. We have to refresh the data in Excel to reflect the changes.
  4. Click anywhere within the data Area.
  5. Select Refresh Data from Data Menu.

    Now the changes that you have made will be updated in the Excel side too. We can tell Excel to do this job automatically at a fixed interval rather than doing it manually.

  6. Right-Click somewhere in the middle of the Linked Table and select Data Range Properties from the displayed Shortcut Menu.
  7. On this Control you will find several options to manage the Data including the Query Name that transports the Data from Access to Excel. Select the Radio Button in the Refresh Every option under the Refresh Control Option Group and set the time 1 Minute, so that we can watch the refreshing action without waiting it for too long to happen.
  8. Open the Access window and remove the changes that we have made in the Categories Table, or make some more changes so that they are clearly visible in Excel when automatic refreshing action takes place.
  9. Open Excel window and wait for the Refresh Action to take palce. The change might have already taken place or it may happen any time because Excel will continue refreshing the change every minute.

If you have made any changes in the Excel side of the data then that will be lost in this process.

If you close the Excel Workbook and open it again a prompt will pop-up asking you to re-confirm whether Excel should automatically do the refreshing of linked data or not. You may respond the way you want it.

We will continue our discussion on few more points on this subject in the Next Post, instead of crowding everything in here.

Share:

Database Connection String Properties

Opening external data sources like dBase, Excel, Table from another MS-Access Database directly in VBA Code is only one of the options available to us. Those who are not comfortable with VBA have a better and much easier method available, without linking them permanently to MS-Access Database. In either case one thing is certain that we must know how to reference different external data sources correctly with Source Database Path and Connection String Values. But, these are very easy to learn.

We may face little difficulty in finding ODBC (Open Database Connectivity) Connection string Syntax, but this also we can find out by going through some shortcut tricks. Let us not mix the ODBC part now with our current example and we will deal with it later.

We have seen that we can display Values from Excel Cells or Range directly on MS-Access Form controls.

After going through the earlier examples for opening Access Table, dBase Table and Excel tables directly in VBA, I hope at least few readers thought of copying the SQL string presented in the VBA Code into a Query and try them out. If not, it is time to do that. It is a better option to understand them and to look into few things associated with their usage.

To start with, let us try opening an MS-Access Table from another Database directly in a Select Query.

  1. Open one of your Databases or create a new one.
  2. Select the Query Tab and Click the New Button on the Database Menu. Select Design View from the displayed Options and Click Close without selecting any Table from the list.
  3. Click on the SQL Toolbar Button or select SQL View from View Menu.
  4. Copy and Paste the following SQL String in the SQL Editing Window, 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-Access2003 then change the Path to . . .\Office11\Samples\. . .

  5. 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 an IN Clause is used for pointing to the correct database path and the entire Path Name is put in quotes and ends with a semi-colon indicating the end of SQL String, which is applicable for all Queries.

There is another way that you can do this. But, the first part you have to write in SQL window and the IN Clause part we can add separately in the Source Database and Source Connection Str properties of the Query so that you don't have to remember where to put the word IN or where to put the Opening/Closing quotes or worry about such syntax issues.

  1. Create a New Query and open the SQL editing window following Steps-1 to 3 explained above.
  2. Write the SQL string SELECT Employees.* FROM Employees;
  3. Select Design View from 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 is appearing on the Query Design without any Field Names showing in it.
  4. Select Properties from View Menu to display the Property Sheet of the Query.
  5. Click on an empty area of the Query surface, to the right of the Table Name, to display the Query level Property Sheet correctly.
  6. 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 external MS-Access Table.

  7. Enter the following Path Name in the Source Database Property over-writing the text (current).

    C:\Program Files\Microsoft Office\Office\samples\Northwind.mdb

    or with the change explained above for MS-Access2003 cases.

  8. Select Datasheet View from View Menu.

You will now see the same result as of 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.

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

Example: opening 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 Excel Table (Named Range) direct.

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

Share:

Opening Excel Database directly

In the World of Personal Computers many Applications came and disappeared. One of the very popular Applications was WordStar (Word Processor) used under Operating Systems CP/M (Control Program for Microcomputers) and DOS (Disk Operating System) till 1992. There were dedicated Word Processing Machines in those days using WordStar and competing Software WordPerfect. Microsoft Excel is also one of the very early days Applications, since 1985, and we all know how popular it is.

The first Worksheet Program Visicalc came out in 1979 with a bang and branched out into several names Supercalc, Multiplan, Lotus 1-2-3, Microsoft Excel and others putting more power into these clones than the original Worksheet Program Visicalc. (Source: Wikipedia)

Like Microsoft Access, Microsoft Excel also has very powerful Automation features that can make working in Excel more interesting. I have published two Articles in articlebase.com featuring two simple macros for Automation. The links are given at the bottom of this Post. If you would like to have a look at it, please do.

Spread Sheet Programs are designed for analysis that involves a chain of calculations with all the information spread across the Sheet visible to the user. A small change at the beginning of the calculation triggers a chain reaction across the cells, which have formula that depends on other cells, and the end result is instantaneous. It is an invaluable tool for What. . If. . . Analysis. When we think about Graph Charts the first name comes into our mind is Microsoft Excel.

With limited features of Database Functions also built into it with the power of Filter, Sort etc. This is where we step in to introduce a Table in Excel for our new example for Reading/Updating the Excel data from Access.

Even though there are facilities in Excel to implement general database rules for creating and maintaining a table it is often not followed. It is left to the user to decide how to create a worksheet and how to create a database when both can go into a single Worksheet side by side.

In Access, there are strict rules that we should follow, like should not enter text into Numeric Field, or cannot enter Text larger than the field size, Field Names must be unique and so on. All these rules are applicable in Excel also. But unlike in Access if we don't comply with any of these rules it may not give you an indication to correct it but it will not work as you have originally planned. All the Data Field Types available in Access are not present in Excel, like True/False in Access, but these constants are valid values in Excel Cells.

Before we quickly introduce a database in Excel and open it directly in Access as we did for dBase File let us look into an example of setting field Validations in Excel to restrict Data Entry in a Cell. You will be surprised to see how powerful it is.

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

  1. Open Excel and select a Cell in Sheet1.
  2. Select Data- -> Validation . . . - -> Settings.
  3. Select Whole Number in the Allow: Control.
  4. Enter 25 in the Minimum Control and 100 in the Maximum Control.
  5. Select the Input Message Tab. Enter Age in the Title Control and type Enter Value between 25 and 100 in the Input Message.
  6. Select Error Alert Tab and type Value Error in the Title Control.
  7. 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, 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 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 validation rule in a Cell to accept only Text Length, Less Than, 15 characters. Try entering 16 characters or more into that Cell.

When we plan for creating a database in Excel we can define short and meaningful Headings on the top row to stand for Field Names and set up Data Entry rules, following the procedure explained above, to each Field cells so that they will accept only valid values into them. You already have a Data Entry/Search Form in Excel like in Microsoft Access.

If you have a Data Table in Excel then click anywhere within this Table, select Form . . . from Data Menu. You will get a Data Entry/Search Form. Clicking the Criteria Command Button will turn it into a Search Form, in that you can enter Search Criteria into the Field, which you want to use to find your record. You can try this after we create a Table in Excel quickly for our VBA Program to open the Excel Table directly in MS-Access.

  1. Open Microsoft Excel (if you have closed it).
  2. Open the NorthWind.mdb sample database. Check the link Saving Data on Forms not in Table for location references, if you are not sure where this file can be found.
  3. Open the Categories Table in Datasheet View.
  4. Right-Click on the top left corner of the Datasheet View and select Copy from the shortcut Menu.
  5. Click on the Excel Icon on the Taskbar to open it and select Cell A1.
  6. Select Paste from Edit Menu.
  7. While the highlighting is still on the pasted Table, select Insert - ->Name - -> Define and type Categories in the Names in Workbook Control and Click OK to close it.
  8. Save the Workbook with the name: C:\My Documents\myData.xls and close Microsoft Excel and close Northwind.mdb sample Database.
  9. Open any one of your Databases or create a new one.
  10. 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 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 rstmsg = ""
    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
    
  11. Click in the middle of the Code and Press F5 to Run it. Displays the Product Category Names of 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 Confections and updated the Value Chocolates back into Excel Cell overwriting the word Confections.

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

  1. Microsoft Excel and Automation
  2. Microsoft Excel and Automation-2

Next: Database Connection String Properties

Share:

Display Excel Value Directly on Form

We have already seen how to open dBase Files directly and work with the data in VBA in the earlier Post Opening dBase File Directly. But, there are other alternatives (besides linking the files) to work with dBase Files in Access. We will explore this aspect in detail later. This Post was originally intended for a demo of opening Excel based Data Table directly and working with it in VBA Code. We will do that in the next Post. Before that we will look into some interesting and very easy trick with Excel.

Why I said very easy, because you don't have to struggle with VBA Code to do this. I know you will be happier if you don't have to work with my spaghetti VBA Code. So, I thought we will take a break and do something different and interesting before continuing with databases.

You got the clue from the title itself. That's right, you can display Values from Excel Worksheet directly on Microsoft Access Forms. We will go straight into a Demo.

  1. Open Microsoft Excel.
  2. Enter your name in Cell A1 on Sheet1 and minimize Excel, don't close it. You may save the Workbook with a name before minimizing it. The WorkBook may keep interrupting for saving it, if Auto-save feature is on.
  3. Open any one of your Microsoft Access Databases or create a new one.
  4. Open a New Form in Design View.
  5. Select the Text Box Tool from the Tool Box and draw a Text Box on the Detail Section of the Form.
  6. Click on the Text Box and display the Property Sheet (View - -> Properties).
  7. Write the following expression in the Control Source Property of the Text Box:

    =DDE("Excel","Sheet1","R1C1")

  8. Save the Form with a name of your choice.
  9. Open the Form in Normal View. You will see your Name written on Excel Cell is appearing in the Text Box on the Form.

    The DDE() Function stands for Dynamic Data Exchange. The first two Parameters are Excel Application and 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 A1 style.

    But, there are chances that you may end up with nothing showing on the Text Box. In that case you have to see a particular Option is correctly set in the General Tab of Options. . . in Excel Tools Menu.

  10. Click on the Excel icon on the Taskbar to open it. Click on Tools - -> Options. . . and select the General Tab.
  11. Remove the check mark (if it is set) from the option Ignore Other Applications under the Settings Options.
  12. Minimize Excel, Close MS-Access Form and open it again. Now you must see your name from the Excel Cell on the Form. Don't close the Form.
  13. Maximize Excel again and make some change in your Name and Minimize again. The change may not reflect immediately on the Form. You can update the Form value manually without closing and opening the Form again.
  14. While the Access Form is in Normal View, select OLE/DDE Links from Edit Menu.
  15. The Links Dialog Box will open up. Select DDE Links in the Links Group control. The Link's list will appear below. Select the Link and click on the Update Now Command Button. Now the change on the Worksheet will reflect on the Form Value.

There are Option settings in MS-Access also similar to the one we have made in Excel. The general rule is that instead of refreshing the change manually, as we did above, it should happen automatically at a fixed interval as per the Tools - - > Option. . . settings in Microsoft Access, but I didn't see it happen successfully on my machine so far.

Select Tools - -> Options. . . - -> Advanced Tab. Put a check mark in Enable DDE Refresh option, if it is not set. You can see the default Values set for automatic refreshing for Dynamic Data Exchange and ODBC links, how many retries and in case of failures how long Microsoft Access should wait before trying again etc.

You can display Values from Excel in two more Access Controls, in Combo Boxes and Option Group Control. See the sample image given below.

  1. Minimize Access and Maximize Excel.
  2. Enter few Names of people, books or anything else in Cells A9 to A17.
  3. Enter Value 2 in Cell C1 and minimize Excel Application Window.
  4. Maximize MS-Access and open the Form in Design View.
  5. Disable the Control Wizards Button (top right button, if it is active) on the Tool Box. Select the Combo Box Tool and draw a Combo Box Control on the form in such a way that it looks like a List Box. See the sample image given above.
  6. 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.

  7. 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 into the Text Box portion of the Combo Box and use it in a data field.

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

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

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

The selection of item on the Option Group will be based on the Value given in the Excel Cell C1. Now the value in Cell C1 is 2. The second item on the Option 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 Excel?"

If you respond with Yes then Excel Application will be started with Blank Sheets. You must open the Excel Workbook that provides information for DDE() Function manually to show up the values in Access Form.

Next: Opening Excel Database directly.

Share:

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions 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 msaccessprocess security advanced Access Security Array Custom Functions Data Macros Menus Property Report Top Values VBA msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy 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 Calculation ChDir Color Palette Conditional Formatting Controls Data Filtering Data Type Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Field Type Fields 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

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts