Update Access Database Using Excel

[Guest]

I need to update an existing database entry with a new values. My current
code is shown below. This code adds a new entry, but a couple days after I
have added this new entry, I want to update this new entry with new data.
This new entry would be unique by the Date and Node Entry together (Range A
and B). I found some code using the '.Find' function, but returns error
'Method or Data Member Not Found'. Any help on this matter would be much
appreciated.


Sub Dbase()

Dim db As DAO.Database, rs As DAO.Recordset, r As Long

'Starting Row in Worksheet
r = 2

Set db = OpenDatabase("T:\Data\XXX.mdb")
Set rs = db.OpenRecordset("MISO Real Time LMP", dbOpenTable)

'Repeat Until 0 Value Reached in Column D
Do Until Range("D" & r).Value = Change
With rs
On Error Resume Next

'Create New Record
.AddNew
.Fields("Date") = Range("A" & r).Value
.Fields("Node") = Range("B" & r).Value
.Fields("Type") = Range("C" & r).Value
.Fields("HE 1") = Range("E" & r).Value
.Fields("HE 2") = Range("F" & r).Value
.Fields("HE 3") = Range("G" & r).Value
.Fields("HE 4") = Range("H" & r).Value
.Fields("HE 5") = Range("I" & r).Value
.Fields("HE 6") = Range("J" & r).Value
.Update

End With

r = r + 1
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

 

[Geoff]

I need to update an existing database entry with a new values. My current

code is shown below. This code adds a new entry, but a couple days after
I
have added this new entry, I want to update this new entry with new data.
This new entry would be unique by the Date and Node Entry together (Range
A
and B). I found some code using the '.Find' function, but returns error
'Method or Data Member Not Found'. Any help on this matter would be much
appreciated.

I've made a couple of assumptions. If these are right, then the following
code might do the trick. I've annotated the code to explain what's going on.

I probably won't be able to respond again this week.
Good luck.
Geoff


Sub Dbase()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Long
Dim datDate As Date
Dim vntNode As Variant

Set db = OpenDatabase("T:\Data\XXX.mdb")
Set rs = db.OpenRecordset("MISO Real Time LMP", dbOpenTable)

' As you want to update the new entry in the database,
' this code assumes that you do not wish to change the
' Date or Node fields. If these two fields do not
' change, then you can use their values in the Excel
' spreadsheet to find records in the Access database.
' This code assumes that's what you want to do.

' As you are opening a recordset of type dbOpenTable,
' then you can use the "Seek" method to find records.

' The Seek method assumes:
' 1. That the table is not a linked table (ie the table
' is in the current database).
' 2. That the table contains a primary or unique index
' based on the fields you want to find.

' The "Seek" method is the fastest way to locate records
' in the recordset.

' Let's assume you've already created one (unique) index
' in the table containing the two fields "Date" and "Node"
' in that order and let's assume this index has the name
' "MyUniqueIndex".

' Point to the index:
rs.Index = "MyUniqueIndex"

' Not sure why you had this but I've left in:
On Error Resume Next

'Starting Row in Worksheet
r = 2

'Repeat Until 0 Value Reached in Column D
Do Until Range("D" & r).Value = 0
GoSub UpdateNextRecord
r = r + 1
Loop

Bye:

On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Exit Sub

UpdateNextRecord:

' Get Date in Excel Range:
datDate = Range("A" & r).Value

' Get Node in Excel Range:
vntNode = Range("B" & r).Value

With rs

' Find record in database:
.Seek "=", datDate, vntNode

' If record not found in database,
' just ignore and return to loop to
' do next record:
If .NoMatch = True Then Return

' Record found so edit it:
.Edit
.Fields("Type") = Range("C" & r).Value
.Fields("HE 1") = Range("E" & r).Value
.Fields("HE 2") = Range("F" & r).Value
.Fields("HE 3") = Range("G" & r).Value
.Fields("HE 4") = Range("H" & r).Value
.Fields("HE 5") = Range("I" & r).Value
.Fields("HE 6") = Range("J" & r).Value
.Update

End With

Return

End Sub

 

[Guest]

Thanks Geoff. I am a beginner at this stuff, but learning quickly. Not even
a month ago I created my first database. I realized that the index was in
the opposite order, so I switched the two around in the Seek method. Now I
just have to finish the logic on whether to add or update the values. Thanks
very much to people like you that help. I appreciate your time.

 

[Geoff]

Now I just have to finish the logic on whether to add or update

the values.

Here's a suggestion.
Geoff

CHANGE:

If .NoMatch = True Then Return

TO:

' If record not found in database, add new record;
' otherwise, edit existing record:
If .NoMatch = True Then

' Record not found so create new record:
.AddNew
.Fields("Date") = Range("A" & r).Value
.Fields("Node") = Range("B" & r).Value
.Fields("Type") = Range("C" & r).Value
.Fields("HE 1") = Range("E" & r).Value
.Fields("HE 2") = Range("F" & r).Value
.Fields("HE 3") = Range("G" & r).Value
.Fields("HE 4") = Range("H" & r).Value
.Fields("HE 5") = Range("I" & r).Value
.Fields("HE 6") = Range("J" & r).Value
.Update

Else

' Record found so edit it:
.Edit
.Fields("Type") = Range("C" & r).Value
.Fields("HE 1") = Range("E" & r).Value
.Fields("HE 2") = Range("F" & r).Value
.Fields("HE 3") = Range("G" & r).Value
.Fields("HE 4") = Range("H" & r).Value
.Fields("HE 5") = Range("I" & r).Value
.Fields("HE 6") = Range("J" & r).Value
.Update

End If