How not import duplicates

[Nickaroo]

How not import duplicates from one table to another….

I have two tables one is the Main table and the other is a Temp table which is imported via ODBC. The Temp table could possibly have a record in it that is already in the Main table. There is not and can not have an UNIQUE field in the Main table.

“Case#”is the field in both table that should not be duplicated

I am also using ADO Recordsets….
Nick....Remember to turn of the sound when your mind goes blank

 

[GummowN]

In database terms "Cannot have a unique field" is not valid.

It is always possible to have a unique field, it is just how you wish to implement it. Access makes it very easy with the autonumber field - which I know is not the case for all ODBC sources.

The fact case# cannot be duplicated would itself indicate that it is a unique ID - no duplicates = always unique!!!

You will want a left join query IE

SELECT * FROM Table1 LEFT JOIN table2 ON case#.table1 = case#.table2 WHERE case#.table2 is null

Will pull all records from table1 where there is no matching case in table2

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Nickaroo]

This is how i am importing now....I should have sent this earlier..

Private Sub SplitTimeDate()

'This Subroutine Spilts the Time and Date from the table Mapping, which is imported from Spillman via ODBC.


Dim ImportMapData As ADODB.Recordset, MasterCase As ADODB.Recordset, weekofyear As Variant
Set ImportMapData = New ADODB.Recordset
Set MasterCase = New ADODB.Recordset

'The String below is a query that selects from Mapping and OffenseCodes
'StrSQL
strSQL = "SELECT Mapping.number, Mapping.ocurdt1, Mapping.ocurdt2, Mapping.address, Mapping.city, Mapping.state, Mapping.zip, OffenseCodes.Category, OffenseCodes.Offcodes, OffenseCodes.Used, OffenseCodes.patno " & _
"FROM OffenseCodes INNER JOIN Mapping ON OffenseCodes.Offcodes = Mapping.offcode " & _
"WHERE (((OffenseCodes.Used)=True));"

ImportMapData.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
MasterCase.Open "select * from [master case]", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
With ImportMapData
'Move first moves the Pointer to the "First Record" in the Set
'If there are no records in the "Record Set" then do nothing, Else Start reading records from Mapping and
'Split the Time and Date and insert into "Master Case" table.
'
If ImportMapData.RecordCount > 0 Then
ImportMapData.MoveFirst
While Not .EOF
' MasterCase.Find("mastercase.caseno = " & ImportMapData.Fields("caseno&quot, , adSearchForward) Then
MasterCase.AddNew
weekofyear = Format(ImportMapData.Fields("ocurdt1&quot, "ww&quot
If Len(weekofyear) < 2 Then
weekofyear = 0 & weekofyear
End If
MasterCase.Fields(&quot;patternno&quot = Year(ImportMapData.Fields(&quot;ocurdt1&quot) & weekofyear & ImportMapData.Fields(&quot;Patno&quot
MasterCase.Fields(&quot;Start date&quot = Left(ImportMapData.Fields(&quot;ocurdt1&quot, 10)
If Len(ImportMapData.Fields(&quot;ocurdt1&quot) = 10 Then
MasterCase.Fields(&quot;start time&quot = &quot;00:00&quot;
Else
MasterCase.Fields(&quot;start time&quot = Right(ImportMapData.Fields(&quot;ocurdt1&quot, 10)
End If
If Len(ImportMapData.Fields(&quot;ocurdt2&quot) = 10 Then
MasterCase.Fields(&quot;End time&quot = &quot;00:00&quot;
Else
MasterCase.Fields(&quot;End time&quot = Right(ImportMapData.Fields(&quot;ocurdt2&quot, 10)
End If
MasterCase.Fields(&quot;End Date&quot = Left(ImportMapData.Fields(&quot;ocurdt2&quot, 10)
MasterCase.Fields(&quot;caseno&quot = Trim(ImportMapData.Fields(&quot;number&quot)
MasterCase.Fields(&quot;address&quot = ImportMapData.Fields(&quot;address&quot
MasterCase.Fields(&quot;city&quot = ImportMapData.Fields(&quot;city&quot
MasterCase.Fields(&quot;zip&quot = Left(ImportMapData.Fields(&quot;zip&quot, 5)
MasterCase.Fields(&quot;state&quot = ImportMapData.Fields(&quot;state&quot
MasterCase.Fields(&quot;offenseType&quot = ImportMapData.Fields(&quot;category&quot
MasterCase.Update
' End If
.MoveNext

Wend
ImportMapData.Close
MsgBox &quot; Import Sucessfull &quot;, vbInformation, &quot;UPDATE&quot;
MasterCase.Close
End If
End With
Set MasterCase = Nothing
Set ImportMapData = Nothing

End Sub

 

[GummowN]

You are not validating the information you are importing. You need to manipulate the data in the importmapdata recordset before you pass it straight into the mastercase recordset.

You are assuming that importmapdata is correctly formatted and containing valid information.

Before you do the .addnew add lines of validation

eg
If left(ImportMapData.Fields(&quot;city&quot ,1) <>&quot;p&quot; then
blnValid = False 'IE Only wanting to import data from cities beginning with the letter P
End if

If blnValid then
RUN THE IMPORT NOW
Else
Skip record and go to the next record
OR Add to an error recordset
End if

Hope that helps

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Nickaroo]

How would i keep &quot;Mastercase.caseno&quot; from importing the same &quot;importmapdata.caseno&quot; IE Duplicate record

 

[GummowN]

Have a simple function like the following - you will need to convert to ado as this for dao.

I am assuming the caseno is an integer otherwise you will have to ammend the strSQL to include 's if it is a string

Use as:

If fncFindID(Importmapdata!caseno) = true then
' Found already
Else
' Not Found
IMPORT CODE
End if

Public Function fncFindID (intCase as integer) as boolean
Dim dbs as database
Dim rst as recordset
Dim strSQL as string

Set dbs = currentdb
strSQL = &quot;SELECT caseno FROM mastercase WHERE caseno = &quot; & intCase
set rst = dbs.openrecordset(strSQL)
IF rst.eof then
fncFindID = false
Else
fncFindID = true
End if
rst.close
set dbs = nothing
End Function



If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[Nickaroo]

Private Function DuplicateRecord(Caseno As String, Offcode As String) As Boolean
Dim CheckDup As ADODB.Recordset
Dim StrSql As String
Set CheckDup = New ADODB.Recordset
StrSql = &quot;select * from [master case] &quot; & _
&quot;where [master case].caseno = '&quot; & Caseno & &quot;' and [master case].offensetype = '&quot; & Offcode & &quot;'&quot;
'strWhere = strWhere & &quot; offensetype = '&quot; & Me.OffenseCB & &quot;' And &quot;

Debug.Print StrSql

CheckDup.Open StrSql, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

If CheckDup.RecordCount > 0 Then
DuplicateRecord = True
Debug.Print Caseno
Else
DuplicateRecord = False
End If

CheckDup.Close
Set CheckDup = Nothing

End Function

‘From SpiltTimeDate Sub


TempCaseno = Trim(ImportMapData.Fields(&quot;number&quot)
TempCategory = ImportMapData.Fields(&quot;category&quot

If Not DuplicateRecord(TempCaseno, TempCategory) Then

This works perfectly
Thanks so much for your help…..