Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding VBA results to a table 1

Status
Not open for further replies.
puforee

puforee

Technical User
Oct 6, 2006
741
US
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
 
Sort by date Sort by votes

Could you show what Debug.Print gave you?
Which field is the Computer_name?
What is the structure of your "User Tbl"?

Have fun.

---- Andy
 
Upvote 0 Downvote
Debug print shows this in the Immediate area:

ShowUserRosterMultipleUsers
COMPUTER_NAME LOGIN_NAME CONNECTED SUSPECT_STATE
A4826682 Admin True Null
 
Upvote 0 Downvote

If rs.Fields(0) is the Computer_Name, then:
Code: 
While Not rs.EOF
    strSQL = INSERT INTO TblUser(ComputerName, ...) " _
        & " VALUES([blue]'" & rs.Fields(0) & "'[/blue], ...)"
    cn.Execute strSQL
    rs.MoveNext
Wend

Have fun.

---- Andy
 
Upvote 0 Downvote
OK. I am getting some red code. My table name is User Tbl. So, should my insert statement be something like...

strSQL = INSERT INTO Tbl User Tbl(ComputerName, ...) " _
& " VALUES('" & rs.Fields(0) & "', ...)"
 
Upvote 0 Downvote

You see, that's why I hate spaces in table names and in field names. And that's why I asked about your table structure: table name, field names, field data types, etc.
Try
[tt]
strSQL = INSERT INTO [User Tbl](ComputerName, ...) " _
& " VALUES('" & rs.Fields(0) & "', ...)"
[/tt]
And you should have [tt] Option Explicit[/tt] at the top of your code, which means you need to declare [tt]strSQL[/tt] somewhere before you can use it.

Have fun.

---- Andy
 
Upvote 0 Downvote
OK...I get the message....bad habit. So new table name "Users_tbl", only one field...."ComputerName" (text field).

So can we try one more time. I only need the one value.

Thanks,
 
Upvote 0 Downvote

Try:
Code: 
While Not rs.EOF
    strSQL = INSERT INTO Users_tbl(ComputerName) " _
        & " VALUES('" & rs.Fields(0) & ")"
    cn.Execute strSQL
    rs.MoveNext
Wend
And I don't think it is a bad habit, it is lack of standards.

Have fun.

---- Andy
 
Upvote 0 Downvote
Fantastic. I will work this tomorrow AM and get back to you with the results.
 
Upvote 0 Downvote
Andy,

I copied your code directly into my VBA Module. I get a "Compile error: Expected: end of statement. And the INTO word is highlighted. So....this all turns red and INTO is highlighted:

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

End Sub


The red part is:
strSQL = INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & ")"

 
Upvote 0 Downvote
Code: 
strSQL = [b][red]"[/red][/b]INSERT INTO Users_tbl(ComputerName) " _


Randy
 
Upvote 0 Downvote
Great...all black now. Testing in a few minutes. Do I still need this code or should it be replaced with something else? It occurs just before your code..see original thread.

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

You don't need [tt]Debug.Print[/tt] anywhere, it is just to be able to see what you are getting while debugging your code.

I often do this:
[tt]
While Not rs.EOF
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & ")"[blue]
Debug.Print strSQL[/blue]
cn.Execute strSQL
rs.MoveNext
Wend
[/tt]
Just to see the SQL and make sure it is what I expected it to be. Especially if I pass a lot of variables into it.
You can comment it out. I would.

Have fun.

---- Andy
 
Upvote 0 Downvote
That was not where it is. Check the code JUST BEFORE yours.

Function 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
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & ")"
cn.Execute strSQL
rs.MoveNext
Wend

End Function
 
Upvote 0 Downvote

I know, you were talking about other location of [tt]Debug.Print[/tt]
I just showed you how I use it in my code.
But none of them ([tt]Debug.Print[/tt]) are needed for the program to run.

Have fun.

---- Andy
 
Upvote 0 Downvote
I ran the code as an autoexec and it failed at the indicated step.

Function 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
strSQL = "INSERT INTO Users_tbl(ComputerName) " _
& " VALUES('" & rs.Fields(0) & ")"
Fail Here cn.Execute strSQL
rs.MoveNext
Wend

End Function

Fail message:
Run-time error '-2147217900 (80040e14)':
Syntax error in string in query expression "TS-BLV-118'.

I ran it again on my local computer and the same message showed up except the last part was "A4826682" (This is my computer name)
 
Upvote 0 Downvote

Do you have a table named Users_tbl ? If so,
do you have a field in this table named ComputerName ? If so,
how is this field defined in your table?

But there is another question:
Who / when / what is going to run this code?
Because you will end up with totally useless information in your Users_tbl, something like:
[tt]
ComputerName
A4826682
TS-BLV-118
TS-BLV-118
TS-BLV-120
A4826682
A4826687
A4826789
TS-BLV-118
A4826682
TS-BLV-118
[/tt]

Have fun.

---- Andy
 
Upvote 0 Downvote
Table Name: Users _tbl
Field Name: ComputerName Data Type: Text - 50 characters

The job will run when the DB is opened...normally there will only be one persons computer attached...the other items TS...... CITRIX servers...depends on where we run the DB from. It is housed on a server (Not CITRIX) and is a split DB.

The Axxxxxx numbers will do me a lot of good...the employees table has this information in a field. I can compare and then determine what I want this employee to see.

Do you have other thoughts? I would appreciate hearing about them.
 
Upvote 0 Downvote

You see, this is why I use Debug.Print
If you would do this:
Code: 
   While Not rs.EOF
        strSQL = "INSERT INTO Users_tbl(ComputerName) " _
        & " VALUES('" & rs.Fields(0) & ")"
Debug.Print strSQL

 Fail Here cn.Execute strSQL
        rs.MoveNext
    Wend
you would see:
[tt]
INSERT INTO Users_tbl(ComputerName)
VALUES('A4826682)
[/tt]
There is a missing single quote, there should be one around the ComputerName.

Try:
Code: 
   While Not rs.EOF
        strSQL = "INSERT INTO Users_tbl(ComputerName) " _
        & " VALUES('" & rs.Fields(0) & "[red]'[/red])"
        cn.Execute strSQL
        rs.MoveNext
    Wend

Have fun.

---- Andy
 
Upvote 0 Downvote
Still fails this statement. and I did put the single quote in the spot you indicated.
cn.Execute strSQL
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

renigar
  • Locked
  • Question
Access VBA Syntax Error 1
Replies
5
Views
3K
Andrzejek
Andrzejek
A
  • Question
Print Table attachment
Replies
2
Views
128
andre.meyer
A
ameedoo3000
  • Locked
  • Question
format partition by vba code
Replies
6
Views
685
mattKnight
mattKnight
renigar
  • Locked
  • Question
Access 365: Search LastName in one table to put EmployeeID in another
Replies
8
Views
647
renigar
renigar
OCM
  • Locked
  • Question
Populate access table from excel
Replies
6
Views
3K
OCM
OCM

Part and Inventory Search

Sponsor

Back
Top