VBA question

[dhowell]

The following is code to take information from an Excel 2007 spread
sheet and dump it into an Access 2007 database.

I need to somehow make sure this only happens if the information has
not already been placed in the database.

Each record should be completely unique.
IE
As long as the new record to be entered is not an exact match on all
fields, then it can be entered.

If the record is a complete match on all fields, then it will not be
entered.


Here is the current code.
(Which works perfect except for the fact I can get duplicate records.)


Sub printlog2()
' exports data from the active batch log to
' \\DCC-1\LabShare\D\Polyurethane Group\PUR Batch Logs\PUR Batch Raw
Log.accdb
'
Dim db As Database, rs As Recordset, r As Long, s As Long
Set db = OpenDatabase("\\DCC-1\LabShare\D\Polyurethane Group\PUR Batch
Logs\PUR Batch Raw Log.accdb")
' open the database
Set rs = db.OpenRecordset("rawlog", dbOpenTable)
' all records in a table
s = 5
Do While Len(Range("C" & s).Formula) > 0
r = s ' the start row in the given raw material
Do While Len(Range("C" & r).Formula) > 0
' repeat until first empty cell in column C5+x
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("batdate") = Range("B5").Value
.Fields("prod") = Range("E2").Value
.Fields("prodlot") = Range("H2").Value
.Fields("reactor") = Range("N2").Value
.Fields("rawnum") = Range("C" & r).Value
.Fields("begin") = Range("E" & r).Value
.Fields("end") = Range("F" & r).Value
.Fields("rawlot") = Range("G" & r).Value
.Fields("rawwt") = Range("H" & r).Value
.Fields("oper1") = Range("K" & r).Value
.Fields("oper2") = Range("L" & r).Value
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
s = s + 4 ' next raw
Loop
rs.Close
Set rs = Nothing

Set rs = db.OpenRecordset("mdiadd", dbOpenTable)
' all records in a table
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("batdate") = Range("B5").Value
.Fields("prod") = Range("E2").Value
.Fields("prodlot") = Range("H2").Value
.Fields("reactor") = Range("N2").Value
.Fields("mdidate") = Range("B60").Value
.Fields("mditime") = Range("C60").Value
.Fields("mdisec") = Range("E60").Value
.Update ' stores the new record
End With
rs.Close
Set rs = Nothing
db.Close
' Prints Pre-Batch Checklist and Batch Log sheets in the DCC mail
room.
'
Sheets("Pre-Batch Checklist").Select
ActiveSheet.PrintOut
Sheets("Batch Log").Select
ActiveSheet.PrintOut
Sheets("Pre-Batch Checklist").Select
Range("A7").Select
End Sub

 

[Chris O'C via AccessMonster.com]

Don't use a recordset. That's the slowest, most resource intensive way to do
it.

Best way is to add a unique index on the table for the fields you don't want
duplicated, convert the xls file to a csv file and import using the
transfertext function.

Since the data you want to import is really multiple cells on the worksheet,
create a new worksheet mapping those cells with references so that they lie
in a contiguous grid in the new worksheet. Convert this worksheet to a csv
file and import this new file with the transfertext function.

Chris