SEARCHING FOR OTHER FILES FROM MSACCESS

We can search for Microsoft Access database files on disk by selecting File – – > Open option from the main menu.  But, if we want to search, select and open some file, other than database, from disk then how do we do that?  If we can, then what do we do with those File(s) after bringing into Access? Well, we can create a Hyperlink to the file and store it in a data field or use FileCopy() function to copy it into a different location.

Anyway let us get to work with the first part. But before that, a preview of the Run of our Project is shown below:

Common Dialogue control image

You will see the same Dialog Control when you select the File – – >Open option from the main menu to open a Database from your Computer or from the Network Drive.

Let us design a small Form with the Common Dialog Control, a Textbox and a Command Button with few lines of VBA Code for our project.  The design will look like the image given below. 

Tip: You can download a demo database from the bottom of this page.

File Browser Design

The rectangular shaped object at the left side is the Common Dialog control inserted from the ActiveX control’s group. when the User clicks on the Browse. . . button the dialog box like the first image above opens up.

Now let us get to work.

  1. Open a new Form and Create a Text-Box Control, wide enough to hold the Path and File name selected from the disk.
    • Change the Caption of the child-label to File Path Name.
    • Select the Textbox control, display the property sheet (F4) and change the Name property value to lbldb.
  2. Create a Command Button as shown in the above Design, display its Property Sheet and change the following property values as shown below:
    • Name = cmdBrowse
    • Caption = Browse. . .
  3. Now it is time to bring in the real hero element of our design: the Microsoft Common Dialog Control, to do that follow the procedure given below:
    • Select ActiveX Control from Insert Menu
    • You will find a List of ActiveX Controls opens up, scroll down and select the Microsoft Common Dialog Control and Click OK. If you didn’t met any trouble on the way out after clicking the OK button you will find a square shaped control sitting on your Form. If your MS-Office installation is not properly done it is likely that you may end with a message like ‘this ActiveX DLL is not registered, re-install it‘ or something similar.

    Display the Property Sheet of the Common Dialog Control and change the Name property to cmDialog1. You can place it anywhere at your convenience, it will not be visible when you activate your Form.

  4. Click on the Command Button to select it.
  5. Display the Property Sheet (F4).
  6. Click on the On Click Event property and select [Event Procedure] from the drop-down control.
  7. Click on the build (. . .) button to open the VBA Module Window of the form.
  8. Copy the Following Visual Basic Code and paste it, over-writing the existing empty procedure lines, into the Module and save the Form.
Private Sub cmdBrowse_Click()
Dim VFile As String 
On Error GoTo cmdBrowse_Click_Err
  ChDrive ("C") 
ChDir ("C:\")
  cmDialog1.Filter = "All Files (*.*)|*.*| _ Text Files (*.txt)|*.txt|Excel WorkBooks (*.xls)|*.xls"  cmDialog1.FilterIndex = 1
  cmDialog1.Action = 1
  If cmDialog1.FileName =  "" Then 
      VFile = cmDialog1.FileName 
      Me!lbldb = VFile
  End If  
cmdBrowse_Click_Exit: 
Exit Sub  

cmdBrowse_Click_Err:
MsgBox Err.Description, , "cmdBrowse_Click" 
Resume cmdBrowse_Click_Exit 
End Sub 

Open the Form in normal view and click on the Browse Button. The File Browsing Control, that we have seen on the top of this page, will open up. Select a file from any location you like and click Open. The selected file with its complete location address will be inserted into the Text Box control.



Download Demo Database