Good day!
I heavily use:
dim db as dao.database
dim rst as dao.recordset
set db = db name
set rst = Db.OpenRecordset(recordset)
set rst.index = xxx
............ etc.
Can I declare all these values once only and then refer to them within my code?
thanks in advance
Yes you can, in a Standard Module you can write:
Public g_db as dao.database 'g_ prefix for global objects
and then set it in your startup code. I do this in my code.
For recordsets it can be more tricky because you may sometimes want to keep them open for more than the current procedure, but if you're careful then you can declare the public recordset the same way as I showed for database.
The convenience of typing less isn’t very important to me—what matters far more is writing clear, understandable code that everyone can follow.
You might also want to read up on self-healing objects/properties.
PS. I just noticed this thread is in the Word forum. Although the suggestions still apply, I just wanted to confirm. Are you using these variables in a Word document?
The Recordset seems like a really bad idea. If you plan to reuse the variable but "reload" it.
If you really plan to have one recordset and never reload it that would be OK, but cannot envision a database where that could be of any use.
If that is a generic place holder variable then the chance of making a mistake seems really high.
So you have a global RST variable and you set it to some sql string.
Then later on another module you forget to set it to the new sql string and start working with it. You will likely get all kinds of errors calling fields that do not exist or worse bad data.
I could see maybe a group of recordsets that I set once and reuse. I cannot envision a single Recordset being used all the time.
I could see something like a family of globals
rsActiveEmployees
rsInActiveEmployees
rsTotalRevenueByEmployee
So if you are talking about a single recordset variable that you reset, that sounds like a disaster waiting to happen
If you are talking about a single recordset that gets used all the time, I cannot see much utility.
Bottom line when you use a recordset you really want to return the minimum records and fields.
I see people return all the records then when they loop it have a bunch of if checks.
Code:
if RS!Active = true then ...
if RS!Position = "Manager"
code
end if
end if
Instead of "Select field, field2 from tblEmployees where Active = true and Position = 'manager')
Technically you can do that.
But, as others in this thread, I strongly advise against doing so. You change the variables from being exclusive to the procedure they are declared in to global or module level variables, which can be accessed from a wide variety of procedures. Then previously independent procedures share interdependent common state. This allows to introduce subtle errors, which are hard to analyze and fix.
The only "global, persistent" variable I would see as useful is the DAO.Database item which lets you do action queries for which the DB.RecordsAffected property is often an issue. Also, there is a slight speed advantage with a predefined database pointer. Or globally persistent TempVars and Dictionary objects. I'm with the others who suggest that recordset variables are probably not so good UNLESS that recordset is used for something that would have a potential scope of the entire duration of the session. Like a persistent recordset for a logging or auditing function to record what someone was doing.
Yes you can, in a Standard Module you can write:
Public g_db as dao.database 'g_ prefix for global objects
and then set it in your startup code. I do this in my code.
For recordsets it can be more tricky because you may sometimes want to keep them open for more than the current procedure, but if you're careful then you can declare the public recordset the same way as I showed for database.
The convenience of typing less isn’t very important to me—what matters far more is writing clear, understandable code that everyone can follow.
You can have an opening macro which contains a RunCode action, naming a public function. It doesn't matter what the function returns, but for some reason Access requires the target of a RunCode must be a function.
The function can call however many routines you want. It can perform operations on recordsets. Note that the macro could possibly be triggered at a time when no forms are open just yet, so don't try to diddle with forms.. Always remember to exit the macro cleanly.
