Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Make MDEs of several MDBs in Batch process

Posted on 2004-03-25
5
Medium Priority
?
650 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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: …

636 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