Frondend db's working together as one application

Question

Hi,

Could I have two frondend db's working together so it's for the user as if it works as one application?

The background to this question is that my current frondend db has so much functionality that it has reached its maximum in VBA. I've been researching the following four weeks (also on this forum), but I come to the conclusion that my current frondend is at the maximum.

Upgrading to SQL Server is not an option.
I could split functionality. But in the search for the right solution, I thought: Is it an option to split my current frondend into two parts and work these db together so that it is for the user as if there is still one application, but that in reality this are two applications?

Does anyone have experience with this or a suggestion?

Regards

Henk

Answer 1

Does anyone have experience with this or a suggestion?

Hi Henk,

You can have as many FE's as you want to the same BE. It just depends on the linking of the tables. You can even use a not-linked FE, and use late binding to the tables.

The maintenance of the different FE's will be more complicated, I fear, and for the user it could be more disturbing which FE to use, especially in the area where the different FE's "overlap" each other.

A different approach could be to make a referenced library database, where code can be placed "externally". If you furthermore try to reduce the amount of code by re-use of several parts of code, and place that in the library database, you could make a very efficient system.

Imb.

Answer 2

Hi imb,

I'm still struggling with my FE, which in the VBA editor continues to keep the message "out of memory".
I have tested to delete a couple of forms thans contains VBA code. Then the error message is gone. So I conclude that it's most likely that my FE in VBA code has become too big.

That's why I'm looking for a solution.
The current FE is suitable for multiple user types. One solution would be to limit that. Only that is very complex to break apart.
Another solution I thought was to transfer a piece of functionality to a separate FE. Disadvantage is indeed (as you indicate) that it is not really user-friendly to the user.
But I was curious whether there might be a possibility that the user can access multiple FE from one work environment.
But this seems to be very complex.

Your option to create an external library and then works with late binding may be a workable option. But I have no experience (yet).
I assume that this is only possible with stand-alone modules?
Could you give me some more information about this?

Henk

Answer 3

One option is to create a Reference Library database in which you put all your common code: Windows APIs, Files & Folders, ... Then you can reference it within your db and call the code as required, but it isn't counted against you limit.  This way, in your db, you only house code specific to your db application and none of the general reusable code (that is all in the Reference Library db).

I'm curious though, How many modules, lines of code do you currently have in your db?

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 4

Hi Daniël,

102346 line(s) in 132 stand-alone module(s)

411539 line(s) in 535 module(s) behind forms

12682 line(s) in 125 module(s) behind reports

total 526567

My database is the result of years of development (as hobby).

Thus the db grows unnoticed. However, the db is in the closed state but 270 mb.

Henk

Answer 5

Your option to create an external library and then works with late binding may be a workable option. But I have no experience (yet).
I assume that this is only possible with stand-alone modules?
Could you give me some more information about this?

Hi Henk,

The late binding is not an important option on this moment, I only used it to show that there are many possibilities within Access to reach some goal.

The general modules can be placed in this Referenced library database, but also large parts from Form or Report modules that have common code, or code that can easily be tranferred to common code.

I have started this way of working long time ago. The result is that more than 95% of all code in an application is in a Referenced library database. Not because I reached the limits of VBA, but because I wanted the exactly the same kind of behaviour in all (about 100) my applications. The result of this is that when I start a new application, it is already working for more than 95%. It only lacks the data, whatever that is.

If you are interested, you could pass by and see many examples.

Imb.

Answer 6

Hi Imb,

I am very interested.This offers a lot of perspective!
Unfortunately, my mobility is not so self-sufficient to pass by shortly.Depend on others.So I have to think about it. But I'm interested!! It is clear to me that I will have to apply another method of programming.

Answer 7

Hi Daniël,

I always distribute my applications as 'accde'.
If I make a reference to a referenced database, how can I change it in my app?
I can only adjust references if the application is an accdb?

Henk

Answer 8

See: https://books.google.ca/books?id=0vaEcTMIOKgC&pg=PA1082&lpg=PA1082&dq=access+reference+library+database&source=bl&ots=BE5OtNZPBm&sig=4oymFR-VWL_hWKc3xFcBLnnmzsg&hl=en&sa=X&ved=0ahUKEwjih5_h26zXAhWi6YMKHafPDHsQ6AEIUzAI#v=onepage&q=access%20reference%20library%20database&f=false

At the end of the day it just needs to be present in the same directory as the FE.  Access is smart enough to make the necessary adjustments.

---

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 9

Thanks Daniël,

I ty it out.

Answer 10

Hello,

Could you successfully reference project in anther database? If your issue has been resolved, i suggest you mark helpful post as answer to close this thread.

If you have any issue, please let us know.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].