Solved

I need a command to save and publish a db as an accde via vba.

Posted on 2012-03-28
8
507 Views
Last Modified: 2012-09-21
I posted this before, but I was hoping someone might have some new ideas.

I need a command to save and publish a db as an accde via vba.

Any thoughts?
0
Comment
Question by:akivashapiro
  • 4
  • 3
8 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
You can try:

SysCmd 603, strPathInputMdb, strPathOutputMde

However it cannot be the current MDB.

Jim.
0
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
DO you mean you're trying to create an accde from the database you're currently in?
I don't think that is possible.

It is possible to create an mde of a different database in code.
0
 

Author Comment

by:akivashapiro
Comment Utility
Sorry for the long delay.

I'm using MS Access 2010.

I create a new database and I tried

  SysCmd 603, strPathInputMdb, strPathOutputMde

It does nothing. Also, 603 is not a value listed for SysCmd in the help or drop down enumerations. It may be a hidden command, but it does nothing and generates no error.

I then tried creating a new application instance:

  Dim appAccess As New Access.Application
   appAccess.OpenCurrentDatabase sourceDB$ 'where sourceDB is a different DB from the currentDb
  appAccess.RunCommand acCmdMakeMDEFile

This returns the same error message as when trying to execute acCmdMakeMDEFile on the current db.

Both of you suggest that what I want to do can be accomplished - albeit in a differrent db. If you could attach some code you've tested that would be great and appreciated.

Thanks!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<It does nothing. Also, 603 is not a value listed for SysCmd in the help or drop down enumerations. It may be a hidden command, but it does nothing and generates no error.>>

 603 is an undocumented call that worked in earlier versions

<<I then tried creating a new application instance:

  Dim appAccess As New Access.Application
>>

  That approach would work, but you don't want to open the database.

Try this instead:

Function GenerateMDEFile(MyPath As String)

    On Error GoTo FailedMDE
   
    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 MyPath & "{Enter}{Enter}"
    SendKeys "{Enter}"
   
    NAcc.DoCmd.RunCommand acCmdMakeMDEFile
   
    Goto Done

FailedMDE:
    msgbox "Failed" & vbcrlf & err.description
Done:
    On Error Resume Next
    Set NAcc = Nothing

End Function


  Note the DB is not opened.  This should work as it is a solution posted by Rocki Roads here on EE.  

  However, I don't know that I'd be trying to automate MDE creation; it's so problematic.   A MDB must be fully compiled, compacted and repaired, and be free from errors in order to convert to a MDE.

  I typically create a brand new DB, import everything, check references, then compile, and follow with a C&R before trying to make a MDE.

  Many in the past have tried to automate that whole process and without much luck (it's simply easier to do it manually).

Jim.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:akivashapiro
Comment Utility
Thanks Jim for the response.

-> In order to do this:
    'The following lines simulate accepting the default
    'name, clicking Make MDE, and clicking Save
    SendKeys MyPath & "{Enter}{Enter}"
    SendKeys "{Enter}"

-> There must be a dialog box to accept the input.

-> This line does not generate a dialog box in Access 2010. I think it did in 2003.
    Set NAcc = CreateObject("Access.Application")

Any other ideas?

Thanks.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Any other ideas?>>

 SendKey's sequence is a bit different.  You need Alt/F, then O to bring up the open file dialog.  

  But the other thing that trips this up is the "Enable content" button.  This method is too fragile and the wrong approach.

  Let me see what I can come up with.

Jim.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
Comment Utility
I'm afraid I've come up with not much.  Best you can do is put the DB in a trusted location and use the sendkey's approach and I have verified that the syscmd 603 call does not work beyond 2003.

Honestly, the MDE creation process is so problematic, I'm not sure why you'd want to automate it anyway.

Jim.
0
 

Author Comment

by:akivashapiro
Comment Utility
Okay, thanks for trying.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

9 Experts available now in Live!

Get 1:1 Help Now