Re the first 2 questions. What you're asking about is called variable scope. IIRC, it's not enough to declare a variable Public in a standard module and expect it to be visible to the whole project - you have to do so in the right spot. While it's implied that this link is for Excel, the title suggest otherwise, plus I am certain the information applies to all of vba.
https://support.microsoft.com/en-ca/...r-applications
This link should also answer the latest question. Sounds to me like the class module is useless if there's nothing in it but "Option Compare Database" which leads me to point out that any project that doesn't include Option Explicit in every module is just asking for trouble. This should be turned on by default and added where it is missing.
Project level scope is risky IMHO, and public variables cannot also be static. You have to clearly understand the chances of processes possibly altering the value of a public variable and what could happen if it occurs. It might, for example, be very bad in a financial application. Depending on the application, table records might be more secure/reliable.
Last edited by Micron; 11-21-2018 at 05:04 PM.
Reason: wrong link
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.