INSERT statement skipping records 1

[geonebula]

I have perused the forum and found a thread similar to what I'm looking for but not quite: thread705-449900.

I have multiple (60+) daily databases with 10 tables. Theoretically there are no duplicate records and to date this has held true. I am merging all these databases into a master database. The schema is the same throughout. To handle this I mashed together the script below. However, the script skips one table (and it's related table) in only some of the databases even when I specifically pick a single offending database to merge; the one offending table and it's related table don't append, however the other tables in that database do go into their respective "Master" tables. The offending table usually has ~800 to 1500 records in it. Any thoughts on why this is happening and/or how to fix? Thanks!

Sub Import_To_MasterDB()
    On Error GoTo ErrorHandler
         
    [COLOR=green]'Define Table Array for order of Insertion [/color]
    varTableArray = Array("Dt_task", "Dt_subfacility", "Dt_location", "Dt_location_parameter", "Dt_field_sample", "Dt_sample_parameter", "LU_equipment", "LU_person", "Dt_equipment_parameter", "Dt_purge", "equipment", "person")
    strFieldList = ""
    
    [COLOR=green]'Get a list of paths to each individual database within a directory[/color]
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
    
    If colDirList.Count = 0 Then
       MsgBox ("No databases were found.")
       Exit Sub
    Else:
        [COLOR=green]'Insert tables from DBs to MasterDB[/color]
        Set db = CurrentDb
        Set dbMasterDuplicate = OpenDatabase(strMasterDuplicatesDBPath)
            For Each varItem In colDirList
                For Each varTableName In varTableArray
                    On Error Resume Next [COLOR=green]'Assume error means table doesn't exist[/color]                  
                    [COLOR=green]'Use Insert to add data[/color]
                    strsql = "INSERT INTO " & varTableName & " SELECT * FROM [;DATABASE=" & [varItem] & "]." & [varTableName]
                    db.Execute strsql
                    intInsertedCount = db.RecordsAffected
             
                Next varTableName
            Next varItem
        MsgBox ("Importing of Databases Complete.")
    End If
    
End Sub

 

[Golom]

Since you asked this on June 3, you may have already resolved the issue.

There are a couple of things you can try

Enclose the table name in brackets just in case it contains spaces or other reserved characters.

strsql = "INSERT INTO [red][[/red]" & varTableName & "[red]][/red] SELECT * FROM [;DATABASE=" & [varItem] & "].[red][[/red]" & [varTableName][red] & ']' [/red]

Raise an error if there is a problem with the SQL

db.Execute strsql[red], dbFailOnError
If Err.Number <> 0 Then
    MsgBox Err.description
End If
Err.Clear[/red]

 

[geonebula]

Thanks for the suggestions. I haven't tried them yet, (busy with priority work) but I will let you know what happens when I do.

 

[geonebula]

SO I have included the dbFailOnError and have been unable to reproduce the bug. Any database I attempt to merge now includes every table. I'm glad it's working now but wish I knew what was going wrong before. Thanks again, Golom.