[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Make MDEs of several MDBs in Batch process

Is there a way in VBA to take Several databases and create mde's out of them?  I have an application that consists of several mdb's.  I would like to be able to run from code to save all these mdb's as mde's so that I can have any changes compiled and available quicker.  I have tried the following code, but it seems to hang up waiting for the security logon to access.  ***** in the code below signifies where I have the password sent, it did work the first time I ran the code, and then I would have to press ESC manually and it would work for each subsequent file.  However, now when I try to run it, it always stops on the first opening of the new access application.  Can anybody please help!!! Thank you... I have the code listed below:

Sub MDE_All()
Dim db As Database
Dim rs As Recordset
    Set db = CodeDb
    Set rs = db.OpenRecordset("tblModules", dbOpenDynaset, dbSeeChanges)
    While Not rs.EOF
    GenerateMDEFile ("U:\Development\" & rs!FileName)
    rs.MoveNext
Wend
   

End Sub
Function GenerateMDEFile(MyPath As String)
 
    Dim NAcc As Access.Application
    SendKeys "*****{Enter}"
    Set NAcc = CreateObject("Access.Application")
   'The following lines simulate accepting the default
   'name, clicking Make MDE, and clicking Save
    SendKeys MyPath & "{Enter}{Enter}"
    SendKeys "{Left}{Enter}"
    NAcc.DoCmd.RunCommand acCmdMakeMDEFile
    NAcc.Quit
    Set NAcc = Nothing
End Function
0
dialdnet
Asked:
dialdnet
  • 2
1 Solution
 
rockiroadsCommented:
Ive got something similar, this worked (test mod below), it created 3 mde files without any user intervention
the only difference I coudl see is in the send keys, I didnt do the 2nd one

Public Function Install_GenerateMDEFile(ByVal sPath As String)
   
    Dim NAcc As Access.Application
   
    Set NAcc = CreateObject("Access.Application")

    'The following lines simulate accepting the default name, clicking Make MDE, and clicking Save
    SendKeys sPath & "{Enter}{Enter}"
    'SendKeys "{Enter}"
    NAcc.DoCmd.RunCommand acCmdMakeMDEFile
   
    Set NAcc = Nothing
 End Function

Public Sub XMD()
    Install_GenerateMDEFile "C:\EMEA\X1\Budgets.mdb"
    Install_GenerateMDEFile "C:\EMEA\X1\B2.mdb"
    Install_GenerateMDEFile "C:\EMEA\X1\B3.mdb"
End Sub
0
 
rockiroadsCommented:
by the way the second SendKeys is coded but commented, very similar code, must be a popular solution

also you cannot create MDE files if there is a compile error (if you have modules)

maybe you can also compile you code as welll

e.g.

    sModule = CurrentDb.Containers("Modules").Documents(0).Name
   
    Debug.Print "Module Name : " & sModule
   
    'Open the module so we can use the Compile Modules menu command
    DoCmd.OpenModule sModule
   
    Debug.Print "Compiling"
   
    'Compile and save all modules
    Application.RunCommand acCmdCompileAndSaveAllModules
   
    'Close the module we opened
    DoCmd.Close acModule, sModule
0
 
dialdnetAuthor Commented:
I ran this program this morning after rebooting my pc and it works fine.  I now am working on some issues I have with making the mde a second time, but only because the files reference one another and compiling them in the wrong order gets the referencing messed up and it won't compile or mde properly until I have the right order, hard to explain, but this is why I wanted to have a mass mde process as well, I don't have to individually go to each file and mde them.  I have over 40 mdb files to mde... I didn't write this program... I just make it work! :)

I do have another issue with this, since you have done this before rockiroads, is there a way to prevent keyboard commands and mouse commands from screwing up the program?  I want to be able to start the process and then be able to use my keyboard to switch to another port on my switchbox.  It is a Raritan MasterConsole 4x8 so I don't have a button to press on the switchbox to get me access to another pc while I wait for the process to finish.

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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