Solved

Perform function on all Databases in a folder

Posted on 2013-01-30
3
373 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:budorat
  • 2
3 Comments
 
LVL 24

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:budorat
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 24

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now