Help with found code

[Stephen sjw_ost]

Ok, so I thought I could reduce it down to 1 item being looked at by doing
this;

strSQL = "INSERT INTO T_CurrentUsers" & _
"(COMPUTERNAME)" & _
"VALUES(" & rs.Fields(0) & ")"

I am doing this to get just the PC NAME. But I am still getting a syntax
error on the INSERT INTO statement when the code gets to the 2nd cm.Execute.
And the Debug.Print is giving me this in the Immediate Window;

INSERT INTO T_CurrentUsers(COMPUTERNAME)VALUES(WSUSJXC2B2A169A
)

So I now have 1 listing with the single value that I want, but as you can
see there is a very large space between the end of the PC NAME
"WSUSJXC2B2A169A" and the closing parenthasis. I am not sure if this makes
any difference.

Please be pateinet with my lack of understanding. I consider myself to be
only a beginning intermediate, if that. I do not know the ends and outs of
code as well as I would like so when you explain things, please help me to
understand what you are trying to explain. If there is a way that I can
compair the results of this code to a static table to determine who is logged
into my DB then please help me to understand how to do that with this code. I
want to use this code.
Otherwise, what I am thinking is, this code will put the PC NAME of the
current user(s) into a table "T_CurrentUsers", within the same database of
which this code is written, which I will have a static table with all of the
possible PC NAMEs with a discription of the PC NAME, related to
T_CurrentUsers via a query. When the query is ran, it will give the results
based on the 2 tables via the relationship I will put into place. Is this
do-able with this code? If so, how?

Thank you for your patience, understanding and assistance.
Stephen

 

[Douglas J. Steele]

For one thing, you're missing the quotes:

strSQL = "INSERT INTO T_CurrentUsers" & _
"(COMPUTERNAME)" & _
"VALUES('" & rs.Fields(0) & "')"

Exagerated for clarity, that's

strSQL = "INSERT INTO T_CurrentUsers" & _
"(COMPUTERNAME)" & _
"VALUES( ' " & rs.Fields(0) & " ' )"

I'm not sure whether those are strictly spaces, or if there's one (or more)
special characters, most likely a Null character (Ascii 0). You could try
using Trim, and if that doesn't work, then use a function like:

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function

However, you seem to have missed my point that you do not need to use that
ADO code to retrieve the data from the LDB file. In my opinion, all you need
is the code from http://www.mvps.org/access/api/api0008.htm and
http://www.mvps.org/access/api/api0009.htm at "The Access Web"

When a user logs on, determine their computer name using the code from
http://www.mvps.org/access/api/api0009.htm and the user's network name using
the code from http://www.mvps.org/access/api/api0008.htm

Check whether that's the user name associated with the computer in your
table. If it is, you're done. If it isn't, you have to decide what to do. Do
you allow multiple users to use the same computer, or do you update the user
name currently associated with the computer? If you're going to allow
multiple users for the same computer, it would be something like:

strComputerName = fOSMachineName()
strUserName = fOSUserName()
If DCount("*", "T_CurrentUsers", _
"COMPUTERNAME ='" & strComputerName & "' AND " & _
"USERNAME ='" & strUserName & "'") = 0 Then

strSQL = "INSERT INTO T_CurrentUsers" & _
"(COMPUTERNAME, USERNAME)" & _
"VALUES('" & strComputerName & "', " & _
"'" & strUserName & "')"
CurrentDb.Execute strSQL

End If

If you're not going to allow multiple users, it would be something like:

strComputerName = fOSMachineName()
strUserName = fOSUserName()
If DCount("*", "T_CurrentUsers", _
"COMPUTERNAME ='" & strComputerName & "'") = 0 Then

strSQL = "INSERT INTO T_CurrentUsers" & _
"(COMPUTERNAME, USERNAME)" & _
"VALUES('" & strComputerName & "', " & _
"'" & strUserName & "')"

Else

If Nz(DLookup("USERNAME", "T_CurrentUsers", _
"COMPUTERNAME ='" & strComputerName & "'"), vbNullString) <> _
strUserName Then

strSQL = "UPDATE T_CurrentUsers" & _
"SET USERNAME = '" & strUserName & "' " & _
"WHERE COMPUTERNAME ='" & strComputerName & "'"

End If

End If

If Len(strSQL) > 0 Then
CurrentDb.Execute strSQL
End If

 

[Stephen sjw_ost]

I have tried with and without the ' quotes. I still get the same syntax error
when the code gets to the 2nd cm.Execute. The spaces go into place even if I
leave no spaces at all. Once I move my cursor to the next line, my VB
explorer makes the adjustments. I guess to make the code "proper"? Any idea
what might still be causeing the syntax error?
I am using this code as a training exercise to help me understand how the
code works, how to create an update query in code as well as comprehinding
the various parts of this code, what it does and how it works. Can I ask of
you to help me understand what might be causing the syntax error and or why
it might be causing it? How tocorrect it?

I believe I understand now how to implement and work your alternate solution
and I appreciate the detailed examples that you've provided. They filled in
the gaps of what wasn't getting thru to me.
I would still like to finish what we have started with the original code,
get it to work, if you'll continue to help me?

Thanks again for all the help you have already provided.
Stephen

 

[Douglas J. Steele]

Please post the exact code you've got now, as well as indicating exact where
the error occurs (and the exact error message)