I found this code that finds a list of users that have a database open. I now want to open up a form with a table of all the people that have it open. I figure the best way is log it to a table and then use that table as a recordsource on a form. Then when I close the form, it deletes all records in the table. I am using this code to write it to the table:
The sql keeps on getting truncated. the single apostrophe after Trim(rs!Login_Name) and closing parenthesis and semi-colon are not showing up in the sql code.
Code:
Sub ShowUserRosterMultipleUsers()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i, j As Long
Dim sql As String
On Error GoTo UnknownError
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.
DoCmd.SetWarnings False
rs.MoveFirst
While Not rs.EOF
sql = "INSERT INTO tblLoggedInUsers (Login_Name) VALUES('" & Trim(rs!Login_Name) & "');"
'sql = "INSERT INTO tblLoggedInUsers (Login_Name, Computer_Name, Connected) VALUES('" & rs.Fields(1) & "', '" & rs.Fields(0) & "', '" & rs.Fields(2) & "');"
MsgBox sql
DoCmd.RunSQL sql
rs.MoveNext
WendThe sql keeps on getting truncated. the single apostrophe after Trim(rs!Login_Name) and closing parenthesis and semi-colon are not showing up in the sql code.
