Open Report remotely from other database (1 Viewer)

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
Good evening to all,

I have the following issue. I have two databases, into the first one there is a form and one command button, and into the second there is a Report. Is there a way to open the Report remotely using the command button which is into the other database?
 

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
Dear June,

Thanks, but still cannot manage the vba code in order to open the report from my second database.... Could you please apply a sample code?
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
Alternatively, link any tables used by that report & import the report together with any underlying queries needed. Then run it from the main database.
 

June7

AWF VIP
Local time
Today, 03:40
Joined
Mar 9, 2014
Messages
5,423
What did you try? What happens? Post your attempted code. Following example works for me:
Code:
Private Sub Command29_Click()
Dim appAccess As New Access.Application
appAccess.OpenCurrentDatabase ("C:\Users\June\Overtime.accdb")
appAccess.Visible = True
appAccess.Run "Test"
End Sub
And now there is a second fully open db that user will have to close. Is that what you really want?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:40
Joined
Feb 19, 2002
Messages
42,970
Have you considered merging the applications? If they use the same data, perhaps there should be a single version of the FE rather than two.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:40
Joined
May 7, 2009
Messages
19,169
highly consider Post #4.
very light. no other Access popping up.
no extra vba required.
 

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
Hello all of you,

My better description of this issue is:

We have an old database which is accde format and as you may know we can not do any changes on reports in case where this may needed. Also keep in mind that, the database is not split due to bad initial design, and that's why we have left it until we finish with a new version which its on process.

Because of the above serious issue, I thought to transfer all reports and all related tables and queries to a new accdb database (which will be locked), in order to make any changes may occur into reports, but i must find the way to call those reports using a command button into the main database (accde).

The resulting questions are:

1. If using vba code we can find the way to call the reports from the accde database, how will they be presented if they open? I must go to the second base (accdb) to see them?

2. Can those reports they appear inside the main database (accde) as a Pop-up report?

Thanks in advanced
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
If I've understood you correctly, you want to copy database objects such as forms & reports from an ACCDE to a new ACCDB file.

Unfortunately that is not possible - ACCDE files are designed to prevent copying all database objects EXCEPT tables & queries
 

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
If I've understood you correctly, you want to copy database objects such as forms & reports from an ACCDE to a new ACCDB file.

Unfortunately that is not possible - ACCDE files are designed to prevent copying all database objects EXCEPT tables & queries

No my friend isladogs...!!!! As i told, i want to open the reports (which are embedded into a database B accdb), using a command button from database A (accde)..
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
No my friend isladogs...!!!! As i told, i want to open the reports (which are embedded into a database B accdb), using a command button from database A (accde)..

Yes that's what I thought originally but in post #8 you wrote this

Because of the above serious issue, I thought to transfer all reports and all related tables and queries to a new accdb database (which will be locked), in order to make any changes may occur into reports,

Hence my comment
 

June7

AWF VIP
Local time
Today, 03:40
Joined
Mar 9, 2014
Messages
5,423
I could only get code to call a procedure in a general module, not procedure behind form such as button click. Since you cannot modify accde to create procedure, I see no solution.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
The problem is that most button clicks, if implemented by the form wizards, are defined with Private button_Click... which means that external access to the button click code using VBA to call it is not possible. There is no public reference point that you can "grab" in order to trigger the event.

I think this problem is insoluble as asked. If you had the developer's copy of that .ACCDE then maybe you could do something, but the .ACCDE is designed to stop this kind of internal exploration and "diddling about."
 

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
The problem is that most button clicks, if implemented by the form wizards, are defined with Private button_Click... which means that external access to the button click code using VBA to call it is not possible. There is no public reference point that you can "grab" in order to trigger the event.

I think this problem is insoluble as asked. If you had the developer's copy of that .ACCDE then maybe you could do something, but the .ACCDE is designed to stop this kind of internal exploration and "diddling about."



Dear friend Doc_man, i'm the developer of this accde database, so i have the accdb format. Do you have an idea ?

Note that, after any modification we would like to return again to accde format.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
If the .ACCDE in file B contains data not in file A, you could copy the report to file A and LINK to the tables in file B, then re-point that report's data sources and run the PRINT operation directly from file A. June7 has also posted an option.

If neither of these are going to work for you, you have painted yourself into a corner.
 

isladogs

MVP / VIP
Local time
Today, 11:40
Joined
Jan 14, 2017
Messages
18,186
If the .ACCDE in file B contains data not in file A, you could copy the report to file A and LINK to the tables in file B, then re-point that report's data sources and run the PRINT operation directly from file A.

See post #4!
 

gstylianou

Registered User.
Local time
Today, 13:40
Joined
Dec 16, 2013
Messages
357
Good evening to all,

After many post where posted from all, Ι understand that maybe it's not clear what I needed .Therefore, i would like to make clear what is the ideal solution for my issue. I'm asking the help of the most experienced members in order to find the ideal solution of the following issue:

First of all, i think is necessary to to analyze exactly the problem and then to see how the issue can be resolved.
Before some years ago, I had created a Database which is used by several users till today.This database includes some standard reports which unfortunately for me some users asking for a different layout as well as sometimes a different design in total!! (logo, business details, colors etc)

Please note that, this database once installed to each user, is converted to accde file so nobody can not intervene in forms - code, etc (theoretically). I also underline that this database is not split (Backend / FrondEnd), and each time where tried to do that, some processes are not work normally. That's why till now remain as is it.

Converting the database to accde file, can solve the security problem, but it is a big problem for me if the user asks for changes on the reports. The process in this case its very hard and un-proffesional, but unfortunately with MSAccess we must following the un-proffesional way. As a result i would like to inform you about this un-proffesional way in order to solved the issue:

1. After the understanding about the changes to be made in the reports, I must connect to the user's computer and in order to get the accde file from C:/. in order to making the changes. For the moment I'm using the drobox to take the database file.

2. When i have the database into my PC, then I must go to find the master database.accdb of this user who I have Backup in order to making the changes that need to be made on it.

3. Then i'm going to create a new accdb file which i will import all tables from the accde file as well as all the other objects (forms, queries, macros, reports, modules etc) from the master backup accdb file of the user, in which I made the modifications on the reports design.

4. Once everything are ready into the new accdb file, I send the database into the shared folder which I have with all users. After all those procedures i must reconnect again with user's Pc in order to move the database from drobox to C:/ drive for the final installation.

5. After the installation finished, i save the database as accde and the process is completed.

Please note that, I am already in the process to creating a new version of this database (accde) since several months ago, and finally the new version will work as Backend - FrondEnd file without any problem.

What did I think to do to get rid of all the above:

1. Alternatively, I thought to create a second (admin database) accdb database which let's say we will call it AdminDatabase.

2. I will import all necessary objects into the new database in order the reports to work correctly. (reports, queries, etc). All the necessary related tables will be link with the accde database (lets call it for now "UserDatabase")

3. I will ensure that the new database (lets call it for now "AdminDatabase.accdb") will be locked and will installed in the same folder as the UserDatabase.accde.

4. After all the above i must find a solution so using Vba code to call the Reports from the AdminDatabase.accdb using a command button which already exists on form into the UserDatabase.accde.

5. With this solution i will be able to make any design changes directly on the reports into AdminDatabase.accdb. as well as all this work will be done directly on user's computer without having to do all the above procedures that are being followed at this time.

However, the questions that arise are so many, even if we found the correct solution to call the Reports directly from UserDatabase.accde.

Assuming that we can find correct solution and we are in possition to call the Reports from UserDatabase.accde, there is one big question.......those Reports are feasible to open normally as a popup in the same database (UserDatabase.accde) which we wanted? Or the user must go to the second database (AdminDatabase.accdb) to see the reports?

Any help - idea on how can solve the issue will be appreciated ..

thanks again for your time....even to read me..!!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 28, 2001
Messages
26,996
If a person can open the database as noted in post #4, it will be that person's copy of Access that is running. The number of databases open (one or two) is immaterial. Only one copy of Access will be running.

If UserDatabase opens the AdminDatabase and if the report you want in the AdminDatabase is ready, and finally if the command button in the UserDatabase.ACCDE points to the correct report when it issues a DoCmd OpenReport, it will open in the context of the user's copy of Access. (Not in the context of the UserDatabase OR the AdminDatabase, but in the user's Access DBEngine(0).Workspace(0). I believe that an option in the DoCmd OpenReport instructon (which would underly the command button) would define whether it opens as a pop-up or not.

If these conditions as I have outlined are not met then you cannot do what you are describing. In particular, if each user requires a unique report, you have to consider that your AdminDatabase will need uniquely named reports so that the individual copies of each UserDatabase will point to the custom report for that customer.
 

Users who are viewing this thread

Top Bottom