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.

 

[Pat Hartman]

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

Creating and deleting (I presume) tables in a FE is poor practice. It causes bloat. Plus the user should never be updating the FE at all. If you have temporary tables that need to be replaced periodically, then use what we refer to as a side-BE which will only hold the temporary tables and where the whole database is replaced each time you need to refresh the temp data, not just the table.

We can add more detail if necessary.

 

[zelarra821]

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

Therefore, users don't use this table.

 

[Pat Hartman]

Why are you creating a backup table in the active database? Do you just keep making more tables or do you only ever make one in the FE? What happens when the FE is updated and the modified version is replaced? why are you backing up ANYTHING in the FE this way. Once you deliver the FE, it should NEVER be changed and therefore does not require a backup. Only the BE requires active backups. Before you distribute the FE, you make a backup of the FE and store it in a safe place. A backup on the fly is never necessary.

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

If you "know" how to do it right, why persist in doing it wrong? Whatever floats your boat I guess. I have lots of scars acquired over 50+ years of designing and developing software. I can assure you that doing it right is not any more difficult than fixing mistakes later and doesn't leave scars

I even do it "right" when building for myself. I learned my lesson the hard way.

 

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

 

[Pat Hartman]

because for me it has no consequences

The consequence is that you become responsible for the data if they need updates. Make whatever excuses you like. Do whatever you want. When you develop software for others, you are placing yourself in the role of expert and that is why we always give you the expert advice. Ignore it if you know better.

 

[zelarra821]

Thanks.

 

[Pat Hartman]

You're welcome. Good luck.