Problem exporting forms with DoCmd.TransferDatabase from an accde file.

Question

Access 2021

Hello,

In a database, from a button on a form, I create another database and export a table and a form to this new database. This functionality is in VBA code.

In the accdb file everything works perfectly.

I then create an accde file from the accdb file.

If I run the accde file, I get an error message about exporting the form.

The export of the table was carried out correctly.

Here is the VBA code :

Private Sub Commande0_Click()

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">Dim db As Database

Dim filePathDataBaseDestination As String

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">Const pathDataBaseDestination As String = "D:\Documents\Tempo"

Const nameDataBaseDestination As String = "NewDB.accdb"

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">filePathDataBaseDestination = pathDataBaseDestination & "" & nameDataBaseDestination

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">'Check if we need to create the database

If Dir(filePathDataBaseDestination) = "" Then

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">'Create the database

Set db = DBEngine.Workspaces(0).CreateDatabase(filePathDataBaseDestination, dbLangGeneral)

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">'Transfer the table

DoCmd.TransferDatabase acExport, "Microsoft Access", filePathDataBaseDestination, acTable, "Table1", "TableNew", False

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">'Transfer the form

DoCmd.TransferDatabase acExport, "Microsoft Access", filePathDataBaseDestination, acForm, "Formulaire2", "Formulaire2", False

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">db.Close

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">End If

https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA"https://learn-attachment.microsoft.com/api/attachments/8b1fe90b-9411-498e-a4ba-9da597ee9b45?platform=QnA" title="filestore.community.support.microsoft.com" rel="ugc nofollow">End Sub

Answer 1

If I want to keep the add-in in accde format, one solution would be to remove the form in the viewer program and display the ribbon without a form.

Hi Perpéte jacky,

Can't you use the form in the Add-in directly, without copying it to the viewer program?

Imb.

Answer 2

Hello Scottem,

I use my database in accde format as an add-in.

This database allows me to create custom ribbons in other databases.

From the add-in, I create another database in a user folder which will receive the data from the custom ribbon for its visualization.

This visualization database consists of a form and tables.

I chose this solution because I couldn't find a way to display a ribbon's changed data in the add-in without restarting the program. Ribbon data is only loaded at startup and is no longer updated afterward.

If I want to keep the add-in in accde format, one solution would be to remove the form in the viewer program and display the ribbon without a form.

To keep the form in the viewer program, another solution would be to simply change the accdb database extension to accde or accda to create the add-in.

For this solution, I lose the protections provided by creating the export of the accdb file in accde format.

(Possible bypass with password protection of VBA code).

The ideal solution would be for the ribbon to be able to reload the modified data from the USysRibbons table and then display it directly in the add-in.

Currently, I don't know this solution.

Is this possible?

It would be good for the future, a refresh or reload command for the ribbons.

Answer 3

Can you explain why you "must" do this and why you need an accde?

Answer 4

Hello Scottem,

Thank you for your comment.

You are right, I just found the info from:

https://support.microsoft.com/en-au/office/export-a-database-object-to-another-access-database-4e2cd6dd-e482-441e-88b5-aa5319a428a6.

If the file is in ACCDE format, the source object must be a table, query, or macro. Forms, reports and modules cannot be exported.

Unfortunately, this restriction is not explained in the help for the DoCmd.TransferDatabase method.

However, I must create an add-int which must create a database by exporting tables and a form.

How to make this add-in to avoid the problem?

Answer 5

I have never tried this, but in an accde, there is no access to design capabilities. So I would not expect an accde to allow export of a form.