Forum Discussion
MS Access (Office 365) Command buttons do not work on a cloned DB
I have a bookkeeping application. Every year I copy and paste the DB with a new name and remove the data from the tables relating to the bookkeeping entries of the previous year. This time af...
I can't say for sure why VBA would stop working.
However, you have a serious design flaw here and that's a higher priority in the short run:
"...copy and paste the DB with a new name and remove the data..."
One of the most important features of an effective relational database application is that it is able to store HISTORY, history that can encompass years and years and years. Deleting data destroys history. For a bookkeeping application to destroy history is probably not in accord with good accounting practices.
Instead, you should keep all of your history for things like year-to-year accounting.
If you want to deactivate some records, you can easily do that by adding an Active field to the tables. This will be a Yes/No field. At the end of the year, run an update query to change those records from "Yes" (meaning active) to "No" (meaning not active).
Beyond that, if you have a valid bookkeeping application, every transaction already has a date field in it. You record a payment with the date of the payment. You record income with a date the income was received, and so on.
All you ever need to do is filter those tables for the records that apply to the CURRENT year, by adding a criteria to queries against that data which select current year records.
No more risky copy/paste/delete needed.
This is what I would really recommend you do.
===============
Now, one could guess that perhaps the new accdb has been copied to a new location and that location has not been designated as a Trusted Location. That's one possibility. As noted above, it would probably require a copy of the non-functioning accdb to be able to trouble-shoot it.
But again, the more appropriate action will be to stop this process and instead implement a standard relational database application approach that relies on filtering of records by the date.
BTW: if you've ever looked at accounting software like QuickBooks, you'll recognize that is how the professional bookkeeping software does it.
However, you have a serious design flaw here and that's a higher priority in the short run:
"...copy and paste the DB with a new name and remove the data..."
One of the most important features of an effective relational database application is that it is able to store HISTORY, history that can encompass years and years and years. Deleting data destroys history. For a bookkeeping application to destroy history is probably not in accord with good accounting practices.
Instead, you should keep all of your history for things like year-to-year accounting.
If you want to deactivate some records, you can easily do that by adding an Active field to the tables. This will be a Yes/No field. At the end of the year, run an update query to change those records from "Yes" (meaning active) to "No" (meaning not active).
Beyond that, if you have a valid bookkeeping application, every transaction already has a date field in it. You record a payment with the date of the payment. You record income with a date the income was received, and so on.
All you ever need to do is filter those tables for the records that apply to the CURRENT year, by adding a criteria to queries against that data which select current year records.
No more risky copy/paste/delete needed.
This is what I would really recommend you do.
===============
Now, one could guess that perhaps the new accdb has been copied to a new location and that location has not been designated as a Trusted Location. That's one possibility. As noted above, it would probably require a copy of the non-functioning accdb to be able to trouble-shoot it.
But again, the more appropriate action will be to stop this process and instead implement a standard relational database application approach that relies on filtering of records by the date.
BTW: if you've ever looked at accounting software like QuickBooks, you'll recognize that is how the professional bookkeeping software does it.
Thank you for your reply and I very much appreciate your suggestion. A follow up question, which is based on a perhaps unwarranted desire for year to year data segregation. What do you think about the idea simply cloning the tables?
Regarding my primary DB (not bookkeeping), which also has command buttons as well make s use of the on-click within controls, I did an experiment. I copied and pasted the DB. I had the same issue. Command buttons and on-click of controls within forms do not work.
Regarding my primary DB (not bookkeeping), which also has command buttons as well make s use of the on-click within controls, I did an experiment. I copied and pasted the DB. I had the same issue. Command buttons and on-click of controls within forms do not work.
I am not an expert in accounting, but I don't think it's standard practice to segregate data year-to-year, PHYSICALLY. My bank certainly doesn't do that, nor do standard off-the-shelf accounting packages. So, while I am not going to be adamant, it does seem to be out-of-mainstream practice. Cloning tables is roughly the same thing, IMO.
To focus, though, on this problem. I asked if the accdbs are both in a Trusted Location. It's also important to allow macros to run. Although the dialog refers only to macros, this does include VBA,
Although that's the most likely source of this problem (one or the other property, or both), it could be something else. Start there. See if that corrects the problem.
- Thank you very much! The issue has been resolved by "enabling all macros". I do appreciate also the instructive comments regarding the bookkeeping application, which I plan to implement.