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.