Need Help Regarding vba code (1 Viewer)

[moin555]

I have a school's database. I want a code that I can run on the 25th of each month, and it should print the fee challan (invoice) for all students.
Also, if any student has an arrear, it should be updated and included in the next fee.

 

[CJ_London]

Before suggesting code - is the db opened every day of the year? Including Xmas day? Or is it open 24/7? And is the machine that hosts the FE on all the time and doesn’t go into sleep mode

Reason for asking is you need a trigger to start the code which might when the db is opened, or a timer event running in the background or a windows timer to open the db and run the code.

 

[moin555]

Before suggesting code - is the db opened every day of the year? Including Xmas day? Or is it open 24/7? And is the machine that hosts the FE on all the time and doesn’t go into sleep mode

Reason for asking is you need a trigger to start the code which might when the db is opened, or a timer event running in the background or a windows timer to open the db and run the code.

is it open 24/7

 

[CJ_London]

I don’t know - I’m asking you

If you are saying it is open 24/7 what happens with windows updates which require a reboot of the computer and do you have a UPS for computer and printer

 

[DHookom]

I have a school's database. I want a code that I can run on the 25th of each month, and it should print the fee challan (invoice) for all students.
Also, if any student has an arrear, it should be updated and included in the next fee.

Are you ok with running the reports by clicking a button on the 25th or do you expect these reports to be run unattended?

 

[The_Doc_Man]

The problem you just gave yourself is that NOW you have to remember if you already did something,... e.g. your "arrears" situation.

Your comments about accounts in arrears that trigger a specific type of update means that if you open the DB twice on the same day, you have to remember that you did something the first time that you don't want done again. I.e. double-charge of arrears. This makes it more complex.

If you have a power outage on the 25th of the month, you have to remember on every 25th of a month that you DID generate your output so that on the day of the power recovery after the DB was down on the 25th, you can remember that you didn't yet create your desired outputs. If it happens that you do some processing of updates during this output generation, you would again run in a double-processing case.

How many people can open this DB with the right to run that required output? If the answer is "more than one person" then you also have to allow for possible simultaneous actions that would trigger double-reports or double-arrears. This sounds like a situation that INVITES destructive interference in everyday operation.

Then there is the question of a database proposed to enjoy 24/7 activity, which I will CATEGORICALLY tell you is not going to happen. Every so often, because of memory leaks that clog up Windows scratchpad memory in something called Non-Paged Pool and, to a lesser extent, in Paged Pool, Windows MUST be rebooted. More directly, whether you like it or not, Windows WILL reboot - and not give you a choice - AND it will be the ugly type of reboot derived from a Blue Screen of Death. Having an Access app open during one of these forced reboots is the proverbial hole so big you could drive a Mack truck through it. This is one of the circumstance that KILLS databases - because if there is a pending update in this DB at the time that Windows takes the big dirt nap, you can corrupt your DB irretrievably. OK, admittedly Microsoft has improved the stability of Windows - but it still isn't perfect.

This concept - a 24/7 database - is fraught with peril.

 

[amorosik]

Having an Access app open during one of these forced reboots is the proverbial hole so big you could drive a Mack truck through it. This is one of the circumstance that KILLS databases - because if there is a pending update in this DB at the time that Windows takes the big dirt nap, you can corrupt your DB irretrievably. OK, admittedly Microsoft has improved the stability of Windows - but it still isn't perfect.

This concept - a 24/7 database - is fraught with peril.

Of course our friend Moin555 meant that he would like the code built with Access and the data on any db server
No one would be so crazy to keep important data on native Access files

 

[Pat Hartman]

Start by removing the multi-value fields from the tables and replacing them with proper tables. You need a Student table and a Guardian table (not father to be more generic since I assume, a child might not have a father or even a mother as his guardian). The registration table then links to the student table which links to the Guardian table. The student table has demographic information for the student. The Guardian table has demographic information for the Guardian. Usually the address information would be the same but there are circumstances where it could be different. Perhaps the Guardian (father) has a job which requires him to travel and so the student lives with his grandparents during the school year. Then to connect a registration with a course, you need a junction table which is how we implement a many-many relationship. The total cost belongs on the registration record. I don't know what the Fee table is. If that is monthly payments, maybe that should be its name.

When you have recurring processes, you either have a user run them manually or you set up some automatic mechanism to run the recurring process. There should be a table to log when the process runs each month so you don't accidentally run it more than once. OR, if you need to run it more than once, you need a way of backing out the previous try. Typically, you might have a query that runs whenever someone opens the database to determine the date and figure out if the monthly billing has run or not. It can then warn you when the automatic process didn't run so you can start it manually. This is a good idea regardless of whether your trigger will be automatic or manual. The questions regarding 24/7 operation are to ascertain whether or not you have a way to run this process without any human intervention. You also don't trigger this type of process ON the 25th. You trigger it for the first time the app opens on or AFTER the 25th and you need the run log table to make that feasible.

Do a little more work on the schema and answer the outstanding questions and we can zero in on a good solution for you.

 

[The_Doc_Man]

No one would be so crazy to keep important data on native Access files

Admittedly less likely, but even servers are subject to some variant of the crash problem I described. The last time I saw this actually happen it was on an ORACLE Enterprise Server database, version 5.something.

 

[amorosik]

Oracle 5 ??? Are we talking about a product from 1985 ???
I think the good versions are at least from version 8 (1997) onwards
But even a great db server could lose data if the hardware does not work properly

Let's do a simple test: in your life how many db servers have you seen losing data and how many mdb/accdb?
In my personal experience: 2 (hardware server with faulty ram) / dozens of times (multiuser access)

 

[Pat Hartman]

@amorosik While a "real" RDBMS is required for maximum data security, not every small company has the means to run a database server so they may end up relying on Jet/ACE. No one is arguing with your opinion that Jet/ACE are inferior but then there is the real world.

 

[amorosik]

I don't think there's any discussion
I'm writing obvious things, which I believe are indisputable
Having abandoned the use of mdb/accdb files for data for several years, I was curious to understand the proportion between data lost on db server and on mdb/accdb files

 

[moin555]

is it open 24/7

may be in office timing

Are you ok with running the reports by clicking a button on the 25th or do you expect these reports to be run unattended?

yes

 

[moin555]

Start by removing the multi-value fields from the tables and replacing them with proper tables. You need a Student table and a Guardian table (not father to be more generic since I assume, a child might not have a father or even a mother as his guardian). The registration table then links to the student table which links to the Guardian table. The student table has demographic information for the student. The Guardian table has demographic information for the Guardian. Usually the address information would be the same but there are circumstances where it could be different. Perhaps the Guardian (father) has a job which requires him to travel and so the student lives with his grandparents during the school year. Then to connect a registration with a course, you need a junction table which is how we implement a many-many relationship. The total cost belongs on the registration record. I don't know what the Fee table is. If that is monthly payments, maybe that should be its name.

When you have recurring processes, you either have a user run them manually or you set up some automatic mechanism to run the recurring process. There should be a table to log when the process runs each month so you don't accidentally run it more than once. OR, if you need to run it more than once, you need a way of backing out the previous try. Typically, you might have a query that runs whenever someone opens the database to determine the date and figure out if the monthly billing has run or not. It can then warn you when the automatic process didn't run so you can start it manually. This is a good idea regardless of whether your trigger will be automatic or manual. The questions regarding 24/7 operation are to ascertain whether or not you have a way to run this process without any human intervention. You also don't trigger this type of process ON the 25th. You trigger it for the first time the app opens on or AFTER the 25th and you need the run log table to make that feasible.

Do a little more work on the schema and answer the outstanding questions and we can zero in on a good solution for you.

yes

 

[Pat Hartman]

I'm not sure that statistic exists. I have several clients with mission critical Access databases. One uses SQL Server, the others use ACE. None have lost data but that is probably due to a sound backup strategy and a solid. wired, LAN connection.

 

[CJ_London]

yes

Which? One word answers will not get you very far.

may be in office timing

Vague to say the least and does not answer the question

If you are struggling with English, write your responses in your native language then use an online translation service to translate to English

 

[Silversee]

No question that a server DBMS is more robust and more scalable, but companies have been running production applications on LAN-based products for literally decades.


With the right network infrastructure, appropriately scaled projects, and proper backups, there is no reason you should automatically lose data.

Part of what makes a server DBMS more resilient is that they are often managed professionally. Similar due diligence is needed for any important project, regardless of the technology employed.