Adding VBA results to a table 1

[puforee]

I am using some code Andy directed me to....to find out the user/s computer names...for those currently using the DB...see code at bottom. I now need the code modified to take the Computer_Name and place it in the "User Tbl".

Can someone help me change the Debug Print statements so I can place the data in the table. Or if there are any other actions I should take. I only need the Computer_name field. Thanks,

------------------------------------------------------------------

Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

Set cn = CurrentProject.Connection

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the current database.

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend

End Sub

 

[puforee]

OK. I moved the DB and the backend to my computer. I set the code to its new location. When I run the code I get an error....

Run-time error '3734':
The database has been placed in a state by user 'Admin' on machine 'A4826682' that prevents it from being opened or locked.

I assume this to mean the DB is already open...which it is.

HERE IS THE HELP TEXT.
This error occurs when the new passive shutdown/connection control is being used. This error message indicates that a user set the database in a mode that will prevent other users from opening it.

I found one db setting that might cause this. I changed the setting and restarted the DB. Now when I run the code I get past the Set dgs step. It now fails at the dbs.Execute strSQL step indicating Run time error '3075'..Syntax error in string in query expression "A4826682' The expression you typed is not valid for the reason indicated in the message. Make sure you have typed field names and punctuation correctly, and then try the operation again.

I read your latest post..sorry about the lack of explaination. This post should answer most of your questions. I am going to google now.

 

[puforee]

At this time I believe it does not like the Syntax for:

strSQL = "INSERT INTO Usertbl(ComputerName) " _
& " VALUES(" & Trim(rs.Fields(0)) & ")"

I am very new to this...can you explain what all the puncuation is for. The Syntax is in the query expression with "A4826683',

Note the coumpter name shows, in the syntax error with open " and closing '. Of course this is just the error report.

 

[puforee]

I can't remember if I told you. I got past the file location issue and I am back on the
dbs.Execute strSQL Sytax error. This is what I am refering to in the last post.

 

[puforee]

Andy,

Would it be inappropriate to create a new thread asking for help with the Execute SQL...Syntax issue? You have helped to a great extent but could it be time to involve a bigger knowledge base.

I don't want to offend you because you have been a great help so far. :-D :-D

 

[Andrzejek]

Well, here is a possibile problem....
What is the name of your table? Simple question, but so far I was under the impression that it is [tt][blue]Users_tbl[/blue][/tt], but you show me a code with:
[tt]
strSQL = "INSERT INTO [blue]Usertbl[/blue](ComputerName) " _
& " VALUES(" & Trim(rs.Fields(0)) & ")"
[/tt]
so is it User_tbl or Usertbl?
What's in your Access db?
Go to Access' query builder (I think that's the name of it), drag the table in question and look at the name of it. It is important, all the punctuation, underscores, dots, spaces, etc.

And the punctuation:
If you have a string called strSQL and you want to assign an empty value to it, you do:

strSQL = ""

If you wan it to have a value you do:

strSQL = "puforee" or
strSQL = "This is a value in my String"

If you want to use a continuation of a string, use _ (underscore) and an & sign:

strSQL = "Ths is a value " _
& "in my String"

and if you wan to put a variable in the middle of a string, your do:

strSQL = "Ths is a value of " & SomeVariable _
& "in my String"

Then you want to have a ' (single quote) you need to do that, too. Or a Date with # around it. This is what is required by SQL syntax.

Have fun.

---- Andy

 

[puforee]

I renamed my table to Usertbl because I still had a space in the old name.

If I create a query as you suggested and then look at SQL vies I get:

SELECT Usertbl.ComputerName
FROM Usertbl;

So, my current naming convention in our code is good.

Function ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Dim dbs As Database

Set cn = CurrentProject.Connection
Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
'"", rs.Fields(2).Name, rs.Fields(3).Name

' Modify this line to include the path and name
' to your Access data base
Set dbs = OpenDatabase("C:\Documents and Settings\xjjs8542\Desktop\ED\DB\Working\Test ED Rev 1.X.mdb")

While Not rs.EOF
strSQL = "INSERT INTO Usertbl(ComputerName) " _
& " VALUES('" & Trim(rs.Fields(0)) & "')"
Debug.Print strSQL
dbs.Execute strSQL
rs.MoveNext
Wend

dbs.Close

End Function

But the indication when it is run is that something is wrong with the Syntax in our INSERT statement...at least that is what the error message is saying... Syntax error in string in query expression "A4826682'. Help wants us to verify all the text is spelled correctly and the punctuation is correct.


strSQL = "INSERT INTO Usertbl(ComputerName) " _
& " VALUES('" & Trim(rs.Fields(0)) & "')"


Immediate screen shows:

INSERT INTO Usertbl(ComputerName) VALUES('A4826682 ')

I won't change anything else without informing you.

 

[Andrzejek]

Let's add a Spece between Usertbl and (ComputerName) so you will have:
[tt]
strSQL = "INSERT INTO Usertbl (ComputerName) " _
& " VALUES('" & Trim(rs.Fields(0)) & "')"
[/tt]

Have fun.

---- Andy

 

[puforee]

I know you are going on vacation...and for today my brain is dead.

We still get the error...same place, using your new code.

Have a good vacation.

 

[puforee]

Andy...I found a different piece of code that returns the actual UserID. And, I finaly got the SQL to work. This code returns the Network UserID and stores it in my table. I still have some clean up work to do but I have the basic problem solved. I want to thank for all your help along the way. Hope the vaction was good.

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
Dim strName As String
Dim strSQL As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
strSQL = "INSERT INTO Usertbl (ComputerName) " _
& " VALUES ('" & Trim((fOSUserName)) & "')"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
MsgBox ("Welcome to ED, " & (fOSUserName))
'Debug.Print fOSUserName
Else
fOSUserName = vbNullString
End If


End Function

It does everything thing I need a places the UserID in the user Usertbl. I have a little tweaking to do to get the field name to match the value and to suppress some dialog boxes that show, but I know how to do that.

Again I appreciate your valuable input.

Thanks,

John

 

[Andrzejek]

Or you can simply do:

strSQL = "INSERT INTO Usertbl (ComputerName) " _       
  & " VALUES ('" & UCase(Environ("USERNAME")) & "')"        
'Debug.Print strSQL        
DoCmd.RunSQL strSQL

You may want to change field ComputerName to UserName

So, the [tt]DoCmd.RunSQL[/tt] was the answer....

Have fun.

---- Andy