Solved Create table by VBA in accde

[JohnPapa]

No you can't unless the data is static which of course a client list isn't.

If you think data in the FE is OK, you must be sharing the FE rather than giving each user his own personal copy. That is another big mistake. Only the BE is shared. When you update the FE, you post the updated version in the master folder on the server. The next time the use clicks on his shortcut, the replacement is copied to his local directory and opened.

As I mentioned the client list can for example contain the expiry date of a specific client's subscription. After the client pays his subscription this date is changed and the client downloads the new FE.

And yes you can keep data that is common to all users in the FE. As an example, let's say that all clients use graphic pictures. You have several options:
1) Keep pictures in an external folder on the hard disk
2) Keep it in the BE
3) Keep it in the FE

The FE has its advantages. The user cannot inadvertently delete the external folder and if the pictures are in the FE it is easier to delete, add. update the contents. In our case we have found it extremely helpful. Other people may not find it helpful.

 

[The_Doc_Man]

As I mentioned the client list can for example contain the expiry date of a specific client's subscription. After the client pays his subscription this date is changed and the client downloads the new FE.

And yes you can keep data that is common to all users in the FE. As an example, let's say that all clients use graphic pictures. You have several options:
1) Keep pictures in an external folder on the hard disk
2) Keep it in the BE
3) Keep it in the FE

The FE has its advantages. The user cannot inadvertently delete the external folder and if the pictures are in the FE it is easier to delete, add. update the contents. In our case we have found it extremely helpful. Other people may not find it helpful.

The external folder can be different than the one used for the FE repository or the BE shared file. THAT folder doesn't have to have DELETE privileges for users. Only the BE folder needs full-on MODIFY privileges. As to "the user cannot inadvertently delete"... if you have users who don't honor company-imposed rules on keeping their mitts away from infrastructure, invest in some barbed-wire bullwhips. Inadvertent deletion can only occur when a user is exceeding his/her authority OR when the code has a bad flaw in it. In the first case, find a way to publicly punish that person. In the second case, whose fault is that?

Keeping multiple-user data in a shared FE file that is downloaded to each user's PC might seem reasonable for performance issues, but if ANY FIELD in that FE's user-oriented table gets changed in the BE, you now need to find a way to detect the change and then to reconcile the shared table vs. the master table. You just bought yourself a ton of overhead. Not only that, but TECHNICALLY that FE-side table now violates normalization even if the BE-side table does not. The question related to normalization is this: You now have two fields holding the same data. What other field do you consult to determine whether to refresh from the BE or use the copy in the FE? Which field is definitive? If it is time-sensitive, which copy contains the deciding date? And why do you continue to use the other copy when you know it ISN'T definitive?

I once did something similar to this, but NOT with user data. We had a lot of lookup tables, SOME of which might have records added to them during a business day, but it was a VERY rare event. I put copies of the lookup data in the FE along with a date marker to show when that lookup table was last refreshed. If you were about to run procedures that would use the lookup, the code would compare the dates for the FE and BE sources and would, if needed, refresh the sources before continuing.

This was NEVER anything but lookup/translation data, which 99% of the time was static. When the app started, the lookup tables in the FE got refreshed and usually stayed that way. It was needed because of horrific speed differences in FE vs. BE. Then we finally got the speed issues resolved by being allocated a VM to act as a back-end that could be LOCALLY shared vs. multi-state LAN sharing (and the BE wasn't local.) But I never backed out those dynamic lookup tables once the local BE server was set up because by then I had bigger maintenance issues to resolve. I CAN tell you that without what amounted to a 5000:1 speed difference between FE and BE, I would never have done it.

 

[GPGeorge]

Spoiler:

The external folder can be different than the one used for the FE repository or the BE shared file. THAT folder doesn't have to have DELETE privileges for users. Only the BE folder needs full-on MODIFY privileges. As to "the user cannot inadvertently delete"... if you have users who don't honor company-imposed rules on keeping their mitts away from infrastructure, invest in some barbed-wire bullwhips. Inadvertent deletion can only occur when a user is exceeding his/her authority OR when the code has a bad flaw in it. In the first case, find a way to publicly punish that person. In the second case, whose fault is that?

Keeping multiple-user data in a shared FE file that is downloaded to each user's PC might seem reasonable for performance issues, but if ANY FIELD in that FE's user-oriented table gets changed in the BE, you now need to find a way to detect the change and then to reconcile the shared table vs. the master table. You just bought yourself a ton of overhead. Not only that, but TECHNICALLY that FE-side table now violates normalization even if the BE-side table does not. The question related to normalization is this: You now have two fields holding the same data. What other field do you consult to determine whether to refresh from the BE or use the copy in the FE? Which field is definitive? If it is time-sensitive, which copy contains the deciding date? And why do you continue to use the other copy when you know it ISN'T definitive?

I once did something similar to this, but NOT with user data. We had a lot of lookup tables, SOME of which might have records added to them during a business day, but it was a VERY rare event. I put copies of the lookup data in the FE along with a date marker to show when that lookup table was last refreshed. If you were about to run procedures that would use the lookup, the code would compare the dates for the FE and BE sources and would, if needed, refresh the sources before continuing.

This was NEVER anything but lookup/translation data, which 99% of the time was static. When the app started, the lookup tables in the FE got refreshed and usually stayed that way. It was needed because of horrific speed differences in FE vs. BE. Then we finally got the speed issues resolved by being allocated a VM to act as a back-end that could be LOCALLY shared vs. multi-state LAN sharing (and the BE wasn't local.) But I never backed out those dynamic lookup tables once the local BE server was set up because by then I had bigger maintenance issues to resolve. I CAN tell you that without what amounted to a 5000:1 speed difference between FE and BE, I would never have done it.

I might put configuration tables in the FE along with static lookup tables. In Northwind, for example:

This SystemSettings table illustrates why you can't do so, however, for all tables. The TaxRate fields are standard, but ShowWelcome and FirstTimeProcessing are user specific. LastResetDate would be systemwide, but also updateable.

The Strings table contains standard strings used in Message Boxes, etc. and would not change from user to user. Plus there are two tables unique to the template, Learn and Northwind Features. Again, though, these would not be user specific.

Not shown would be other config tables that are system wide as opposed to user specific.

And, of course, a custom ribbon table, USysRibbons, would be system wide, not user specific.

 

[The_Doc_Man]

This SystemSettings table illustrates why you can't do so, however, for all tables.

I only did what I did because of (a) a MASSIVE speed issue and (b) the tables in question were 99% static so could be refreshed once and ignored most of the time. The only tables that got this treatment were translations/lookups of factors that were ALMOST immutable. Truly immutable lookup tables could easily go in the FE without normalization issues IF you had speed issues. My problem was the "ALMOST immutable" issue. Anyone else who wants to consider this, just remember. If your problem isn't due to the speed of the network between FE and BE then the approach I used was wrong, wrong, wrong. It was necessitated by severe physical network slowdown issues. Thus speaks the voice of experience.

 

[JohnPapa]

You can also put Temporary tables in the FE. Sometimes Temporary tables are used as the record source for forms and reports. The Temporary table is filled in with the relevant data. Yes you can skip Temporary tables, but many times they help debugging since you have a clear picture of the contents of the form and report and why they may or may not work.

 

[Pat Hartman]

The FE has its advantages. The user cannot inadvertently delete the external folder and if the pictures are in the FE it is easier to delete, add. update the contents. In our case we have found it extremely helpful. Other people may not find it helpful.

So when UserA updates some data in his copy of the FE, how does that get pushed to every other user and their personal copies of the FE?

Remember, in a properly structured application, each user has his own personal copy of the FE which is replaced each time the app opens or whenever the app opens and a version change is identified. The FE is NEVER shared the way the BE is shared.

 

[JohnPapa]

So when UserA updates some data in his copy of the FE, how does that get pushed to every other user and their personal copies of the FE?

Remember, in a properly structured application, each user has his own personal copy of the FE which is replaced each time the app opens or whenever the app opens and a version change is identified. The FE is NEVER shared the way the BE is shared.

"UserA" does not update his copy of the FE. He may use the Static data common to all users and the Temporary tables that can be helpful to creating Forms and Reports.

The users may operate on different versions of the FE, assuming the FE is properly constructed so that when it is copied to a specific user it performs the necessary changes, if any to the back end. Remember we are talking about a local DB. There is no need to update all users with the new FE when there is a change. This way you can test new features on a handful of users prior to releasing it to all users.

The updating of the FE can happen in various ways. One way is not automatic. Let me give an example. If we want to add a new field in the BE and new functionality, we can change the FE to perform this task and update the FE of the specific user. The rest of the users need not be updated at this point. At the latest they will be updated when they need to renew their subscription.

 

[Pat Hartman]

Just trying to clarify. The common data in the FE is not ever updated because that would make the FE's give different results to different users. Risky but your choice.

 

[JohnPapa]

Just trying to clarify. The common data in the FE is not ever updated because that would make the FE's give different results to different users. Risky but your choice.

The data in the FE is basically the same for all users. As I mentioned not all users have the latest FE, but this does not affect their operation. If all clients get the latest FE , they will have the same FE. There is no risk.

 

[Pat Hartman]

The data in the FE is basically the same for all users. As I mentioned not all users have the latest FE, but this does not affect their operation. If all clients get the latest FE , they will have the same FE. There is no risk.

People with no experience read these replies. I hope none will make the mistake of sharing the FE because you are advising them to put shared data in an FE rather than in the BE where it belongs.

Thanks for the participation so far. I don't believe I can add anything else to the discussion.

 

[JohnPapa]

People with no experience read these replies. I hope none will make the mistake of sharing the FE because you are advising them to put shared data in an FE rather than in the BE where it belongs.

Thanks for the participation so far. I don't believe I can add anything else to the discussion.

I apologize for being unable to explain a simple thing.

1) In the FE you can have data common to all clients. You can have this data stored in the BE, but since it is not specific to a specific client and does not change, the FE is preferable to the BE. As an example, you can store all the graphics/pictures used by all clients.
2) You can also have in the FE temporary tables that can be used by each client to easily manipulate and create forms and reports. Usually when you use a temporary table it is cleared and then filled in with the client's data during the process of creating a form or report.

 

[Pat Hartman]

I apologize for being unable to explain a simple thing.

Application data does not belong in the FE no matter how slowly you explain why you think it does.

Temporary tables cause bloat. There are better solutions.