Need to populate access table from sql database query

[Terry Olsen]

I have an access database with a table that contains two columns:
Computer_Name,User_ID

The table contains 600 computer names. I need to populate the User_ID
column with data from an SQL database.

I want to use VBA to step through each Computer_Name in the table, query
the SQL database for the User_ID associated with Computer_Name, and
populate the User_ID in the access table.

I'm not an Access programmer, but this was thrown at me today. I need
some guidance on how to code this. I have the connection string to the
SQL database working, but I can't figure out how to get the data from
the SQL query into the access table.

Thanks.

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

attach to the SQL database from Access and then just write a query.
When you get what you want, turn the query into an update or append
query.

[Terry Olsen]

I got the query side working, but I can't update the access table.
Here's the code:

--------------------------------
Private Sub UpdateUserName(ByVal PCName As String, ByVal UserName As
String)
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source=" & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "SELECT * FROM [Dist 53 3rd party Software] WHERE
[Workstation] = '" & PCName & "'", _
CurConn, , adCmdText

rst!UserID = UserName
rst.Update
rst.Close

End Sub
-------------------------------------

When I run this code, I get: "Current Recordset does not support
updating. This may be a limitation of the Provider, or of the selected
locktype.

Can anyone see what's wrong?

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

Is there a reason you can't call a function from Access that will
update the fields in question?

UPDATE MyAccessTable
SET MyField = SomeFunction(SomeSQLServerAttachedTable, arg2, arg3)
WHERE SomeField=True;

[pietlinden]

what happens if you create a link to the SQL Server table from within
Access and then write an update query that updates your Access native
table? This looks like doing everything the hard way. Why not do it
the easy way?

Should be something simple like a simple inner join query and then turn
that into an update query.

UPDATE AccessTable
SET AccessTable.SomeField = SQLServerTable.SomeField
FROM AccessTable INNER JOIN SQLServerTable ON
AccessTable.FieldX=SQLServerTable.FieldY
WHERE...

You shouldn't need any code at all.