Opening Access Objects from Desktop

Opening Access Objects from the Desktop.

1. Set the Form Name in the Display Form Option of the current database in Access Options.

   BIG DEAL! This is the first trick any novice learns when he/she starts learning Microsoft Access.

2. Create an AutoExec macro with the FormOpen action and the required Form Name in the Property.

The above method opens the form when the database is launched.

Opening a Form Directly, without any Changes to the Database.

We would like to launch a specific Form tomorrow to continue updating data in that form, without making any changes in the database.

If you would like to print a particular Report, first thing in the morning without fail, then here is a simple trick.

Note: Your Database's Navigation Pane must be accessible.

1. Open the Database.

2. Click the Restore Window Control Button to reduce the Application Window size, so that the empty area of the Desktop is visible.

3. Click and hold the Mouse Button on the Form Name in the Navigation Pane, then drag the form to the desktop and drop it there.

4. Close the Database.

5. Double-Click on the Desktop-Shortcut. The Form will open when the Database is open.

   You can open the following Objects directly with Desktop-Shortcuts:

   • Report.

   • Table.

   • Select Query.

   • VBA Module.

   • Macro.

Try it out yourself.

1. MS-Access Class Module and VBA
2. MS-Access and Collection Object Basics
3. Dictionary Objects Basics
4. Withevents and All Form Control Types

Read More

Hyperlink Opens Objects from Another Database

Hyperlink Opens Objects from Another Database.

Hyperlinks in Microsoft Access provide a convenient way to open internal or external objects without the need to write Macros or VBA code. The Hyperlink Address and Hyperlink SubAddress properties are available for Label Controls, Command Buttons, and Image controls.

We have already explored a few examples of using hyperlinks, and links to those articles are provided at the end of this page for reference.

If you haven’t experimented with these properties yet, let’s walk through a demo to understand how both properties can be used effectively.

The Sample Trial Run.

1. Create a new Blank Form.
2. Insert a Label control on the Form.
3. While the label control is selected, display its Property Sheet (F4).

   Note: Hyperlink Address and Hyperlink SubAddress properties are used as follows:

   • Hyperlink Address: opens external objects, Word Documents, Excel Worksheets, PDF files, etc.
   • Hyperlink SubAddress: opens internal objects, Forms, Reports, Tables, Queries, and Macros.
   

   Let us open a Report from your database using the Hyperlink SubAddress property setting.

4. Find the Hyperlink SubAddress property and write the following text into it:

   Report <Your Report Name>

   Replace <Your Report Name> text with one of your own report names (without the < > brackets). The correct format for writing a value to this property is <Object Type>, i.e., Form, Report, Table, Query, or Macro, followed by a space, followed by the actual object name.

5. Change the Caption Property value of the Label to Open Report.
6. Save the Form.
7. Open the Form in Normal view, and click on the label control.

You will see how your report is open in Print Preview.

Open an Excel or Word File.

Now, try opening an MS Word Document using the other property, the Hyperlink Address setting.

1. Open your Form and create a second Label control on it.

2. Display the property sheet of the label control.

3. Change the Caption property value to Open Word Doc.

4. Set the Hyperlink Address property value to the full pathname of a Word Document on your computer, like C:\My Documents\Letter.doc 

5. Save the Form with the changes.

6. Open the Form in Normal View, and click on the second label to open the Document in MS Word.

Note: You can open Web pages by setting the Hyperlink Address property value to a web address, say https://www.msaccesstips.com

The Hyperlink Base.

If your external files, which you plan to open in Access, are all in one location, C:\My Documents\ then you don’t need to duplicate it in every control, but to specify the Path (C:\My Documents\) at one place: Hyperlink Base and only needs to write the document name (like Letter.doc or any other file on that location) in the Hyperlink Address property.

Right-click to open the large image in New Tab/Window.

Let us try that:

1. Click the Office Button and highlight the Manage option to display Database Options (Access 2007). In Access 2003 and earlier versions, you will find this option in the Tools Menu.

   

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

2. Select Database Properties.

3. Select the Summary Tab.

4. Type C:\My Documents\ in the Hyperlink Base control (see the image above),  and click OK to save it.

5. Open your Form and remove the C:\My Documents\ text entered for an earlier example, leaving the Letter.doc file name intact.

6. Save the Form and click the label control to open the Word Document.

7. You may try to open any other document you have in that location, with only the file name changed in the label.

Opening Objects from another Microsoft Access Database.

If you have followed me so far with the above sample exercises, then with a few changes, we can do it.

1. First, remove the text from the Hyperlink Base control ('C:\My Documents\') and leave the control empty.

2. Create another Label control on your Form.

3. Change the Caption Property value to Ext. Database.

4. Set the Hyperlink Address Property to your external database path, like C:\mdbs\myDatabase.accdb.

5. Set the Hyperlink SubAddress property value to Report myReport. Change the report name to match yours.

6. Save the Form with the change.

7. Open the Form in Normal View, and click on the label control.

Your external database will open first and will show your report in print preview.

Note: If your database is secured, then it will prompt for User ID and Password. You may try opening other objects: Form, Query, Macro & Table.

• Open Forms with Hyperlinks in Listbox
• External Files List in Hyperlinks
• Editing Hyperlink Address Manually

Read More

Printing Ms-Access Report from Excel

Printing MS Access Report from Excel.

Printing an MS Access Report from Excel or from Another Database

In Microsoft Access applications, we often use a Front-End/Back-End design. All the tables are maintained in the Back-End database and linked to the Front-End. We link them because the Front-End frequently needs to retrieve or update data from those tables.

Once linked, external database tables behave just like native tables in the Front-End. You can work with them seamlessly, without noticing any difference.

Note: When linking tables from a network location, always use the full UNC path of the database file, for example:

\\ServerName\FolderName\SubfolderName\DatabaseName.mdb

Instead of a server-mapped drive path, such as:

T:\FolderName\SubfolderName\DatabaseName.mdb

This is important because if the mapped drive letter (e.g., T:\) changes later to K:\, Z:\, or another letter, the links will break. Using the full network address ensures the linked tables remain intact regardless of drive mappings.

Updating a Table not Linked with the Current Database.

When you need to retrieve (or add/update) information in a table that is not linked to the front-end database, you need a VBA program to do that.

A sample procedure is shown below:

Public Function CategoryTable()
Dim wsp As Workspace, db As Database
Dim rst As Recordset

'Set a reference to the active Workspace
Set wsp = DBEngine.Workspaces(0)

'DBEngine.Workspaces(0).Databases(0) is CurrentDB 
'Open a second database in Workspace(0).Databases(1) position 
Set db = wsp.OpenDatabase("C:\mdbs\NWSample.mdb")
 
'Open Categories recordset from Database(1) 
Set rst = db.OpenRecordset("Categories")
 
'Display the CategoryName field value 
MsgBox rst![CategoryName]
 
rst.Close 
db.Close 

'remove objects and release memory
Set rst = Nothing 
Set doc = Nothing 
Set db = Nothing 
Set wsp = Nothing 
End Function

The Databases Workspace.

Working with Databases in the DBEngine Workspaces Collection

When an Access database is opened in the Application Window, it is actually opened within a Workspace in the Workspaces collection, under the Application.DBEngine object.

The default Workspace is Workspace(0). 

The first open database inside that workspace is addressable as Workspaces(0).Databases(0).

You can open more than one database within the same workspace and work with its tables or query recordsets. This approach is often better than permanently linking those tables to the Front-End database, especially if you do not need to use them on a day-to-day basis.

However, there is an important limitation: you cannot open Forms or Reports from databases opened this way.

In fact, the object reference:

Application.DBEngine.Workspaces(0).Databases(0)

is equivalent to the CurrentDb object. While several databases can be loaded into Workspaces(0), only the current database will be visible in the Access Application Window. Other databases, if opened, will remain in memory until you explicitly close them.

This means you can:

Read and update tables from an external database without linking them together.

Create new tables or queries in those external databases as needed.

But you cannot:

Open forms or reports stored in those databases through this method.

Creating Queries on a non-linked External Table.

What if we want to create a query using data from an external table that is not linked to the front-end database? Surprisingly, Microsoft Access allows you to create queries without permanently linking external tables to the current database. Curious about how this works? You can learn the trick [here].

Up to this point, our discussion has focused on working with external tables and queries. However, the methods we covered so far will not allow you to open a form or report from another database. Normally, this requires opening the database in a separate Access Application Window.

That said, this statement is not entirely accurate—we’ll explore why in next week’s session.

But before diving into the Excel-based procedure, let’s first see how the same task can be handled directly in the active database.

The simple procedure steps are given below:

1. Create a separate Access Application Object.

2. Set its Visible property to Yes so we can see the Application Window.

3. Open the required Access Database within that Application Window.

4. Open the required Report in Print mode (acViewNormal) to send it to the default printer, or in Print-Preview mode (acViewPreview) to view the report before sending it to the printer manually.

5. Close the Database first and Quit the Application.

The Sample VBA Code is given below:

' Include the following in Declarations section of module.
Dim appAccess As Access.Application

Public Function PrintReport()
'---------------------------------------------------------
'Original Code Source: Microsoft Access Help Document
'---------------------------------------------------------

    Dim strDB As String

' Initialize string to database path.
    Const strConPathToSamples = "C:\Program Files\Microsoft Office\Office11\Samples\"
    strDB = strConPathToSamples & "Northwind.mdb"

' Create new instance of Microsoft Access Application.
    Set appAccess = CreateObject("Access.Application")
' Make Application Window Visible
    appAccess.Visible = True

' Open database in Microsoft Access window.
    appAccess.OpenCurrentDatabase strDB

' Open Catalog Report in Print Preview
    appAccess.DoCmd.OpenReport "Catalog", acViewPreview
    
' Enable next line of code to Print the Report
    'appAccess.DoCmd.OpenReport "Catalog", acNormal

    'appAccess.DoCmd.Close acReport, "Catalog", acSaveNo
    'appAccess.CloseCurrentDatabase
    'appAccess.Quit
    
End Function

1. Copy and paste the code into a new Standard Module of your Database.

2. Make changes to the Path of the Database and Report name, if needed.

3. Click in the Code and press F5 to run the Program.

In Microsoft Excel.

If you were able to run the code successfully and Print/preview your Report in a separate Access Application Window, then you may proceed to do the same thing from Microsoft Excel.

1. Open Microsoft Excel.

2. Display the VBA Window (Developer ->Visual Basic).

3. Insert a Standard Module (Insert -> Module) in the VBA Window.

4. Copy and paste the Code into the Module and save it.

Before running the code, you must add the Microsoft Access 12.0 Object Library to the Excel Project.

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

1. Select the References option from the Tools Menu.

2. Find Microsoft Access 12.0 Object Library (or the available version on your machine) and select it.

3. Click the OK Command Button to close the Control.

4. Click in the Code and press F5 to run.

You will see the same result you saw when you ran the Code in Microsoft Access.

1. Roundup Function of Excel in MS-Access
2. Proper Function of Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel 2
11. Opening an Excel Database Directly
12. Create an Excel Word File from Access

Read More

Copy Paste Data from Excel to Access2007

Copy-paste data from Excel to Access2007.

Microsoft Office Applications already provide built-in methods to transfer data between them. This can be implemented by importing or exporting data, directly linking to the source while keeping the original data intact in the parent application, or by simply copying data to the clipboard and pasting it into another program. These options have long been available.

In earlier versions of Access, you first needed to create a table with matching field types before pasting or appending data. Access 2007 has made this process much simpler. For example, you no longer need to create a table beforehand when pasting data from Excel. Instead, Access 2007 prompts you to confirm whether the copied data includes header rows. If you select Yes, Access automatically creates a new table (using the worksheet name) and pastes the data into it, assigning the correct field types.

Let us find out how.

1. Open Microsoft Excel and create a small database with the sample data given below:

   

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

2. Open Microsoft Access 2007 and open an existing .accdb database or create a new one.

3. Make the Excel database window active.

4. Highlight the Excel database range, including the header row.

5. Select Copy from the Home Menu, to transfer the data into the Clipboard.

6. Make the Access 2007 database window active.

7. Right-click on the Navigation Pane of Tables and select Paste from the shortcut menu.  The following message box is displayed:

   

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

8. If you have included the header line, then you may click on the Yes Command Button; otherwise, select No.

A new Table will be created with the Worksheet name.  The header cell values will be used as field names.  The field data type (Text, Date, Number, etc.) will be correctly imported depending on the data type copied from Excel.

If you have selected No, then the data will still be pasted into a new table, but the field names will be F1, F2, F3, etc.

Technorati Tags: Microsoft

1. Roundup Excel Function in MS-Access
2. Proper Excel Function in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word Files from Access

Read More

Attachment Field in Access2007

Attachment Field in Access 2007.

Working with images or animations in applications like Microsoft Access has always been enjoyable. I have used the Office Assistant for message boxes in all my Access applications, particularly when creating and deploying common VBA library programs across the network.

Not everyone may know that it is possible to display custom images in the Office Assistant control. I have leveraged this feature to display custom greetings to Access users on special occasions such as Christmas, Eid, and Onam simply by replacing the standard company logo image on the server. I was extremely disappointed when Microsoft removed this feature in Office 2007.

For those still using Microsoft Access 2003 or earlier, the following links provide tips and tricks for using the Office Assistant with MsgBox:

• MsgBox with Office Assistant
• MsgBox with Options Menu
• Office Assistant with Check-Box Menu
• Selection of Office Assistant
• Color and Picture in MsgBox
• Office Assistant and MsgBox Menus
• Office Assistant and MsgBox Menus-2
• Office Assistant and MsgBox Menus-3

The Attachment field type in Access 2007 has significant flexibility, allowing multiple external documents or images to be stored in a single record without inflating the database size. This overcomes the limitations of the older Object Linking and Embedding (OLE) method, which was commonly used in earlier MS Access Versions for storing, editing, or displaying images. While a Hyperlink field can link to only one external file or a single internal object (such as a form or report), the Attachment field supports multiple items per record.

This feature is useful for storing and retrieving essential documents related to a record, such as project site plans, diagrams, contract agreements, engineering drawings, or employees’ family photos. Each attached document or image can be edited in its native application, preserving full functionality.

A Sample Demo.

1. Open Microsoft Access 2007.

2. If you have already created the Northwind 2007 sample database, open it; otherwise, select Local Templates from the Template Categories.

3. Click on the Northwind 2007 Template to select it.

4. Click the Folder icon on the right side of the File Name control, select the required folder, and save Northwind 2007.accdb Database.

5. Open Northwind 2007.accdb Database,

6. Close the Home Form.

7. Select Object Type from the drop-down list in the Navigation Pane and select Tables.

8. Right-click on the Employees table and select Design View from the Shortcut Menu.

9. Use the right scroll bar to move the field list up and bring the last field, Attachments (field type: Attachment), into view.

10. Now that we have seen the Attachment Field in the Employees Table (or you can create a new Table with the Attachment Field if you prefer), close the Design View.

11. Open the Employees Table in Datasheet View.

12. Move the horizontal scroll bar and bring the attachment field into view. See the sample image shown below:

    

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

13. The second column (highlighted) is the attachment field where a paper clip image and a number in brackets (zero) display how many attachments are in each record.

14. There are no attachments in any of those records so far, so we will add one; double-click the attachment field in the first record.

15. The Attachment control opens up. Click on the Add… Command Button to browse for files on the hard disk. You may select a Word Document, Excel File, PDF file, or Image.

16. Repeat this action to attach more files to the same field.

17. Click OK to close the dialog box.  You will now see a number appearing in brackets, indicating how many attachments are in that field of the record.

18. Double-click the attachment field to open and show the attached files.

19. Click on one of the files to select it.

20. If you click on the Remove Command Button, you can remove the selected attachment, or click Open to open the document in its parent/preview Application.

21. If you right-click the attachment field, the Manage Attachment shortcut menu is displayed.  Selecting this option will open the earlier dialog box we have seen for attaching /removing/opening external files.

Technorati Tags: Microsoft

• Learn Binary Numbering System-2
• Learn the Binary Numbering System
• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type

Read More

External Files List in Hyperlinks

External Files List in Hyperlinks.

We have seen that we can open and work with external data sources, dBase Tables, Excel Databases, and AS400 (iSeries) tables directly without linking them permanently to MS Access, as demonstrated in earlier articles.

In this tutorial, we will explore a different kind of external access — working with files on your disk, regardless of their type (Word documents, Excel workbooks, PDFs, images, etc.), and displaying them as clickable hyperlinks in an Access Form that opens in their respective default applications.

To achieve this, we will:

1. Browse and select files from Disk using the Common Dialog Control (the File Browser).

2. Store the file paths in a Table for easy retrieval.

3. Display the list of files as hyperlinks on a Form.

4. Open each file in its associated program (Word, Excel, Adobe Reader, etc.) simply by clicking the hyperlink.

This approach is useful in scenarios such as:

• Managing project-related documents from within Access.

• Providing quick access to scanned images or PDFs attached to records.

• Creating document libraries, training materials, or archives where Access acts as a front-end to organize and launch files efficiently.

Before diving into the implementation, let us first understand how the Common Dialog Control (File Picker) works and how we can use it to browse and select files dynamically.

• Opening External Data Sources
• Opening dBase Files Directly
• Display Excel Value Directly on Form
• Opening an Excel Database Directly
• Database Connection String Properties
• Access Live Data in Excel
• Access Live Data in Excel-2
• Source ConnectStr Property and ODBC

But all of them fall into only one category: data files.

Designing the Files List Form.

To answer the above queries, we will create a Form with a Datasheet Sub-Form and with a few simple controls to take a listing of all frequently used files of your choice (Text Files, Word Files, Excel Files, or  Files of all Types) from the Disk and display them in a list of Hyperlinks. When you like to open and work with a file in its parent Application, simply click on the hyperlink to select and open the file.  An Image of the sample Form is given below:



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

Finding Files in Folders

Click the Create File Links button to open the File Browser (the Common Dialog Control). Browse to locate your desired files; you may select one or more, and click OK to bring their references into the List Control as clickable Hyperlinks.

The File Path Name for each file is displayed in the next control to the right of the hyperlink. This makes it easy to identify the location of frequently used files such as Excel workbooks, Word documents, PDFs, or any other file type you’ve linked.

This entire process takes only a few mouse clicks, something you’re likely to do many times throughout the day. You can also import files in batches, and each selection will be added automatically to the directory-list table, building your library of linked documents.

The Data Sheet Form Design.

The Form has a simple design, as shown in the image above. The following are the main elements of the design.

1. A Table: DirectoryList with two Fields: 1. FileLinks with data type Hyperlink. 2. Path with a Text data type to store the file’s complete path name.

2. The Datasheet Form was created in the Table named FilesListing_Sub.

3. The Main Form Files Listing with the Datasheet Form inserted as a Sub-Form that occupies the major part of the design.

4. A Command Button (Name: cmdFileDialog) with the Caption Create File Links runs the Common Dialog Control to browse and select files from the Disk and insert them as Hyperlinks in the FileLinks Field of the Table.

5. The Field Path will be updated with the location address of the selected files.

6. The Unbound Text Box below will show the Current Project Path as the default location when the File Dialog is open.

7. The Command Button named cmdDelAll, and the Caption Delete All Links,  clicks to delete all file links from the tables.

8. The Command Button with the name cmdDelLink and the Caption Delete One Link clicks to delete the selected hyperlink item from the List.  You can manually delete one or more Links, select them by holding the Shift key down and clicking on the left border of items next to each other, and press the DELETE Key.

9. Command Button with the Caption Delete File on Disk and the name cmdDelFile deletes the selected Link, followed by physically deleting the file on disk.  So be careful with this option. Only one File can be deleted at a time.  Use this option with caution; once the file is deleted from the disk, it cannot be reversed.  Click on the left border of a link to select it, and click on Delete File on Disk.
   

Managing the Files List

The Files List is created as Hyperlinks in the target table named DirectoryList. Each record in this table consists of the file’s display name and its corresponding full path stored as a valid hyperlink reference.

New links can be added to the list at any time, and the incoming links are automatically appended to the existing records without overwriting previous entries. Manual data entry or direct editing of hyperlinks through the Form is not allowed, ensuring that the hyperlink structure remains intact.

If you wish to make experimental changes, open the DirectoryList table directly in Datasheet View and modify it there. However, this is not recommended, since any accidental changes to the Hyperlink Value Segments (the visible text, the actual address, or the optional sub-address) may break the link or cause it to open incorrectly.

If you would like to know more about the Hyperlink Value Segments (four segments) and what they do, go to the link Open Forms with Hyperlinks in ListBox.

Download the sample database from the bottom of this page and try it out before you design one of your own to understand how it works.

You can easily implement this in your various Projects by simply importing the Forms and the Table into your Projects if required. The demo database is an Access 2007 Version file.

The Main Form Class Module Code

The VBA Code, which runs behind the Main Form Files Listing, is listed below for info.

Option Compare Database
Option Explicit
Dim strpath As String

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdDelFile_Click()
On Error GoTo cmdDelFile_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("File: " & strFile & vbCr & "DELETE from Disk?", _
vbQuestion + vbYesNo, "cmdDelFile_Click") = vbYes Then
   If MsgBox("Are you sure you want to Delete" & vbCr _
   & rst!Path & " File from DISK?", vbCritical + vbYesNo, "cmdDelFile_Click()") = vbNo Then
    GoTo cmdDelFile_Click_Exit
   End If
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    If Len(Dir(strFile)) > 0 Then
    Kill strFile
    MsgBox "File: " & strFile & " Deleted."
    Else
      MsgBox "File: " & strFile & vbCr & "Not Found on Disk!"
    End If
End If
Else
    MsgBox "File: " & strFile & " Not Found!!"
End If

cmdDelFile_Click_Exit:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

cmdDelFile_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelFile_Click()"
Resume cmdDelFile_Click_Exit
End Sub

Private Sub cmdHelp_Click()
DoCmd.OpenForm "Help", acNormal
End Sub

Private Sub Form_Load()
'strpath = CurrentProject.Path & "\*.*"
On Error GoTo Form_Load_Err
GetProperty
strpath = Me!PathName

Form_Load_Exit:
Exit Sub

Form_Load_Err:
MsgBox Err & " : " & Err.Description, , "Form_Load()"
Resume Form_Load_Exit
End Sub

Private Sub cmdDelLink_Click()
On Error GoTo cmdDelLink_Click_Err
Dim db As DAO.Database, rst As DAO.Recordset
Dim strFile As String

strFile = Me.DirectoryList.Form!Path
Set db = CurrentDb
Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
rst.FindFirst "Path = '" & strFile & "'"
If Not rst.NoMatch Then
If MsgBox("Link: " & strFile & vbCr & "DELETE from above List?", _
vbQuestion + vbYesNo, "cmddelLink_Click()") = vbYes Then
    rst.Delete
    rst.Requery
    Me.DirectoryList.Form.Requery
    MsgBox "File Link: " & strFile & " Deleted."
End If
Else
    MsgBox "Link: " & strFile & " Not Found!!"
End If
rst.Close
Set rst = Nothing
Set db = Nothing

cmdDelLink_Click_Exit:
Exit Sub

cmdDelLink_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdDelLink_Click()"
Resume cmdDelLink_Click_Exit

End Sub

Private Sub cmdFileDialog_Click()
On Error GoTo cmdFileDialog_Click_Err
'Requires reference to Microsoft Office 12.0 Object Library.
Dim db As DAO.Database, rst As DAO.Recordset
   Dim fDialog As Office.FileDialog
   Dim varFile As Variant
Dim strfiles As String
   'Clear listbox contents.
   'Me.FileList.RowSource = ""

   'Set up the File Dialog.
   Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
   With fDialog
      'Allow user to make multiple selections in dialog box.
      .AllowMultiSelect = True
      .InitialFileName = strpath
            
      'Set the title of the dialog box.
      .Title = "Please select one or more files"

      'Clear out the current filters, and add our own.
      .Filters.Clear
      .Filters.Add "All Files", "*.*"
      .Filters.Add "Access Databases", "*.mdb; *.accdb"
      .Filters.Add "Access Projects", "*.adp"
      .Filters.Add "Excel WorkBooks", "*.xlsx; *.xls; *.xml"
      .Filters.Add "Word Documents", "*.docx; *.doc"

      'Show the dialog box. If the .Show method returns True, the
      'user picked at least one file. If the .Show method returns
      'False, the user clicked Cancel.
      If .Show = True Then
    'i = .FoundFiles.Count
    'MsgBox "File found = " & .FoundFiles.Count
    'DoCmd.SetWarnings False
    'DoCmd.RunSQL "DELETE DirectoryList.* FROM DirectoryList;"
    'DoCmd.SetWarnings True
    Set db = CurrentDb
    Set rst = db.OpenRecordset("DirectoryList", dbOpenDynaset)
    'For i = 1 To .FoundFiles.Count
        For Each varFile In .SelectedItems
        rst.AddNew
        strfiles = Mid(varFile, InStrRev(varFile, "\") + 1)
        strfiles = strfiles & "#" & varFile & "##Click"
        rst![FileLinks] = strfiles
        rst![Path] = varFile
        rst.Update
    Next
Me.DirectoryList.Form.Requery
         'Loop through each file selected and add it to the list box.
         'For Each varFile In .SelectedItems
            'Me.FileList.AddItem varFile
         'Next
      Else
         MsgBox "You clicked Cancel in the file dialog box."
      End If
   End With

cmdFileDialog_Click_Exit:
Exit Sub

cmdFileDialog_Click_Err:
MsgBox Err & " : " & Err.Description, , "cmdFileDialog_Click()"
Resume cmdFileDialog_Click_Exit
End Sub

Private Sub Form_Unload(Cancel As Integer)
If Len(strpath) = 0 Then
  strpath = "C:\My Documents\*.*"
End If
SetProperty
End Sub

Private Sub PathName_AfterUpdate()
'On Error GoTo PathName_AfterUpdate_Err
Dim str_path As String, i As Long
Dim test As String

    Me.Refresh
    str_path = Me!PathName
    i = InStrRev(str_path, "\")
    str_path = Left(str_path, i) & "*.*"
    strpath = str_path
    
    test = Dir(strpath)
    If Len(test) = 0 Then
        MsgBox "Invalid PathName: " & strpath
        strpath = CurrentProject.Path & "\*.*"
        Me.PathName = str_path
        Me.Refresh
        Exit Sub
    End If
    Me.PathName = strpath
    Me.Refresh
    
PathName_AfterUpdate_Exit:
Exit Sub

PathName_AfterUpdate_Err:
MsgBox Err & " : " & Err.Description, , "PathName_AfterUpdate()"
Resume PathName_AfterUpdate_Exit
End Sub

Private Function GetProperty() As String
On Error GoTo GetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = doc.Properties("defaultpath").Value
If Len(strLoc) = 0 Then
   strLoc = CurrentProject.Path & "\*.*"
End If

strpath = strLoc
Me!PathName = strpath
Me.Refresh

GetProperty_Exit:
Exit Function

GetProperty_Err:
MsgBox Err & " : " & Err.Description, , "GetProperty()"
Resume GetProperty_Exit
End Function


Private Function SetProperty() As String
On Error GoTo SetProperty_Err
Dim doc As DAO.Document
Dim db As DAO.Database
Dim prp As DAO.Property
Dim strLoc As String

Set db = CurrentDb
Set doc = db.Containers("Forms").Documents("FilesListing")
strLoc = Me!PathName
If Len(strLoc) = 0 Then
    strLoc = CurrentProject.Path & "\*.*"
End If
doc.Properties("defaultpath").Value = strLoc

SetProperty_Exit:
Exit Function

SetProperty_Err:
MsgBox Err & " : " & Err.Description, , "SetProperty()"
Resume SetProperty_Exit
End Function

Download Demo Database.

Download Demo DirListing2K1.zip

• Event Trapping & Summary on Datasheet
• Sum() Min() Max() ParamArray
• Text Box and Label Inner Margins
• Multiple Parameters for Query
• Form Menu Bars and Toolbars

Read More

Lost Links of External Tables

Lost Links of External Tables.

We have already learned several methods to work with external data sources. Linking them to an MS Access database or directly opening them in Queries by setting Source Database and SourceConnectStr Properties. In either case, the Source Data must be present in its original location at all times.

However, there is always a possibility that the links to some of these tables may be lost—for instance, if a source table is accidentally deleted or renamed. Such issues typically go unnoticed until we attempt to work with the linked tables, often resulting in errors, which may appear unexpectedly.

To alleviate this problem, run a check on the linked tables as soon as the Database is open for normal operations. If any of the linked Tables are not in place, then warn the User about it and shut down the Application if it has serious implications.

How do we determine whether a linked external table has lost its connection with the Database or not? It is easy to attempt to open the linked table, and if it is in error, you can be sure the table link is missing. 

There may be several tables in a database, local tables or linked ones. How can we single out the linked ones alone and open them to check the status? Again, this is not a serious issue, and you already have the answer if you have gone through the earlier Articles explaining several methods of accessing external data and the usage of Connection Properties of Linked Tables and Queries.

The Connection Property Value.

We need a small VBA routine to iterate through the Table definitions and check the Connect Property value, and if it is set with a Connect String, then it is a linked table; otherwise, it is a local table. When we encounter a linked table, we will attempt to open it to read data. If this process triggers an Error, then we will prepare a list of such cases and display it at the end to inform the User so that she can initiate appropriate remedial action to rectify the error.

A sample VBA routine is given below. Copy and paste the program into a Global Module and save it.

Public Function LostLinks()
'----------------------------------------------------
'Author : a.p.r. pillai
'URL    : www.msaccesstips.com
'Date   : 21/09/2008
'----------------------------------------------------
Dim msg As String, tbldef As TableDef
Dim strConnect As String, cdb As Database
Dim rst As Recordset, strTableName As String
Dim strDatabase As String, loc As Integer
Dim loc2 As Integer

On Error Resume Next

Set cdb = CurrentDb
For Each tbldef In cdb.TableDefs
    strConnect = tbldef.Connect

    If Len(strConnect) > 0 Then
       strTableName = tbldef.NAME
       Set rst = cdb.OpenRecordset(strTableName, dbOpenDynaset)
       If Err > 0 Then
          If Len(msg) = 0 Then
             msg = "The following Linked Tables are missing:" & vbCr & vbCr
          End If
          msg = msg & strTableName & vbCr
          Err.Clear
        End If
        rst.Close
    End If
Next

If Len(msg) > 0 Then
    MsgBox msg, , "LostLinks()"
End If

End Function

Call the Routine from an Autoexec Macro or from the Form_Load() Event Procedure of the Application's Startup or Main Screen.

Earlier Post Link References:

• PIE Chart Object and VBA
• Column Chart and VBA
• Working with the Chart Object in VBA
• Linking IBM AS400 Tables
• Repairing the Compacting Database with VBA.

Read More

Link External Tables with VBA

Link External Tables with VBA.

We all know how to link to a table from external data sources manually.

1. Highlight Get External Data from the File Menu.

2. Select Link Tables from the displayed options.

3. Select the file type: dBase, Excel, etc., in the Files of Type control.

4. Track the location of the file and select it.

5. Click the link to attach the selected table to the Current Database.

If you are linking an external table from a Network Location, use the UNC (Universal Naming Conventions) type location reference (like \\hosfs03\accounts\myDatabase\...), rather than using a mapped drive location reference like H:\MyDatabase. 

You can even use your Local Drive's share name in this manner. 

\\yourPCName\C$\Databases\myDatabase.mdb

This method ensures that even if the drive mapping changes—for example, from H:\ to K:\ or any other letter—MS Access can still locate the linked tables without issues. Otherwise, you would need to manually update the table locations using Tools → Database Utilities → Linked Table Manager to refresh the changed path references.

We have already seen that we can work with external tables without linking them permanently to the current database.

Here, we will link external Tables to the Current Database using VBA. After linking the table, we will print the contents of five records into the Debug Window and delete the link.

The Steps to follow.

We will go through the following steps to link a Table to a Database with VBA:

1. Create a temporary Table Definition (Tabledef) without any Field Definitions in the Current Database.

2. Load the Connect Property of tabledef. with a connection string value

3. Link the external Table to the temporary Table definition (Tabledef)

4. Add the temporary Table definition to the Tabledefs Group.

5. Rename the temporary Table to match the Source Table Name.

The VBA Functions.

We will write two VBA Functions for our examples. Copy and paste the following VBA code into a Global Module of your MS Access Database and save it:

Public Function LinkMain()
Dim strConnection As String
Dim sourceTable As String

strConnection = ";DATABASE=D:\Program Files\Microsoft office\Office\Samples\Northwind.mdb;TABLE=Orders"

sourceTable = "Orders" 'Access Table Name

LinkExternal strConnection, sourceTable

End Function

Function LinkExternal(ByVal conString As String, sourceTable As String)
Dim db As Database, i As Integer, j As Integer
Dim linktbldef As TableDef, rst As Recordset

Set db = CurrentDb
Set linktbldef = db.CreateTableDef("tmptable") 'create temporary table definition

linktbldef.Connect = conString 'set the connection string
linktbldef.SourceTableName = sourceTable 'attach the source table
db.TableDefs.Append linktbldef 'add the table definition to the group
db.TableDefs.Refresh 'refresh the tabledefinitions

linktbldef.NAME = sourceTable 'rename the tmptable to original source table name

'open the recordset and print 5 records in the debug window
Set rst = db.OpenRecordset(sourceTable, dbOpenDynaset)
i = 0
Do While i < 5 And Not rst.EOF
  For j = 0 To rst.Fields.Count - 1
     Debug.Print rst.Fields(j).Value,
  Next: Debug.Print
  rst.MoveNext
  i = i + 1
Loop
rst.Close

db.TableDefs.Delete sourceTable 'remove to stay the table linked
db.Close
Set rst = Nothing
Set linktbldef = Nothing
Set db = Nothing

End Function

How it works.

The first program, LinkMain(), calls the LinkExternal() Subroutine with strConnection and SourceTable name as parameters. Northwind.mdb sample database and Orders Table are passed as parameters. Open the Debug Window (Immediate Window) by pressing Ctrl+G. Click anywhere within the LinkMain() program and press F5 to run the code and print five records of the Orders table from the Northwind.mdb database.

The LinkExternal() Program performs the five steps of action explained above.

Replace the strConnection and sourceTable with the following sample values for opening a dBase Table:

strConnection = "dBase IV;HDR=NO;IMEX=2;DATABASE=D:\msaccesstips" sourceTable = "Branches" 'Access Table Name

Tip: If you don't have a dBase Table to try the Code, then export a Table from MS Access into the dBase format and run the Code with changes.

Change the Database Folder name and the Table name with your own dBase Folder and Table names.

For Excel-based Tables, two methods are given below.

1. Use Worksheet Reference (Sheet1$) as the source Table location. The $ symbol is necessary with the Worksheet name:

   strConnection = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\msaccesstips\Branch.xls" sourceTable = "Sheet1$" 'Excel Sheet Name Reference
   

   The topmost row contents of the table area will be used as Field Names.

   strConnection = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=D:\msaccesstips\Branch.xls" sourceTable = "BranchNames" 'Excel Range Name Reference
   

2. Excel Range Name, Branch Names will be used as the Table location. The first line is the same as above for this example, also.

Earlier Post Link References:

• Column Chart and VBA
• Working with the Chart Object in VBA
• Linking IBM AS400 Tables
• Repairing a Compacting Database with VBA
• Database Open, Close Event Alerts

Read More

Source Connect Str Property and ODBC

'Source Connect Str' Property and ODBC.

We have already seen that the SourceConnectStr property, when used together with the 'Source Database' property in an MS Access Query, allows us to open and work directly with external data sources such as dBase, FoxPro (Versions 2.5 or 3.0), and Excel tables.

We also learned how to include these property specifications within an 'IN clause' directly in the SQL statement of a query.

However, for data sources such as AS/400 (iSeries), SQL Server, and FoxPro (via newer database engines), Access requires an ODBC (Open Database Connectivity) connection string. This connection string defines how Access communicates with these external systems, specifying the driver, data source name, authentication credentials, and other parameters needed to establish the connection.

ODBC Connection String.

The best way to learn and understand more about the Connection String Syntax of different ODBC Data Sources is to go through the following steps and look at the Connection String of the Linked Table:

1. Create an ODBC DSN (Data Source Name). Refer to the Post Linking with IBM AS400 Tables.

2. Link the Table from the source directly using File -> Get External Data -> Link Table.

3. Select ODBC Databases in the Files of Type control.

4. Select the ODBC DSN that you have created from the displayed list.

5. Click OK. If you have not created a DSN, you can create a new one by selecting the New... Command Button.

6. Select the Table to link with your MS-Access Database.

7. After linking the Table, select the linked Table.

8. Select Design from the Database Menu. You will receive a warning message saying that the Linked Table Structure cannot be modified. Click Yes to the Prompt: Do you want to open it anyway?

9. Display the Property Sheet (View ->Properties).

Description Property of Table.

On the Description Property of the Table Structure, you will find the ODBC String that can be used directly on the Query's SourceConnectStr Property.

A few examples of ODBC Connection String Values are given below:

AS400 (iSeries) Table:
ODBC;DSN=myData;UID=UserID;PWD=Password;TABLE=PAVUP.APC161D  
SQL Server:
ODBC;DSN=MyData;UID=UserID;PWD=Password;DATABASE=Parts  
FoxPro:
ODBC;DSN=Visual FoxPro Tables;UID=;PWD=;SourceDB=C:\MyFoxpro;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=GENERAL;Null=Yes;Deleted=Yes  

As shown in the examples above, the DSN Name, User ID, Password, and other parameters in the ODBC connection string are specific to each data source and must be entered accurately to establish a valid connection to their respective tables.

In the case of the AS400 (iSeries) ODBC connection string, the table name and library (or folder) name are separated by a dot (.), following the convention used in IBM systems, for example, `MYLIB.MYTABLE`.

For more details on setting up such connections, you can refer to the earlier discussion titled “Linking with IBM AS400 Tables”, which explains how to properly link AS400 (iSeries) tables to a Microsoft Access database using ODBC drivers and connection parameters.

Earlier Post Link References:

• Working with the Chart Object in VBA
• Linking with IBM AS400 Tables
• Repairing a Compacting Database with VBA
• Database Open, Close Event Alerts
• List Box and Date: Part-2

Read More

Access Live Data in Excel-2

Continued from last week's post:

This is a continuation of the earlier post Access Live Data in Excel. Please refer to 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 on a cell within the Data Area on the Worksheet.

2. Point to Import External Data (Get External Data in MS Office 2000) in the 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.

Microsoft Query.

Alternatively, you can open the 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 the 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 the 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 the 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 for Northwind is also omitted from the database file name specification.

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

Properties of Microsoft Query.

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 the Northwind.mdb database.

3. Select View -> Design View to change the Query 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 are loaded in the Alias Property, and the Path Name of the NorthWind database appears in the Source Property without the .mdb extension.

7. Change the Table Name appearing in the Alias Property to a different name, say myCategories.

8. Add the .mdb extension at the end of the Path Name string in the Source Property.

The Table Name now appears as myCategories in the Title of the Table. Turn the Query into an SQL View. You can see that at the end of the SQL String, the reference to the Table name, Categories (which appeared twice earlier), has now changed to myCategories.

We must qualify each data field with the Table Name myCategories due to the Alias Name change. Change the SQL String as shown below to qualify each Field with the alias name. Enclose the Database Path Name in Square Brackets ([]) in place of the 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 there is no way MS Access can guess the name. If we are using a Table from within the Database or from a linked Table, then the Alias Name change will automatically take effect in all the fields. You can try this experiment with one of your own Tables 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 a direct Path Name or an ODBC Connection String, and the SQL Syntax is different in the FROM Clause of the Query definition.

Earlier Post Link References:

1. Roundup Function in Excel in MS-Access
2. Proper Function in Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel-Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word files from Access

Read More

MS-Access Live Data in Excel

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.

1. Open a new MS Excel workbook.

2. Select Cell A1 on Sheet1.

3. Point to Import External Data in the Data Menu.

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

5. Select the MS-Access Database* from the list and click OK.

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

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

8. After selecting the Fields, click Next. Here, you can define Filter conditions.

   

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

9. Click Next to proceed to the Sort options.

   

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

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

11. See the Radio Button 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 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.

1. Open the Categories Table of the Northwind.mdb Database.

2. Add Crabs and 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 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.

4. Click anywhere within the data Area.

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

6. Right-click anywhere within the linked table in Excel and select Data Range Properties from the shortcut menu.

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

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

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

1. Roundup Function in Excel in MS-Access
2. Proper Function in Excel in Microsoft Access
3. Appending Data from Excel to Access
4. Writing Excel Data Directly into Access
5. Printing MS-Access Report from Excel
6. Copy-Paste Data From Excel to Access 2007
7. Microsoft Excel, Power in MS-Access
8. Rounding Function MROUND of Excel
9. MS-Access Live Data in Excel
10. Access Live Data in Excel- 2
11. Opening an Excel Database Directly
12. Create Excel, Word files from Access

Read More