Update Backend design from Frontend

[shafara7]

I have a Backend and Frontend database.
I am updating the design and adding some filter in the frontend.

How do I synchronize the design so that the Backend also have the same design as the Frontend? See pictures for example.

 

[jdraw]

?? I don't understand the synchronize the design

If you're talking about traditional Access front end/back end split database, the back end contains tables;
front end has queries, forms,reports,vba/macro.
Form design is in front end.

 

[isladogs]

I have a Backend and Frontend database.
I am updating the design and adding some filter in the frontend.

How do I synchronize the design so that the Backend also have the same design as the Frontend? See pictures for example.

Why would you need to do so?
The BE should just contain tables
Not forms or filters etc

All data manipulation is done in the FE

EDIT:
Just saw the other response from jdraw who was faster on the draw .... groan!

 

[Ranman256]

The backend needs no design sync. (Unless you mean table relink)
If you change a backend table, all you do is refresh links in the FE.
Either provide a button in the FE for users to refresh,
Or
Manually refresh the tables then send all users a new FE copy.

 

[shafara7]

For some reasons (that I also don't know), my team uses both Frontend and Backend.
The other employee uses the Frontend to key-in data.
The leader uses the Backend.

I know the Backend only stores tables but mine also have the Form, just like in Frontend. For example, see the picture.
There is a Form, which can only be opened in Backend. So I needed to update the design of the Form in Backend so that it is the same as my Frontend.

Hopefully I explained it clear.

 

[isladogs]

For some reasons (that I also don't know), my team uses both Frontend and Backend.
The other employee uses the Frontend to key-in data.
The leader uses the Backend.

I know the Backend only stores tables but mine also have the Form, just like in Frontend. For example, see the picture.
There is a Form, which can only be opened in Backend. So I needed to update the design of the Form in Backend so that it is the same as my Frontend.

Hopefully I explained it clear.

Very clear but also VERY bad practice.
You need to change user's behaviour so all use the FE

I would force that change by removing all except tables from the BE and then password protecting it!

 

[shafara7]

So nobodyever works with the Backend? Just me?

Okay I tried to activate the Form in Frontend, but it it does not show any data.

This Database is created by my previous employee so I am not sure what he had put in it.
Is it possible that he write a VBA code so that the data is only available in Backend? I mean do such things exist?

 

[Uncle Gizmo]

If the leader, or anyone else for that matter need special functionality not available to others, then by all means give them their own front end.

Sent from my SM-G925F using Tapatalk

 

[Orthodox Dave]

If there is no reason to work in the back end (such as security as pointed out by Uncle Gizmo), then you should work in the front end.

The fact that you can't activate the form in the Front End suggests it is based on table(s) not linked to the back end.

In a perfect world, there would be no tables in the front end (only LINKED tables) and no Linked tables in the back end (only Tables).

I suggest you inspect your list of tables first in both ends to see if this is the case.

 

[The_Doc_Man]

There are many parts to this question.

You SHOULD start from a properly split database in which the front-end has either no tables at all or only tables used for a temporary staging area. Some folks create and populate, and then later delete a third database for these temporaries, but that is a matter of style - and also a matter of how much time you have to get that done.

All truly shared data must be in the common back-end and that either should contain no code at all or it should have a minimum amount of defensive code to launch a form if you open it. In that BE form you would try to identify the person opening the BE and cause the application to quit if that person is not authorized to work in the BE.

Your FE should be secured by having an opening form that acts like a dispatcher or switchboard form (which you can look up in this forum using its Search functions). No user (even the boss) should work from a database that exposes the navigation pane and ribbon. The switchboard has to control everything.

Ideally, you can identify the person entering the database and have a table that associates the user with a role or level of permissions or something that says "this user can to THIS but cannot do THAT." Then, each action-related form (in its Form_Open routine) can look up whatever user role, privilege, or permission you have defined in the user-related tables to decide whether it wants to allow the user to proceed. Since the Form_Open event can be canceled, you can stop the user at that point, or allow the rest of the normal form opening events to proceed normally.

Now, the question of "synchronization." There are two types of changes you can make to the database (with respect to this question). (A) The kind of changes that only affect the FE and (B) the kind of changes that require a BE change, such as adding a new field to a table or adding a new table. In EITHER case, you need to make a separate copy of both FE and BE as your "design master" and this copy NEVER gets published. It is the developer's copy. You COPY the FE to a work area and set up its security before publishing it.

For case A, all you do is publish a new copy of the updated FE and arrange for everyone to pick it up. In this forum, search for "updating front-end" to see the many strategies involved. To keep your sanity, have a local facility in the FE, perhaps in the opening form that becomes or launches your switchboard, to look at some small table in the BE so that you can, if needed, pop up a message saying "incompatible versions." But if you use the method that updates the FE every time, that would become moot.

For case B, where you need to change BE table structure, you do everything in the design master first. Then set aside some time during which you will not only update the public copy of the FE (see prior paragraph) but you ALSO update the BE. To make THAT work, you have to take good notes for every BE change including a list of the fields you add and what value you define as the initial value for the fields. Ditto, new tables. The production database must be completely unavailable at this time.

You might wish to also look up DDL (Data Definition Language) which Access CAN use and it is related to SQL. It would be a way for you to implement the structural changes quickly so that you could, for example, make a macro to implement the changes. I rarely use macros but this situation is one of the times when I will do so.

As Dave suggests, the FIRST thing to examine is the location and function of every table. Since you "inherited" this beastie, it might not be as familiar to you as you will need it to be once you become its keeper. You MUST strive for the moment that you can look at a problem relating to that database and immediately know what tables are involved. There is also no substitute for knowing the business flow intimately.

Having gone through this myself about 15 years ago, I can tell you that the Database Documenter is your best friend forever. MS Word is a close second, because there is no excuse for not documenting things as to structure and purpose.

I see I've gotten a bit long-winded. You need to do a lot of thinking so I'll leave you at this point to consider your next step. And it is YOUR next step since it is your database. We can only offer suggestions. In the final analysis, you have to implement it.

 

[Orthodox Dave]

Thanks Doc Man. I'm impressed and will keep a note of this post for future reference.

 

[shafara7]

Now, the question of "synchronization." There are two types of changes you can make to the database (with respect to this question). (A) The kind of changes that only affect the FE and (B) the kind of changes that require a BE change, such as adding a new field to a table or adding a new table. In EITHER case, you need to make a separate copy of both FE and BE as your "design master" and this copy NEVER gets published. It is the developer's copy. You COPY the FE to a work area and set up its security before publishing it.

For case A, all you do is publish a new copy of the updated FE and arrange for everyone to pick it up. In this forum, search for "updating front-end" to see the many strategies involved. To keep your sanity, have a local facility in the FE, perhaps in the opening form that becomes or launches your switchboard, to look at some small table in the BE so that you can, if needed, pop up a message saying "incompatible versions." But if you use the method that updates the FE every time, that would become moot.

For case B, where you need to change BE table structure, you do everything in the design master first. Then set aside some time during which you will not only update the public copy of the FE (see prior paragraph) but you ALSO update the BE. To make THAT work, you have to take good notes for every BE change including a list of the fields you add and what value you define as the initial value for the fields. Ditto, new tables. The production database must be completely unavailable at this time.

Thank you very much for the clean explaination!
I believe my case is Case B, but I don't think I want to add new field or tables.

I have the "design master" version of my database, where I design and edit the layout.
I just added some combobox to make filtering easier.

On the special Form (where I said my leader used to open it in backend), the record is shown when I open it in my Design Mode database. But the records are not showing up in my Frontend Database. You can see from the pictures, one with the linked tables (Frontend) and one without the linked tables(Design Mode).

 

[shafara7]

Okay I just found out that my Backend does not contain only tables, but also forms and queries.
Actually it is a normal form that contains everything, it's just the tables are linked to the frontend.
That is why when I did some changes on the layout of the frontend, the layout of the backend does not change.
So the mystery is solved.