?
Solved

Make MDEs of several MDBs in Batch process

Posted on 2004-03-25
5
Medium Priority
?
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 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