Solved: Update Recordset from VBA Not Working

[Ischyros]

I have the following code in VBA which is run from an excel file. It is intended to update an existing recordset in an Access Database. However when I run it a get the following error "Error # -2147217900". I have searched hopelessly for a solution

Const TARGET_DB = "I:\Project Database\TRPDDataTest.mdb"

Sub Update_AccessDB()
Dim wks As Worksheet
Set wks = Worksheets("Project List")
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim sSQL As String
Dim i as Integer
ProjName As String

i = 40
ProjName = wks.Cells(i, 1)

ProjID = 3

On Error GoTo ErrHandler
sSQL = "UPDATE Capital_Projects SET Name=" & ProjName & " WHERE ID =" & ProjID

Set cnn = New ADODB.Connection
MyConn = TARGET_DB
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With

'Load contents of modified record from Excel to Access.
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, _
ActiveConnection:=cnn, _
CursorType:=adOpenKeyset, _
LockType:=adLockOptimistic

rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
Exit Sub
ErrHandler:
msg = "Error # " & Str(Err.Number) & " was generated by " _
' & Err.Source & Chr(13) & Err.Description
MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext

End Sub



On an additional note if my SQL string is

sSQL = "SELECT * FROM Capital_Projects WHERE ID = " & ProjID


then everything works fine. I just get an error when I try to update or insert a record. This is my first time working with a Access DB. Thanks!:whistle:

[CreganTur]

This issue is that you cannot open a recordset with an Action query (UPDATE is a type of action query). Recordsets can only be opened with SELECT querys. So use this SQL string:


sSQL = "SELECT * FROM Capital_Projects WHERE ID = " & ProjID


Now, I am making an assumption here that ID is a primary key, so the above SELECT statement will only pull a single record. If this is correct, then you can update the value in your recordset like this:
rst.Fields("Name").Value = ProjName
rst.update

If you do not use the .update command, then the update will not be saved.

HTH:thumb

[Ischyros]

Thanks, I will try again tomorrow and let you know what happens!

[Ischyros]

Thanks is worked!!!! I can't believe how simple that was and I couln't find that information anywhere.