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,U ser_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@hotmail.com]

Re: Need to populate access table from sql database query

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]

Re: Need to populate access table from sql database query

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.Connectio n
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connectio n

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

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimisti c
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@hotmail.com]

Re: Need to populate access table from sql database query

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

UPDATE MyAccessTable
SET MyField = SomeFunction(So meSQLServerAtta chedTable, arg2, arg3)
WHERE SomeField=True;

[pietlinden@hotmail.com]

Re: Need to populate access table from sql database query

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.Som eField = SQLServerTable. SomeField
FROM AccessTable INNER JOIN SQLServerTable ON
AccessTable.Fie ldX=SQLServerTa ble.FieldY
WHERE...

You shouldn't need any code at all.