Can I use a module property the same as I use a function in an SQL statement? (1 Viewer)

[JMongi]

@The_Doc_Man - I was about to make a long winded post because I didn't understand your caveats, but after rereading post #6, I think I realize why I caused such a distrubance! So, now, a different long-winded post

My original question was meant to be narrow in scope in that I was crafting it with the assumption that all other circumstances were equal (circumstances related to your "whether the object containing the propery is visible to SQL" comment). That question has been answered. A function that returns a string and a property that returns a string are the same (keeping all of the other caveats and circumstances in mind). A string is a string is a string.

Here is the code I posted for reference:

CurrentDb.Execute "UPDATE tblUsers SET LoggedIn = True, Computer = GetComputerName()" & _
" WHERE UserName='" & GetDomainUsername() & "' AND tblUsers.Active=True;"

But, in the actual code, two different string functions were used in two different ways (which I failed to notice). One was used in such a way that VBA evaluated the function and returned a string which was then used to concatenate a string for passing to the SQL engine. This is the one used in the WHERE clause. All well and good. That code ALSO uses a function as a function. This is the one in the UPDATE clause. This was passed as function (not an evaluated string) to the SQL engine with the presumption that the SQL engine can somehow communicate with VBA to get that translated into the string that it needs to work correctly. That is where (I think) The_Doc_Man is highlighting the moving parts that allow or don't allow that to happen.

@isladogs - Thanks for the post, I will check it out.
@MajP - Thanks for the SQL/VBA code bookmark.

The wisdom of splitting the SQL string generation via VBA from the SQL execution has become much clearer to me now.

If I'm missing anything in this, let me know please. Thanks!

 

[MajP]

In my opinion always evaluate the functions outside the SQL string you are building and always save the sql string as a variable so you can debug everything.

Dim strSql as string
strSql = ""UPDATE tblUsers SET LoggedIn = True, Computer = '" &
GetComputerName() & "' WHERE UserName= '" & GetDomainUsername() & "'
AND tblUsers.Active=True"
'because now you can debug the string and ensure your functions are working
and the string is proper
debug.print strSql
Currentdb.execute strSql

 

[JMongi]

@MajP - Makese sense.

A quick aside to wrap this up....

I see in the WHERE portion two criteria. The username field and the active field. Is there a reason why one uses the fieldname directly (UserName=) and the other references the table again (tblUsers.Active=)?

 

[isladogs]

Bearing in mind its based on my code, I'd better answer that....
There is no reason why tblUsers is needed before the Active field in the WHERE clause. It can be removed.

I also agree with creating a sql string so it can easily be debugged before execution
Using Dim strSQL As String would be better...otherwise its a variant

However, it isn't necessary to include the quotes around the function in the SET clause, though they are essential in the WHERE clause.
IIRC, using them in the SET clause will make the query less efficient i.e. slower, but I would need to test whether I have remembered that correctly.

The ( ) brackets can also be omitted