Using add-in with internal databases. (1 Viewer)

[jackyP]

Hi there.

I created an add-in in accde format.
This add-in uses databases internally.
The contents of internal databases are editable while using the add-in.
I thought that when the add-in was opened several times at the same time, each time we had an independent.accde file with its own management of these databases.
My bad.
I found that internal databases were common to all open add-ins.

So I end up with a problem when multiple databases using the add-in are open at the same time. When switching the selection from one database to another, the internal data of the add-in no longer corresponds to the selection?

Can you advise me on the best possible solution?
In my case, shouldn't the opening of the add-in be prevented if it is already open in another database?

THANKS

 

[Josef P.]

The database file from which the add-in was opened can be used to save data or, if only temporary saving is required, a temporary database file can be created for each call.

 

[Pat Hartman]

Can you advise me on the best possible solution?

Not without knowing your objective. What data are you storing in the .accde? What problem do you think this solution is solving for you?

 

[jackyP]

Hi,
The add-in is a custom ribbon creation program.
It creates, loads and modifies XML data from the UsysRibbon table contained in a database in edit mode.
The data in XML format from the UsysRibbon table is decoded and distributed into tables internal to the complement (Attributes, Procedures, Controls).
The data from the tables in the add-in is then modified by the user and then reintroduced in XML format into the UsysRibbon table in the database.
Data from tables internal to the add-in is not preserved. They are deleted when the add-in is opened.

Here is my problem:
If a first database is opened to create or modify its custom ribbons using the add-in, the data from the UsysRibbon table will be loaded into the add-in's internal tables.
Without closing the first database, if another database is opened to do the same work, the data from the UsysRibbon table will also be loaded into the tables internal to the add-in and will overwrite the previous data from the first database.

In this case I have two uses of the add-in at the same time in two different databases with only valid data for the second database.

My goal is to persist data from each add-in opening at the same time.

Each time an add-in is opened, the creation of temporary tables and their management seem quite complex to me.

Can we consider blocking a second use of the supplement?

The add-in should look like an executable program encapsulating these own tables.

 

[Pat Hartman]

My goal is to preserve data for each add-in opening at the same time.

Then you can't store the modified data in the .accde.

The add-in should be like an executable program encapsulating these own tables.

But as you can see, it isn't. "Libraries", which this technically is, are shared they are not owned by a specific .accdb. They are shared.

 

[jackyP]

Hi @Pat Hartman,
Thank you for your reply.
This is what I also see.

Wouldn't the solution be to prevent a second opening of the complement?

 

[Josef P.]

Why do you have to save the (temporary) data in the add-in?

 

[cheekybuddha]

Sounds like you need to add a field to your table to record the name of the currently open app, and filter by that for whatever operations you perform

 

[Pat Hartman]

Wouldn't the solution be to prevent a second opening of the complement?

Not if you want to use the library in the second application.

 

[Josef P.]

With an add-in, you can easily access the database of the calling Access application with CurrentDb.
If the required tables are not created in the add-in but in the respective application file, an add-in can be used in parallel in several Access applications without any problems.
If bound forms are required, a recordset (created via CurrentDb) is used instead of the recordsource to bind the data to the form.

 

[jackyP]

Sounds like you need to add a field to your table to record the name of the currently open app, and filter by that for whatever operations you perform

Hi,

Perhaps the idea of adding a field representing the open application to tables in the add-in is the right solution.

The only problem to solve is this:
Currently, the contents of some tables are cleared when the add-in is opened.
If I use the new solution, the deletion should be done when closing the add-in taking into account the value of the new field in order to delete the data corresponding to the current application. This is not a problem unless the database shuts down inappropriately. I would keep unnecessary data.

One solution would be to detect when opening the add-in that it is the only one in use.
In this case it would clear the data from the tables.
Another add-in opened subsequently and at the same time would not perform this deletion.

The question is how can the add-in detect when it is opened that it is the only one in use?

 

[Josef P.]

I think it is the wrong approach to save external data in the add-in, but if you use the full path of the application as "ID" for the application assignment (name alone might not be enough), you can also delete only these data records when starting the add-in. Then it doesn't matter if the database is already being used in another instance of add-in.

DeleteSql = "delete from YourTable where AppPath = '" &  replace(CurrentDb.Name, "'", "''") & "'"
codedb.execute DeleteSql

 

[ebs17]

Each time an add-in is opened, the creation of temporary tables and their management seems quite complex to me.

Why?

If you work intensively with temporary data and temporary tables, you should neither burden your frontend nor your backend nor an add-in, but rather move the management of such temporary data to an additional local temporary backend, or to several.

To clarify - the entire workflow is difficult to understand:
Where do the multiple applications running in parallel on the same local computer come from? What are you doing?
Is changing ribbons a development process, or does it happen every time at runtime?

 

[jackyP]

Why?

If you work intensively with temporary data and temporary tables, you should neither burden your frontend nor your backend nor an add-in, but rather move the management of such temporary data to an additional local temporary backend, or to several.

To clarify - the entire workflow is difficult to understand:
Where do the multiple applications running in parallel on the same local computer come from? What are you doing?
Is changing ribbons a development process, or does it happen every time at runtime?

Hi,

I tried to re-specify the use of the complement in my comment of September 26.

The add-in is a custom ribbon creation development process.
It creates, loads and modifies XML data from the UsysRibbon table contained in a database in edit mode.

In edit mode, the add-in can be used in different open databases at the same time.

 

[Josef P.]

Although the same add-in file is opened by the different databases, each Access.Application instance (per database) runs its own instance of the add-in application. These only share the database objects. And if you do not save anything there, you will not have a problem with multiple use.

So once again the question: Why do these tables have to be used in the add-in database?
I would use the database of the calling application for this. This is exactly what an add-in is made for - work with the main application.
CodeDb and CurrentDb are not there for fun.

 

[jackyP]

Although the same add-in file is opened by the different databases, each Access.Application instance (per database) runs its own instance of the add-in application. These only share the database objects. And if you do not save anything there, you will not have a problem with multiple use.

So once again the question: Why do these tables have to be used in the add-in database?
I would use the database of the calling application for this. This is exactly what an add-in is made for - work with the main application.
CodeDb and CurrentDb are not there for fun.

When I initially created my add-in, I didn't think that the add-in's internal databases were shared.

To solve my problem, if I understood correctly, I could for example export my empty worktables from the add-in to the database of the calling application and create a link.
I will work with these tables from the add-in.

When the add-in closes, I will delete these tables from the calling application's database.

 

[Josef P.]

When the add-in closes, I will delete these tables from the calling application's database.

Just a thought: what if you leave the table in the application? You'll probably need it more often, right?
It might even be useful not to delete the data every time - then you could interrupt the work on the ribbon of the application and continue later without having to start again.
And since each application has its own table, the stored data will not interfere with any other application.

Tip: If the table name begins with "usys", the table isa "system table" and can be shown/hidden via the navigation settings.

 

[jackyP]

Just a thought: what if you leave the table in the application? You'll probably need it more often, right?
It might even be useful not to delete the data every time - then you could interrupt the work on the ribbon of the application and continue later without having to start again.
And since each application has its own table, the stored data will not interfere with any other application.

Tip: If the table name begins with "usys", the table isa "system table" and can be shown/hidden via the navigation settings.

Thank you very much for your suggestions.
Now I just have to make these changes.

 

[jackyP]

Just a thought: what if you leave the table in the application? You'll probably need it more often, right?
It might even be useful not to delete the data every time - then you could interrupt the work on the ribbon of the application and continue later without having to start again.
And since each application has its own table, the stored data will not interfere with any other application.

Tip: If the table name begins with "usys", the table isa "system table" and can be shown/hidden via the navigation settings.

When I initially created my add-in, I didn't think that the add-in's internal databases were shared.

To solve my problem, if I understood correctly, I could for example export my empty worktables from the add-in to the database of the calling application and create a link.
I will work with these tables from the add-in.

When the add-in closes, I will delete these tables from the calling application's database.

Good morning,

As a problem never comes alone, here is a problem with the DoCmd.TransferDatabase Method with which I am trying to export tables from the add-in to the database of the calling application.

During testing, using a button on a form of the add-in, I execute the following line of code:
DoCmd.TransferDatabase acExport, "Microsoft Access", "D:\Documents\Access\Database1.accdb", acTable, "RubanControle", "RubanControle", True

It works perfectly when I run my add-in with accdb format.

In a database of the calling application, the complement being in accde format, this no longer works.
I receive an error message.
The DoCmd.TransferDatabase method searches for the "RubanControle" table in the calling application and not in the add-in. Obviously, the table is not found.

For a check, I manually copied the "RubanControle" table into the calling application Database1.accdb.
When I run the add-in's DoCmd.TransferDatabase, I get the error message "Unable to export an object to itself".
This confirms the problem.

How to port the source of the DoCmd.TransferDatabase method to the accde add-in table instead of the calling application?

 

[ebs17]

Avoid using TransferDatebase and therefore the need to use an application reference.

Use DAO, the necessary references (CurrentDb / CodeDb) are already available, and a database developer should be able to handle a make table query or an append query with ease.