I stumbled across the idea of defining certain items as properties instead of functions and I like the idea incorporating intellisense and other things that will keep my brain slightly more organized.
However, before I jump fully in I want to make sure that the following is still possible:
SQL:
CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
" WHERE UserName='" & GetDomainUsername() & "' AND tblUsers.Active=True;"
You can see the use of functions. Would I be able to use a property similarly? i.e. replace GetDomainUsername() with modUserControl.WinUser
Hi. My first impression is yes, you should be able to do that. As long as the property is accessible (public), it should then be available to your code.
You could make them TempVars also. Personally, I use a hidden form and hold all application wide variables there. I adopted that method prior to the introduction of TempVars in order to get rid of public variables. The hidden form method also makes it much easier to test since I can make the hidden form visible and change the value so I can easily work through multiple test scenarios.
@MajP Isn't he concatenating the variable in a string? That isn't the same as embedding the class property. Although I do see the inconsistency in the query where one of the function calls is embedded and the other is concatenated.
Let's look at what ACTUALLY happens when you execute the line of code you gave us. I'm being pedantic so that my answer will be clear when I'm done. That line is executed in VBA context, which has sufficient vision to see forms, controls, modules, and variables provided they are PUBLIC with respect to the place where that code is executed. SO...
The CurrentDB.Execute is parsed. The call template for that method of CurrentDB allows two arguments, the first of which must be either the name of an SQL object (a named query, e.g.) or a string containing the SQL itself.
The parser next sees a quote mark, which means "Literal string follows." So it starts accumulating the string until the quote near the right end of that first line. The line still isn't exhausted. VBA sees the ampersand which implies another string is coming. Then the underscore, which says that something is coming on the next line. Then you hit the end of the line but with expectation of a continuation line.
The next line starts with another quote ("another string is coming.") You supply that string, but then you have that sequence in which you break the string, insert ampersand, a function, another ampersand, and another string. You have that function outside the strings. It gets evaluated to return its string (value). Concatenation occurs, double-ended because ampersands before and after the thing that WASN'T a string. At that point the string reads "whatever was the first part" plus "whatever was returned by the function" plus "whatever was the second part of the quoted string." At the end of that sequence, you have a complete string, which will fulfill the 1st argument required for the .Execute method. The 2nd argument is optional so you are good.
At this point, Access from its VBA context now passes the string to the SQL processor, probably ACE but just as well could have been something else. Let's just say ACE for sake of discussion. The most important fact to remember here is that VBA and Access are in their own chunk of memory, but the SQL engine is another process or sub-process with different memory. Physically different, non-shared memory. Windows rules on process separation mean that Access and ACE are different processes that CANNOT SEE EACH OTHER (except through the control interfaces.)
Now the SQL parser kicks in on the string that was passed to ACE. It sees the UPDATE, a table name, a SET clause with two parts (one of which is a function), and a WHERE clause with two parts that are at this time literal values - because the substitution occurred while in VBA context.
ACE cannot yet operate on this because it has to decide if it can see the GetComputerName() function that was passed in to it within a quoted string. And here is where I can now specifically and directly answer your question.
Anything visible to VBA can be used including your module property if you are going to substitute it because VBA can see anything that is public. BUT if you enclose the property in quotes, VBA cannot perform substitution and therefore SQL has to look for that item. But that item might be in physically different memory and thus not visible to the SQL parser.
So... YES, you can use your property of a module IF that module property is visible to VBA AND you substitute it into the string. This is why we often suggest that you build the SQL string for a .Execute separately from the actual .Execute line. That way you can debug or DEBUG.PRINT that string to see what you actually tried to do.
The OP asked this if they could replace G etDomainUsername() with modUserControl.WinUser. since the only modules having properties are classes the answer is no. Only functions.
Unless I am misunderstanding how public properties work, I just gave the exact scenario @JMongi asked about, and it worked for me. I used the self-healing property example from my website. Code Snippets (accessmvp.com)
Here's the code I used:
Code:
CurrentDb.Execute "UPDATE UserLog SET LoginDate=Date(), Username='" & modUser.Username & "' WHERE LogID=7", dbFailOnError
@JMongi Regarding the above statement. If you start out with the name of the module, you should get Intellisense for all the members of that module. For example, in the below image, the first one (GetUsername) is a function, and the second one (Username) is a property.
Unless I am misunderstanding how public properties work, I just gave the exact scenario @JMongi asked about, and it worked for me. I used the self-healing property example from my website. Code Snippets (accessmvp.com)
Here's the code I used:
Code:
CurrentDb.Execute "UPDATE UserLog SET LoginDate=Date(), Username='" & modUser.Username & "' WHERE LogID=7", dbFailOnError
I had no idea that properties exist in standard modules. I always thought classes only. Learn something everyday. I have never seen this done, and cannot understand the utility as it relates to a standard modules. Normally done in a function. Only utility I can think of is to force some processing in the Let/Set.
I had no idea that properties exist in standard modules. I always thought classes only. Learn something everyday. I have never seen this done, and cannot understand the utility as it relates to a standard modules. Normally done in a function. Only utility I can think of is to force some processing in the Let/Set.
The answer to that title is yes it can be used in a SQL string, but not how it was described.. You can use reference a property in a SQL string if you reference it like you would a function. You cannot reference it with the module identifier.
Code:
CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
" WHERE UserName= WinUser() AND tblUsers.Active=True;"
But you cannot do
Code:
CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
" WHERE UserName= modusercontrol.WinUser() AND tblUsers.Active=True;"
However, as @Pat Hartman points out the example is bad because one function is embedded in the SQL and one simply concatenates the value. It is unclear what is being asked by the OP.
Code:
CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
" WHERE UserName='" & GetDomainUsername() & "' AND tblUsers.Active=True;"
You can replace GetDomainUserName() with modUserControl.winUser() but that is not very illustrative. A as Pat pointed out that has nothing to do with using a function in a SQL statement . That is just pure VBA returning a value and concatenating it to the SQL string. However you cannot "use a 'module.property' the same as a function in a SQL statement" and replace GetComputerName() with 'moduleName.property'. This will not work.
"...computer = modUserControl.CompName...". However " .. computer = CompName()" will work
However, a property in a standard module seems kind of duplicative, as It acts no different than just a function. It does not provide the benefits that properties provide like they do in a class. I guess it could bring some value if you use it in pairs with a let/set and get, and then you do some validating in the Set/let. The properties are not encapsulated like in a Class, where you can only reference them through the class.
The Standard Module Object Generation (SMOG) add-in was created by Alan Cossey to make as much use as possible of both compile-time error-checking and Intellisense in Access.
Option Compare Database
Option Explicit
Private m_strPrpTest As String
Public Property Get PrpTest() As String
PrpTest = m_strPrpTest
End Property
Public Property Let PrpTest(ByVal strPrpTest As String)
m_strPrpTest = strPrpTest
End Property
In the Immediate Window, set the value:
Code:
PrpTest = "Task 2"
In a query:
SQL:
SELECT TaskID, Task
FROM tblTask
WHERE Task = PrpTest();
Run the query: Query1
TaskID
Task
2
Task 2
Or in code:
Code:
Function PrpQueryTest() As Boolean
Dim strSQL As String
strSQL = "SELECT TaskID, Task FROM tblTask WHERE Task = '" & basTest.PrpTest() & "';"
With CurrentDb.OpenRecordset(strSQL)
Do While Not .EOF
Debug.Print .Fields(0), .Fields(1)
.MoveNext
Loop
PrpQueryTest = .RecordCount > 0
.Close
End With
End Function
@cheekybuddha, I went back and refined my post to be more exact.
As you pointed out you can call a property as you stated, just like a function
Code:
SELECT TaskID, Task
FROM tblTask
WHERE Task = PrpTest();
You cannot include the module identifier as the OP suggested "GetDomainUsername() with modUserControl.WinUser" within the SQL. Even if you could add the module identifier it provides no value since standard module properties are not protected by the module like they are in a class. You can just reference them without the module identifier unlike a class.
This will not work
Code:
SELECT TaskID, Task
FROM tblTask
WHERE Task = basTst.PrpTest();
As pointed out the concatenation is not illustrative of using a property within a SQL string, since the property is not within the SQL string. VBA is simply concatenating a value to a string and of course that can be done.
Code:
"SELECT TaskID, Task FROM tblTask WHERE Task = '" & basTest.PrpTest() & "';"
Similarly, this will not work when the equivalent is done in code:
Code:
Function PrpQueryTest() As Boolean
Dim strSQL As String
strSQL = "SELECT TaskID, Task FROM tblTask WHERE Task = basTest.PrpTest();"
With CurrentDb.OpenRecordset(strSQL)
' etc ...
Though this will:
Code:
Function PrpQueryTest() As Boolean
Dim strSQL As String
strSQL = "SELECT TaskID, Task FROM tblTask WHERE Task = PrpTest();"
With CurrentDb.OpenRecordset(strSQL)
' etc ...
Glad to see I lived up to my subtitle of "Pot-Stirrer"
Some clarifications and explanations are in order:
1. I asked this question because my experience crafting SQL statements directly is very light and I didn't want my inexperience or syntax issues to cloud the issue.
2. I also asked because it was the end of the day and I was not able to change the code myself and test at that time. I also mistakenly thought it would be a "simple" answer.
3. This SQL statement in particular was taken from part of a sample db on this website from @isladogs (sorry to drag you in Colin!). I don't believe the code was originally from him but I believe he modernized it, polished it and posted it here. It's code used for session logging that direct writes to the users table via SQL to change user status. I can't recall the exact sample db, maybe the one with the password security built in as well?
4. As @The_Doc_Man highlighted, integrating VBA functions and SQL statements are tricky with regards to making sure you have a correctly formatted string to pass to he SQL engine. It has been too long and I was not confident in remembering all of the delimiters between SQL and VBA as he highlighted.
5. If my question title was misleading/confusing I apologize. I used the terminology I read on other access websites. It's quite possible I used them incorrectly.
To address @The_Doc_Man a bit, the code behind the property would likely be useful for your analysis. Here it is:
Rich (BB code):
Public Property Get WindowsUser() As String
'Used to pull the current user's username
Static Username As String 'Persist as long as module level variable
If Username = "" Then
Username = CreateObject("wscript.network").Username
Else
'Return the existing username
End If
WindowsUser = Username
End Property
I like the idea of using a standard module property because I don't have to remember all of the function names I've defined. With a property I just have to remember the module name (in this case dbUser) and then intellisense will help me with the rest. It also means I know exactly where the code resides without having to remember which module or form or whatever contains the code.
As far as parsing together SQL strings, if there are a few principles when trying to craft a statement incorporating VBA I'm all ears. I only have other user examples to go by.
This utility has been provided to manage user passwords and also keep a user session log
www.isladogs.co.uk
It was originally based on an old MDB file by David Crake but in updating it a few years ago, I rewrote almost all of the code, added encryption etc, etc.
I normally use functions in standard modules rather than tempvars or properties but that's just my style of working rather than stating any one method is better.
If you haven't already done so, I recommend looking at the SMOG addin which might well be what you are looking for. There is an accompanying video on YouTube which is linked in the same article.
like the idea of using a standard module property because I don't have to remember all of the function names I've defined. With a property I just have to remember the module name (in this case dbUser) and then intellisense will help me with the rest
See post 8 by @theDBguy. This is not unique to a property. You can type the module name and bring up all methods. So in the example simply type moduser. Then select the function getusername.
Most of the time I do not know the name of all my functions or procedures, so simply type the module name and use intellisense.
Some value = somemodule.somefunction
Or to run a procedure
Somemodule.someprocedure
Actually, no it wouldn't. The question will always be whether the object containing the property is visible to SQL, which thus depends on where and how the reference is quoted. Your presentation of the Property Get code just adds a layer of the question "Is it visible from where I use it."
If you use concatenation while in VBA/Access context, you have far better odds of getting it through. If you leave it for SQL to resolve, much poorer odds. Doesn't matter what the code for that property looks like, it is all about syntax and visibility from the point where it has to be evaluated. No other factors apply to the example as originally shown.
If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...
