Solved

Make MDEs of several MDBs in Batch process

Posted on 2004-03-25
5
628 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

13 Experts available now in Live!

Get 1:1 Help Now