Solved Create table by VBA in accde

[zelarra821]

Hi, guys.

I want to create a table by VBA in a accde file, but I get an error.

I attach two files.

First one is accdb file. It works fine.

But if I convert to accde, I get an error.

I don't know what's wrong.

Can somebody help me?

Thanks.

 

[xavier.batlle]

Hi zelarra821,
In the ACCDE the error raises in the line:

Application.RunCommand acCmdSave

If I'm not mistaken this instruction saves the current object, and it can only be used on ACCDB files.
If you comment or delete this line, the table is properly created in the ACCDE file but not shown until you refresh the view or restart the application.

 

[zelarra821]

refresh the view

How do I get this?

 

[xavier.batlle]

How do I get this?

Try using Application.RefreshDatabaseWindow :

Private Sub CmdCreateTable_Click()
  
        Dim dbs As Database
  
        ' Modify this line to include the path to Northwind
        ' on your computer.
        Set dbs = Application.CurrentDb
  
        ' Create a table with two text fields.
        dbs.Execute "CREATE TABLE ThisTable " _
            & "(FirstName CHAR, LastName CHAR);"

        dbs.Close
      
        'Application.RunCommand acCmdSave
        Application.RefreshDatabaseWindow

          
End Sub

 

[zelarra821]

Thanks a lot.

I share final result.

 

[zelarra821]

I need this table to create backups, so I can store backups name.

Therefore, users don't use this table.

 

[zelarra821]

The database I attached is just an example of something more complex that I have. So, a split so I can get to the problem.

I have a database configuration form so that the user can add a favicon, change the file folder path, the colors of the database, or create backups (and choose how many to keep).

When the user activates backups, this table is created to store the name of the backup (date plus database name). This table is automatically updated every time a new backup is created, deleting the old backup from the folder.

 

[June7]

I agree. These user settings is data. Store data in a backend table. Can associate with user's Windows login USERNAME.

 

[zelarra821]

Hi guys.

One moment please.

First of all, I don't like written communication because it lacks tone, body language, etc., and can lead to misinterpretations.

So, Pat, I'm reading you and in my head I see you shouting (with an Andalusian accent):

"Have you seen what this madman is doing? Have you seen it the same as me?"

But I know that's not the case.

Therefore, I want to clarify that although I talk about users, in reality we are the Holy Trinity, that is, a friend from the village to run four olive farms, a friend to run four crazy massages and me to run the books I read.

As you can see, nothing to do with users of the Ibex 35 or the New York Stock Exchange.

I know that now you will tell me all the advantages of the FE and BE, and I will thank you as I have always tried to do with all the help you have given me and that has made me learn so much.

I have used them in specific cases, but these people (me too) need a simple, home-made database, they don't need to have "two databases" because it would be confusing.

I really appreciate your comments, really.

Greetings.

 

[zelarra821]

I understand you, but I don't want to have "two databases", and neither do the other two people, because they have little knowledge of computers, and it would be a mess to make them understand. That's why I decided to do it this way, because for me it has no consequences, since I've been doing it this way for many years and I've never had any problems with myself or anyone else.

 

[zelarra821]

Thanks.

 

[JohnPapa]

Again, I say, we don't keep data in the FE. Why? because the FE is completely replaced every time you release an update. We especially do not create new objects in the FE. At least keep the log in the BE.

PS, I do keep a log in the FE but it is MY programming log. I update it when I update the FE. This is not user data.

You can keep data in the FE that is common to all users.

You can also keep a list of all your clients together with the date when their subscription ends. When your client renews that date will be updated. T

 

[zelarra821]

Thanks.

 

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

 

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

 

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