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..!!