Need to populate access table from sql database query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Terry Olsen - 05 Apr 2006 18:49 GMT

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.

Reply to this Message

pietlinden@hotmail.com - 05 Apr 2006 21:06 GMT

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.

Reply to this Message

Terry Olsen - 05 Apr 2006 23:14 GMT

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?

Reply to this Message

pietlinden@hotmail.com - 06 Apr 2006 00:50 GMT

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;

Reply to this Message

pietlinden@hotmail.com - 06 Apr 2006 00:56 GMT

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.

Reply to this Message