How do I copy duplicate records into another database table?

[geonebula]

I have 3 databases with a set of redundant tables:
Source - with the field data entered (Primary Keys same as Master)
Master - containing all the merged data from the Sources
Master_Duplicate - to contain all duplicate records from an attempt to merge a Source into the Master

I have created 2 linked tables in the Master_Duplicate db for the subject table (one from the Master and the other from the Source).

Then I generate this SQL statement:

INSERT INTO [TableName](Field1,Field2,...FieldN) SELECT (Field1,Field2,...FieldN) FROM LinkTable_Source INNER JOIN LinkTable_Master ON LinkTable_Source.PrimaryKey = LinkTable_Master.PrimaryKey

and have the the Master_Duplicate database execute it. However, nothing is being inserted. What am I missing here?

The code is:

[COLOR=green]'Create link table of Duplicate Records[/color]
                    sTableName = varTableName
                    sDBPath = varItem
                    sLinkName = sTableName & "_Source"
                    sLinkName2 = sTableName & "_Master"
                    Set tblsrc = db.CreateTableDef(sLinkName)
                    tblsrc.Connect = "MS Access;DATABASE=" & sDBPath & ";TABLE=" & sTableName
                    tblsrc.SourceTableName = sTableName
                    Set tbldupsrc = dbMasterDuplicate.CreateTableDef(sLinkName)
                    tbldupsrc.Connect = "MS Access;DATABASE=" & sDBPath & ";TABLE=" & sTableName
                    tbldupsrc.SourceTableName = sTableName
                    Set tbldup = dbMasterDuplicate.CreateTableDef(sLinkName2)
                    tbldup.Connect = "MS Access;DATABASE=" & strMasterDbPath & ";TABLE=" & sTableName
                    tbldup.SourceTableName = sTableName
                    
                    On Error Resume Next [COLOR=green]'Assume error means table doesn't exist[/color]
                    db.TableDefs.Delete tblsrc.sLinkName[COLOR=green] ' Delete old table link[/color]
                    db.TableDefs.Append tblsrc [COLOR=green]' Link the new table[/color]
                    db.TableDefs.Refresh
                    
                    [COLOR=green]'Create table links in Master Duplicate database of sourcedb and masterdb[/color]
                    dbMasterDuplicate.TableDefs.Delete tbldupsrc.sLinkName [COLOR=green]' Delete old  source table link[/color]
                    dbMasterDuplicate.TableDefs.Append tbldupsrc [COLOR=green]' Link the new source table[/color]
                    dbMasterDuplicate.TableDefs.Delete tbldup.sLinkName2 ' Delete old master table link
                    dbMasterDuplicate.TableDefs.Append tbldup [COLOR=green]' Link the new master table[/color]
                    dbMasterDuplicate.TableDefs.Refresh [COLOR=green]' Refresh the table list just in case[/color]
                    
                    [COLOR=green]'Place all duplicate records into Master Duplicate Database[/color]
                    strSQLDupIns = "INSERT INTO " & [varTableName] & "(" & strFieldList & ")" & " SELECT " & "(" & strFieldList & ")" & " FROM " & [sLinkName] & " INNER JOIN " _
                    & [sLinkName2] & " ON " & [sLinkName] & "." & [PK_field] & " = " & [sLinkName2] & "." & [PK_field]
                    dbMasterDuplicate.Execute strSQLDupIns

 

[vbajock]

Do you have an Option Explicit entered into your module header?

I'd display the strSQLDUPIns to the Immediate window and see if my SQL string is what I am expecting it to be. Put a Stop before dbMasterDuplicate.Execute strSQLDupIns, and then when the code halts, open your immediate window and type in ? strSQLDupIns (hit enter)

 

[geonebula]

I guess my question is more geared towards the SQL syntax, as the strSQLDupIns is the string I thought I needed but it still isn't inserting anything into the Master_Duplicate db. I know that there are duplicates in my test Source db and Master db.

I want to copy a record from the linked_Source Table into the corresponding table in the Master_Duplicate db if the linked_Source table record's primary key is in the linked_Master table.

INSERT INTO Dt_purge(sys_loc_code, ... remark) SELECT (Dt_purge_Source.sys_loc_code, ... Dt_purge_Source.remark) FROM Dt_purge_Source INNER JOIN Dt_purge_Master ON Dt_purge_Source.+person_name = Dt_purge_Master.+person_name

Any thoughts?

 

[PHV]

If you really have field named +person_name, then:
ON Dt_purge_Source.[!][[/!]+person_name[!]][/!] = Dt_purge_Master.[!][[/!]+person_name[!]][/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[geonebula]

I am pulling the primary keys from each table with this:

For Each idx In tbl.Indexes
          If idx.Name = "PrimaryKey" Then
              PK_field = idx.Fields
          End If
Next idx

Should I be doing anything else to the string that is returned? I have tried adding the brackets as PHV suggested but still none of the duplicates are being copied over. I also tried replacing "ON Source_Table.PK = Master_Table.PK" with "USING PK" to no avail.

 

[vbajock]

A plus sign in a field name does not strike me as being a good idea.

 

[geonebula]

The plus sign was not supposed to be there, also the () surrounding the field names after SELECT were throwing an error I didn't see until I copied the generated SQL statement into the Access Query Builder and tried to run it. Once those issues were resolved it at least appears to be inserting some data into the Master_Duplicate database, now to just validate that it's doing it right and not missing any records. Thank you for your help!