Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Perform function on all Databases in a folder

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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 …
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Introduction to Processes

971 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