Make ACCDE in VBA

[Gasman]

I'm trying to create an ACCDE file from within my database.

I discovered code on the net

Public Function MakeACCDE(InPath As String, OutPath As String)

Dim app As New Access.Application

app.AutomationSecurity = 1 'msoAutomationSecurityLow

app.SysCmd 603, InPath, OutPath

Set app = Nothing

End Function

and am calling it from

Private Sub cmdCreate_Click()
Dim strFEPath As String, strBEPath As String, strACCDE As String
Dim Dummy As String

strFEPath = CurrentDb.Name
strBEPath = GetBackEndPath()
strACCDE = Left(strBEPath, InStrRev(strBEPath, "\") - 1) & "\Employee.accde"
Dummy = MakeACCDE(strFEPath, strACCDE)

End Sub

The paths are correct in debugger, I do not get any error but the accde is not created.

I've also tried

Sub CreateAccde()


If MsgBox("Make a new MDE file?", vbYesNo) = vbYes Then
SendKeys "%T"
SendKeys "D"
SendKeys "M"
SendKeys "C:\DB\DB1.mde"
SendKeys "%S"
SendKeys "{ENTER}"
End If

End Sub

that I found on this forum, but this just crashes and restarts Access (even if I change mde to accde) and brings up a dialogue about Digital certificate?

Has anyone managed to achieve this?, and if so could you please tell me how?

TIA

 

[Uncle Gizmo]

Try creating a *.accde by the normal method first. See if that works. Please report back.

 

[Uncle Gizmo]

I also read in another post that you can't do it if you are in the same database you are calling the code from.

 

[CJ_London]

I use a function similar to your first bit of code which does work if everything is correct

1. in and outpaths are correct and valid (i.e. ending .accdb/de)
2. your accdb has been compiled - preferable with Option Explicit at the top
3. all objects are saved (i.e. inpath is not currently open, or if it is then compacted just before running the code - looks like you inpath is the currentdb so is open and probably not compiled or saved - so look here first

My code uses

Dim app As Access.Application
Set app = New Access.Application

rather than your

Dim app As New Access.Application

So might make a difference

 

[CJ_London]

I also read in another post that you can't do it if you are in the same database you are calling the code from

you can - but all objects and code must be compiled and saved.

 

[missinglinq]

The most common reason for failure in creating an ACCDE file is, as has been suggested, that the original file doesn't compile successfully, and that's probably the first thing you need to address.

But being a terminally curious guy...I just have to ask...why would you want to do this in this manner?

Linq ;0)>

 

[Gasman]

Try creating a *.accde by the normal method first. See if that works. Please report back.

I've discovered that I have changed something that would not allow an accde to be created, but not sure as yet what it is. I've removed the forms for now.

Have to work on other things now, so will have to get back to this.

Many thanks for the quick replies.

 

[CJ_London]

I just have to ask...why would you want to do this in this manner?

I can't speak for Gasman, but for me I have a number of different templates for different things and have an automated system to combine them in different ways to provide different solutions, so it just forms part of that automation process. An analogy would be use this car body, with that engine, this level of trim and these gadgets.

 

[Gasman]

The most common reason for failure in creating an ACCDE file is, as has been suggested, that the original file doesn't compile successfully, and that's probably the first thing you need to address.

But being a terminally curious guy...I just have to ask...why would you want to do this in this manner?

Linq ;0)>

I am using the code that updates the FE and BE that was posted in a thread here. I have a form that will update both tables and then just wanted to create the accde from the same form, rather than go through the manual method.

Plus I am always keen to learn new things.
However I amended a form and in some way the code is now not compiling, so I have removed that form from the db for now (another maintenance form) and the db now compiles.

 

[Gasman]

I have amended the code to CJ's version.
Immediate window shows
? inpath
C:\Users\Paul\Documents\Employee.accdb
? outpath
S:\DB\Employee.accde

I have compiled the database, Compile option geryed out after doing so. Saved db.
It still does not work, in fact it does not even appear to attempt it, it passes to the next line too quickly.

My main concern for now is to see what I have mucked up, as that particular code had been in for quite a while.:banghead:

 

[CJ_London]

the 603 code is not documented as such, only referenced by the odd thread.

You saved the db - did you also compact? Only time I've had a problem was when I had forgotten to save a form or report object - just saving the vba is not enough.

Does S:\DB directory exist?

Is there already a Employee.accde file? And if so is it open? Better to delete it first.

Can you create a .accde using the manual method?

Although not essential it is advisable to decompile on a regular basis

 

[Gasman]

Hi CJ,

the 603 code is not documented as such, only referenced by the odd thread.

You saved the db - did you also compact? Only time I've had a problem was when I had forgotten to save a form or report object - just saving the vba is not enough.

Yes

Does S:\DB directory exist?

Yes, please see attached pic.

Is there already a Employee.accde file? And if so is it open? Better to delete it first.

Yes, that is where it resides. Not open, as those who use it have their own copies on their PCs

Can you create a .accde using the manual method?

Yes. I corrected my form error and code compiles. After compile, the Compile option is greyed out until I edit again?

Although not essential it is advisable to decompile on a regular basis

I've not done that. Will investigate on how to.
Found out how to.

Decompiled.
Compiled VBA
Compacted
Made accde manually

Still no joy.

I will try the code on a fresh db tomorrow and see what happens. Frustrating to say the least.

 

[CJ_London]

just to confirm when you made .accde manually, were you prompted to replace the existing .accde?

and yes - if compile option is greyed out, the vba is compiled.

Suggest try saving to a different name - e.g. Employee1.accde. Or temporarily rename the existing file. If that works, you need a bit of code in your routine to delete the current file first.

Other than that, I'm out of ideas. I'm using 32bit 2010, perhaps it is different if you are using 64bit although I'm pretty sure the syscmd 603 is what access uses when it creates a .accde - wrapped up in other stuff for the full routine. It's the same command for .mde for example

 

[Gasman]

just to confirm when you made .accde manually, were you prompted to replace the existing .accde?

and yes - if compile option is greyed out, the vba is compiled.

Suggest try saving to a different name - e.g. Employee1.accde. Or temporarily rename the existing file. If that works, you need a bit of code in your routine to delete the current file first.

Other than that, I'm out of ideas. I'm using 32bit 2010, perhaps it is different if you are using 64bit although I'm pretty sure the syscmd 603 is what access uses when it creates a .accde - wrapped up in other stuff for the full routine. It's the same command for .mde for example

CJ,

I 'll try that with that Help database I posted in another thread. Very little in it at present and no loss it something goes wrong with it.

 

[Gasman]

Ok I added the code to the attached DB.
Created a form frm ACCDE just to run the code.
It does exactly the same with my Timesheet database.
I can compact, make an accde file manually OK.

Could someone please amend the paths in the calling module and try it out.?

TIA

 

[CJ_London]

OK, I've reviewed my code against yours - in mine as part of the overall process, I actually create a new db from the current db as a backup and then created a .accde from that.

If you do the same, take a copy, you can then create a .accde from the copy.

Strange, I'm sure it used to work for me, although perhaps I'm mistaken - so Unc is right (sorry Unc)

 

[Gasman]

Thank you CJ.
I might need a hand on how to do that.
I've only ever run code from the database I am in. Will look at that tomorrow.
I'll try and make an accde of db1 from code in db2 first to see if that actually works for me. Then if i does, step to to run it as you have done from DB1

 

[CJ_London]

you still run the code from the database you are in - but on a copy of the database you are in

app.SysCmd 603, "Copy of current database.accdb", "User database.accde"

 

[Gasman]

[Solved] Re: Make ACCDE in VBA

CJ,

That works.

Thank you.