Overwrite a record instead of add

[MikeS]

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

 

[Patrick Molloy]

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]

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

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

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

 

[MikeS]

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

Thanks for the help.

MikeS

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

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

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