Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Perform function on all Databases in a folder

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
Kev
Asked:
Kev
  • 2
1 Solution
 
chaauCommented:
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
 
KevAuthor Commented:
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
 
chaauCommented:
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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now