Overwrite a record instead of add


M

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
 
Ad

Advertisements

P

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
 
M

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




Patrick Molloy said:
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 said:
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
 
Ad

Advertisements

M

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

MikeS said:
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 said:
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 said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top