Solved

Make MDEs of several MDBs in Batch process

Posted on 2004-03-25
5
637 Views
Last Modified: 2013-12-05
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
Comment
Question by:dialdnet
  • 2
5 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 10687727
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 10687741
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
 

Author Comment

by:dialdnet
ID: 10687845
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

829 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