Unable to create .accde

[JohnPapa]

It is my understanding that when you create a .accde, ACCESS copies the original .accdb file and converts the copy to .accde. Therefore, if you attempt to open the new .accde file without first closing the original .accdb file, you will get that message. Is that what happened?

To create the .accde you must be inside the .accdb.

 

[JohnPapa]

I fixed the problem by moving everything on a new pc. The old pc was a generation 6 pc which was taking its time to respond and maybe this has something to do with the problem. It's been a few months now that the creation of the .accde was a matter of luck. Sometimes it worked and sometimes it did not. Looking back, maybe the entire problem is a hardware problem which manifested itself in large dbs.

 

[isladogs]

To create the .accde you must be inside the .accdb.

Although that is the standard approach, it is possible to create an ACCDE of an external database using code:

Sub MakeACCDE(strPathSource As String, strPathDest As String)

      Dim app as Object

      'First create the Access Automation object
      Set app = CreateObject("Access.Application")

      'Now call the undocumented function
      'this may give err 7952 - illegal function call
      'app.SysCmd 603, strPathSource, strPathDest

      'fix by explicitly setting the paths as strings
      app.SysCmd 603, CStr(strPathSource), CStr(strPathDest)

End Sub

For more info, see my article:

Create ACCDB/ACCDE Files In Code

This explains how to create both ACCDB & ACCDE files in code. Creating an ACCDE file requires the use of an undocumented SysCmd function

isladogs.co.uk

Since I wrote that article, SysCmd 603 has become officially supported as acSysCmdCompile
If preferred use

Sub MakeACCDE(strPathSource As String, strPathDest As String)

      Dim app as Object

      'First create the Access Automation object
      Set app = CreateObject("Access.Application")

      'Now create the ACCDE using SysCmd acSysCmdCompile
      'explicitly set the paths as strings
      app.SysCmd acSysCmdCompile, CStr(strPathSource), CStr(strPathDest)

End Sub

 

[JohnPapa]

I will try the above.

Well I spoke too soon. I was able to create once the .accde and then I get the same problem.

Basically that the .accdb is already in use. In the Help it mentions that there may be too many TableIDs

 

[LarryE]

To create the .accde you must be inside the .accdb.

Once the .accde is created, you should then close the .accdb file and open the .accde file.

 

[JohnPapa]

Could this be the problem?

 

[CJ_London]

if you have that number of forms and reports, etc. possibly - I believe accdb’s have a higher capacity than mdb’s - recent tests on another thread indicate the number of objects is more like 5000 rather than 1000

 

[JohnPapa]

if you have that number of forms and reports, etc. possibly - I believe accdb’s have a higher capacity than mdb’s - recent tests on another thread indicate the number of objects is more like 5000 rather than 1000

The number is at least 2048.

Would it help if I set the Has Module to False for many of the forms and reports?

Edit: There is no problem in creating .accde for the smaller dbs

 

[isladogs]

OK several points here.
1. As stated, the database MUST be compiled - which you've stated yours is!
2. @arnelgp stated that you should open in exclusive mode. That won't do any harm but isn't a requirement for creating an ACCDE.
3. @LarryE stated that the ACCDB should be closed before opening an ACCDE created from it. Again, it won't do any harm but it isn't necessary

I have successfully created ACCDEs from very large split databases including one with over 5500 objects in the FE including almost 600 forms & 900 reports, almost all of which have code modules. I doubt that setting HasModule to False will help. What it will do is remove all the code you had in those modules.

Before you start making possible unnecessary changes to your database, can I suggest you read another of my articles:

Why can't I create an ACCDE file?

This article explains the main reasons why creating an ACCDE file may fail. It also explains how to fix those issues

www.isladogs.co.uk

Perhaps that will contain some info to help solve your issue
e.g. empty procedures, missing references, incorrect API declarations, old ActiveX controls

 

[isladogs]

Forgot to say that two Access limits are being muddled above
1. The maximum number of code modules is officially 1000 but in my tests the actual figure is about 5450 in A365. Possibly less in A2013
2. The maximum number of open TableIDs was 2048 until last year when that limit was doubled in A365. TableIDs are also created by queries, form/report record sources, list box and combo row sources and SQL in code modules in addition to open tables.
However I doubt this limit is preventing you creating an ACCDE

 

[JohnPapa]

OK several points here.
1. As stated, the database MUST be compiled - which you've stated yours is!
2. @arnelgp stated that you should open in exclusive mode. That won't do any harm but isn't a requirement for creating an ACCDE.
3. @LarryE stated that the ACCDB should be closed before opening an ACCDE created from it. Again, it won't do any harm but it isn't necessary

I have successfully created ACCDEs from very large split databases including one with over 5500 objects in the FE including almost 600 forms & 900 reports, almost all of which have code modules. I doubt that setting HasModule to False will help. What it will do is remove all the code you had in those modules.

Before you start making possible unnecessary changes to your database, can I suggest you read another of my articles:

Why can't I create an ACCDE file?

This article explains the main reasons why creating an ACCDE file may fail. It also explains how to fix those issues

www.isladogs.co.uk

Perhaps that will contain some info to help solve your issue
e.g. empty procedures, missing references, incorrect API declarations, old ActiveX controls

I will make sure that the Option Explicit is placed in all modules, forms, reports. If it is not present then it can compile but may cause problems. So it has to compile with Option Explicit present to be on the safe side.

When I close my .accdb the locking file will close if no-one else is using the BE. Sometimes a file called DATABASE.mdb (even with .accdb) appears and then usually disappears, What does this file contain and do I have to give it some time for the processing of DATABASE.mdb when closing the .accdb file?

 

[Minty]

The DATABASE.mdb file is normally created when a compact and repair is run on close.

 

[JohnPapa]

The DATABASE.mdb file is normally created when a compact and repair is run on close.

Is it advisable to interfere with its creation, like deleting all Access processes? What would happen if all Access processes are deleted in the process of the creation of DATABASE.mdb?

 

[Minty]

I wouldn't mess with that - it's used as a temporary store for the data whilst Access is doing the compact and repair.
It sounds as if you have it set to compact on close, which isn't generally considered good practice?

 

[JohnPapa]

I wouldn't mess with that - it's used as a temporary store for the data whilst Access is doing the compact and repair.
It sounds as if you have it set to compact on close, which isn't generally considered good practice?

In other words, do not interrupt the creation of DATABASE.mdb in any way. Wait for it to "cool down"

 

[JohnPapa]

I have fixed all the Option Explicit and I am still getting that my .accdb is in use. I will also check the api declarations.

I could post the entire code around 80Mb.

 

[isladogs]

That would exceed the forum limit many times over.
Don’t forget to check for empty procedures. These are a common cause for ACCDE creation failing.

 

[JohnPapa]

That would exceed the forum limit many times over.
Don’t forget to check for empty procedures. These are a common cause for ACCDE creation failing.

Good to know. It did not appear to make a difference in my case.
If push comes to shove as a temporary solution I can rename the accbd to accde, so that the client has continuity. I would just need a new way to lock the software the software. Hopefully, it will not come to that.
I have also been working on a solution using A365. BTW I can create an .accde in the 365 environment.

 

[isladogs]

I have also been working on a solution using A365. BTW I can create an .accde in the 365 environment.

That may indicate that you were hitting the TableID limit in 2013 as that limit was doubled in 365 last year
However, an ACCDE created in A365 MAY not work in 2013

 

[JohnPapa]

That may indicate that you were hitting the TableID limit in 2013 as that limit was doubled in 365 last year
However, an ACCDE created in A365 MAY not work in 2013

The TableID limit may be the problem since I am able to create .accde for smaller DBs in A2013