?
Solved

Perform function on all Databases in a folder

Posted on 2013-01-30
3
Medium Priority
?
419 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Introduction to Processes

718 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