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]

I am still trying to understand where things go in code. Not that I do NOT have spaces in mhy table and field names..do we still need the double quotes?

 

[Andrzejek]

"do we still need the double quotes"
Yes, you do need the double quotes

If you do:

strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & "')"
[blue]
Debug.Print strSQL[/blue]
cn.Execute strSQL

What do you get in the Immediate Window from the Debug.Print?
Can you copy it and paste in your Access to execute it?

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


If that would be me - I would like to know WHO and WHEN.
So my User_tbl would have:[tt]
ComputerName Text 50 char
UserName Text 50 char
Got_IN Date
Got_OUT Date[/tt]

If you can get the point of where users access your application (very first Form_Load of MainMenu or Splash Screen or something), you could do something like:

(This Delete statement is in case user crashed in the application and did not have a chance to exit it nicely)

strSQL = "DELETE FROM User_tbl " _
" WHERE ComputerName = '" & Environ("COMPUTERNAME") & "' " _
" AND UserName = '" & Environ("USERNAME") & "' " _
" AND Got_OUT IS NULL"

strSQL = "INSERT INTO User_tbl (ComputerName, UserName, Got_IN) " _
" VALUES('" & Environ("COMPUTERNAME") & "','" & Environ("USERNAME") & "', #" & Now & "#)"

And at the place where they leave my application I would do something like:

strSQL = "UPDATE User_tbl " _
" SET Got_OUT = #" & Now & "# " _
" WHERE ComputerName = '" & Environ("COMPUTERNAME") & "' " _
" AND UserName = '" & Environ("USERNAME") & "' " _
" AND Got_OUT IS NULL"

Have fun.

---- Andy

 

[puforee]

So, is this the complete code? Or is this placed somewhere in my original code. And, am I to assume...I need a field named UserName?

It looks like this would be good. (Table) It does contain important information.

I am currently using an autoexec Macro to call the function we have been working on...would I put all of this code in that function?

 

[Andrzejek]

No, it is NOT a complete code, it is just an idea, a few SQLs to show you what I would do to keep track of users who use my application, with dates and times of getting in and out.

I wouldn't put it into the code you are working on. As I stated, part of it would be somewhere at the beginning of your application (splash screen, log in, app start, etc), and another part would be on the way out - Exit application, bye-bye Form, etc.

Have fun.

---- Andy

 

[puforee]

OK. Sounds good...so, can you show me the complete code? Assume I am using Users_tbl
Computer_Name Text 50
UserName Text 50
Got_IN Date
Got_OUT Date

I say this because we are still getting the failure to my code with your modifications at the cn.Execute strSQL step.

I really appreciate the time you are spending with me.

 

[Andrzejek]

No problem, but I still do not know HOW your users run your application. (NOT this little function you/we are working on)

Let say I am one of your Users. HOW would I start your application? Where would I go to run it?

What I would see when I would start your app?

What would I do to STOP (get out of your) application?

BTW - I ask those questions not to be nosy, I just need to know it to let you know where to put certain code/logic. And I would like to get answers to ALL of those questions. Otherwise all what I can do is guess, give a 'shot in the dark', assume. And that will take a long, long, long time to get anywhere.....

Have fun.

---- Andy

 

[puforee]

The DB is geared to keep employee information and control various systems accesses. There are about 120 server shares, four main systems...etc. Supervisors use this DB to Request access for their employees.

OK as a user you would go to the front end application on one of our servers. Double click to open the application. Once in the application there is a opening screen switchboard of options. Depending on who the user is, one or more of the switchboard options may be disabled. The main employee screen can be selected by individual employees, by employee of a specific supervisor, or all. Once on their screen the normal employee would be limited to change ONLY his/her personal data. The Access data can only be changed by a Supervisor or Lead employee's. My backup person and I will be able to control everything.

The supervisor selects one or more of the many items on the screen. From the screen, they will email their request. When I go into the DB I have a Administrator selection on the switch board screen that will open only the employee's screens with request pending. I take it from there...completing the requests and setting the employee screen to completed. Also sending a Completed email from the employee screen.

So, you see, I need to know who is using the DB to be able to limit where the employees can go, and what they can do.

Does this help?

 

[puforee]

Sorry missed a question.
To get in...double click ICON and it opens the Swithboard scree.

To get out. Preferable they will click one of the "Exit Application" buttons..these run code to perform a closing function...outputting data to a different DB. But the big red X is still available..not sure how to turn it off.

 

[puforee]

Andy,

Have not heard from you...are we still good?

 

[Andrzejek]

Yes, we are still good

I am still not very clear if you are talking about just one application or several, set of, many applications.

I can not give you a 'ready to use' code because - believe it or not - I am not an Access guru. I do 'classic' VB 6 with ORACLE, and a little of VBA in Excel and Word. But logic and approach to these kind of problems and solutions is the same no matter which language you are using.

So if you figure out the best (one) place to detect where your users access your app (Splah sceen, log in, etc) you can write (insert) the information about it (ComputerName, UserName, DatTimeIn) into the table.

Then if you find a good place where users leave your app, you can UPDATE the (inserted) record with the DateTimeOUT.

This way you can keep track all the users in your app, know how often they access your app, how long do they stay in there, how often they crash, etc.

PS. I am going to be gone, on vacation, starting Friday. Be back on Nov. 20

Have fun.

---- Andy

 

[puforee]

OK...I can handle the where. What I still need to know...and I don't see it in your two SQLs...is what code should I use to get the data from the .lbd file. Will the code I am currently using, prior to the current sql, work to gather the data.

If it does where would I put..
strSQL = "INSERT INTO User_tbl (ComputerName, UserName, Got_IN) " _" VALUES('" & Environ("COMPUTERNAME") & "','" & Environ("USERNAME") & "', #" & Now & "#)"

And how would I reference the data that was collected by...
While Not rs.EOF strSQL = "INSERT INTO Users_tbl(ComputerName) " _ & " VALUES('" & rs.Fields(0) & "')" cn.Execute strSQL rs.MoveNext Wend

Also, you asked me to test using DebugPrint...I have not done that yet...I will do that now.

 

[puforee]

OK...I ran
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & "')"

Debug.Print strSQL
cn.Execute strSQL

The return was:
ShowUserRosterMultipleUsers
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
INSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118

That is where it stopped and errored out on the next step:
cn.Execdute_strSQL

Any thoughts?

 

[Andrzejek]

I see you have some 'left over' stuff in your Imediate Window from previous Debug.Print
You can clean it by deleteing all of that from that window. It will not go away by itself.

The statement from your code:
[tt]
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & "')"

Debug.Print strSQL
[/tt]
shoud give you:
[tt]
INSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118[red]')[/red]
[/tt]
See the [red]red[/red] quote and )

Copy that Insert statement, go to your Access and execute it in Access. It should insert one row in your Users_tbl table with the computer name TS-BLV-118

You may not need the .lbd file after all. Why would you need to get the users from your data base if you would keep track of who and when is getting in and out?

Have fun.

---- Andy

 

[puforee]

OK...just to be straight here. The total code looks like this:
Function ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long

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

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

End Function

And, because there are two Debug.Print statements I get:

ShowUserRosterMultipleUsers
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
INSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118 ')

And the cn.Execute strSQL fails. Now...you want me to replace what statement with? INSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118') Yes? How about the puncuation in the code.

 

[puforee]

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

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

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

End Function
When I do this...cn.Execute strSQl..There are no failurs...of course the data is not pushed to the table either. I just don't know why it won't load the table.

 

[Andrzejek]

So you DO get the last ' and the )
[tt]
INSERT INTO Users_tbl(ComputerName)
VALUES[highlight]('TS-BLV-118 ')[/highlight][/tt]

You may want to do
[tt]
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & [blue]Trim([/blue]rs.Fields(0)[blue])[/blue] & "')"
[/tt]
Did you try to copy this INSERT statement and run it in Access like I asked before?

Have fun.

---- Andy

 

[puforee]

Results of latest run with Trim inserted..still failed the cn.Execute strSQL step.

Imediate field output looks like this:
INSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118 ').
---------------------------------------------------------------
I also ran this code and got the same error.
While Not rs.EOF
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('TS-BLV-118')"
Debug.Print strSQL
cn.Execute strSQL
rs.MoveNext
Wend

Imediate field output looks like this:
NSERT INTO Users_tbl(ComputerName) VALUES('TS-BLV-118')

 

[Andrzejek]

Since you have some problems with one line of code - INSERT statement, you may want to look here for some hints. There is a nice example of how to do Inserts and Updates in Access VBA, so you would do something like this:

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

    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
[green]
    ' Modify this line to include the path and name
    ' to your Access data base [/green][blue]
    Set dbs = OpenDatabase("C:\SomeFoder\YourDataBase.mdb")[/blue]

    While Not rs.EOF
        strSQL = "INSERT INTO Users_tbl(ComputerName) " _
        & " VALUES('" & Trim(rs.Fields(0)) & "')"
        Debug.Print strSQL[blue]
        dbs.Execute [/blue]strSQL
        rs.MoveNext
    Wend
[blue]
    dbs.Close[/blue]
End Function

Try it and see if you will be able to Insert any record(s) into your User_tbl table this way.

Have fun.

---- Andy

 

[puforee]

Fails at this step:
Set dbs = OpenDatabase("\\Fil-nw-151\maint_train\MT Administration\DB\Working\Test ED Rev 1.X.mdb")

I have spaces in the path...is that causing a problem? I need to work around this because I cannot change the path..but I can change the DB name.

 

[Andrzejek]

Did you try to copy your 'Test ED Rev 1.X.mdb' into, like, C:\TEMP\ location, rename it to something like MyTest.mdb and try something like:
[tt]
Set dbs = OpenDatabase("C:\TEMP\MyTest.mdb")
[/tt]
???
And when you say: "Fails at this step" - what does it do? Do you get any errors? Of so, what do they say?

You cannot just say: "It does not work" or "It fails" because I do not know what happened. You need to provide more information.

Did you Google of how to do any Inserts/Updates/etc in your version of Access?

Have fun.

---- Andy