Ischyros
01-23-2009, 02:10 PM
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:
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: