asked on

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

rockiroads

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

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dialdnet

ASKER

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.