Solved

Perform function on all Databases in a folder

Posted on 2013-01-30
3
404 Views
Last Modified: 2013-01-31
Hi,

I manage many databases and often need to perform some updates on all of these databases  at once. I current have a small DB that I created to allow me to select a DB (mdb/mde) then perform a procedure on the selected DB.

What I really want to be able to do is to select a folder, then have code to automatically apply to the procedure to all the  MDB/MDE in the selected folder. I am just not sure how to do it myself.

Following is some code  I use regularly to enable/disable the ShiftByPass key.

Function EnableDisableShiftByPass(blnBypassStatus As Boolean)
'*************************
' PURPOSE:              Disable the shift at startup. Autoexec macro/Startup properties always executed
' INPUT PARAMETERS:     Nil
'*************************
On Error GoTo Err_Handler

Dim db As dao.Database
Dim prop As dao.Property
Dim strUser As String

Const conPropNotFound = 3270
Dim sDbaseName As String, MyDB As Database
    strUser = Environ("USERNAME")
        'Set db = CurrentDb()

   If IsNull(Me.txtDatabaseFileName) = False Then


        Set MyDB = OpenDatabase(txtDatabaseFileName)
        MyDB.Properties("AllowByPassKey") = blnBypassStatus       'Disables/Enables the shift key on startup.

         'MsgBox ("Enable set, result = " & ChangeProperty("AllowBypassKey", blnBypassStatus, True, MyDb))
        If blnBypassStatus = True Then
            MsgBox "Shift bypass successfully enabled " & strUser & vbCrLf
        ElseIf blnBypassStatus = False Then
            MsgBox "Shift bypass successfully disabled " & strUser & vbCrLf
        End If
   End If

Exit_Handler:
        Exit Function

Err_Handler:
        If Err = conPropNotFound Then                       'Create "AllowByPassKey" property if not exist
            Set prop = MyDB.CreateProperty("AllowByPassKey", dbBoolean, blnBypassStatus)
            db.Properties.Append prop
            Resume Next
        Else
            MsgBox "Function 'EnableDisableShiftByPass' did not successfully complete." & strUser
        End If
        Resume Exit_Handler
End Function

Open in new window


I want to be able to apply the above code to all databases in a given folder. I already know how to capture/store the folder name in the field txtDatabaseFileName.

Any assistance would be greatly appreciated.

Kev
0
Comment
Question by:Kev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 total points
ID: 38838245
these three lines of VB code will give you a list of all files in a directory:

    Set fso    = CreateObject("Scripting.FileSystemObject")
    Set vfolder = fso.GetFolder(sPhysPath)
    Set vfolderContents = vfolder.Files

Open in new window


Now, all you need to do is to scan all files in the folder:

    For Each sFilename In vfolderContents
        sFile = fso.GetFileName(sHL7Filename)
        ....perform you magic database update here
    Next

Open in new window


when you done, release all memory:

    Set fso = Nothing
    Set vfolder = Nothing
    Set vfolderContents = Nothing

Open in new window

0
 
LVL 5

Author Closing Comment

by:Kev
ID: 38838416
Outstanding answer, thanks a lot. I only needed to change the following to get it to work 100% in my DB:

From sFile = fso.GetFileName(sHL7Filename)
To sFile = fso.GetFileName(sFilename )

Thanks heaps.

Kev
0
 
LVL 25

Expert Comment

by:chaau
ID: 38838983
yes. that's right. Good pickup. I have copied the code from my script, and missed that bit when I replaced the variable names
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question