Overwrite a record instead of add
 

I have the following code that adds a record to an Access database:

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=S:\MalLab\MAL_DB.mdb;"
Set rs = New ADODB.Recordset
rs.Open "CurrentPMT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("CurrentPMTScore") = Sheet1.Range("F1").Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Can someone tell me how to overwrite the existing record instead of adding a
new one.

Thanks in advance,

MikeS

Overwrite a record instead of add
 

Sub UpdateDatabase()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "UPDATE BondTable SET [PRICE] = 99.875 WHERE [BOND_ID]='10264796' "

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.Close
Set con = Nothing
End Sub



"MikeS" wrote:

I have the following code that adds a record to an Access database:

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=S:\MalLab\MAL_DB.mdb;"
Set rs = New ADODB.Recordset
rs.Open "CurrentPMT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("CurrentPMTScore") = Sheet1.Range("F1").Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Can someone tell me how to overwrite the existing record instead of adding a
new one.

Thanks in advance,

MikeS

Overwrite a record instead of add
 

Patrick,

This almost works. But, I can't figure out how to get the value from a
cell. For instance, the following line:

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = '500'"

will update the existing record, however if I use:

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = Range("F3")"

I get an error. Or if I try to use a variable:

Dim strData as string
strData = Range("F3")

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = " & strData

I still get an error.

Can you tell me how to fix this?

Thanks,

MikeS




"Patrick Molloy" wrote:

Sub UpdateDatabase()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "UPDATE BondTable SET [PRICE] = 99.875 WHERE [BOND_ID]='10264796' "

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.Close
Set con = Nothing
End Sub



"MikeS" wrote:

I have the following code that adds a record to an Access database:

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=S:\MalLab\MAL_DB.mdb;"
Set rs = New ADODB.Recordset
rs.Open "CurrentPMT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("CurrentPMTScore") = Sheet1.Range("F1").Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Can someone tell me how to overwrite the existing record instead of adding a
new one.

Thanks in advance,

MikeS

Overwrite a record instead of add
 

Nevermind the previous post. I finally figured it out. I was using the
wrong data type for my variable.

Thanks for the help.

MikeS

"MikeS" wrote:

Patrick,

This almost works. But, I can't figure out how to get the value from a
cell. For instance, the following line:

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = '500'"

will update the existing record, however if I use:

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = Range("F3")"

I get an error. Or if I try to use a variable:

Dim strData as string
strData = Range("F3")

SQL = "UPDATE CurrentPMT SET [CurrentPMTScore] = " & strData

I still get an error.

Can you tell me how to fix this?

Thanks,

MikeS




"Patrick Molloy" wrote:

Sub UpdateDatabase()
Dim MyFile As String
Dim con As New ADODB.Connection
Dim SQL As String
Dim com As ADODB.Command

MyFile = "E:\Excel\Excel_Demos\Risk.mdb"
SQL = "UPDATE BondTable SET [PRICE] = 99.875 WHERE [BOND_ID]='10264796' "

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyFile

With New Command
.ActiveConnection = con
.CommandType = adCmdText
.CommandText = SQL
.Execute
Debug.Print .Properties.Count
End With
con.Close
Set con = Nothing
End Sub



"MikeS" wrote:

I have the following code that adds a record to an Access database:

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=S:\MalLab\MAL_DB.mdb;"
Set rs = New ADODB.Recordset
rs.Open "CurrentPMT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("CurrentPMTScore") = Sheet1.Range("F1").Value
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

Can someone tell me how to overwrite the existing record instead of adding a
new one.

Thanks in advance,

MikeS