Append Query not appending all data

[tubbers]

I posted this in another forum but I think it may have been the wrong one, so I'm trying again.

I have a form which has command buttons to do three steps. Step 1 (importing tables from other MDBs) works fine as does Step 3 (deleting imported tables after the data has been appended). Step 2 only works partially.

In this particular instance there are 447 tables which satisfy the If Left(tbl.Name, 2) = "BR" criteria; however, when I run Step 2, only 8 of those tables are appended. All 447 tables have identical structure but a varying number of records.

Does anyone know why not all of the data would be appended? I've included the code I'm using.


Step 1

Private Sub cmdBatchFile_Click()
    Dim MYDB As Database, MyTbl As Recordset
    Set MYDB = CurrentDb()
    Set MyTbl = MYDB.OpenRecordset("BatchImport", dbOpenTable)
    MyTbl.MoveFirst
    Do Until MyTbl.EOF
        DoCmd.TransferDatabase acImport, MyTbl("SourceType"), MyTbl("FilePath"), _
        acTable, MyTbl("SourceName"), MyTbl("FileName"), False
        MyTbl.MoveNext
    Loop
    MyTbl.Close
    MsgBox "All hourly databases have been imported"
End Sub

Step 2

Private Sub cmdAppendTable_Click()
On Error Resume Next

Dim tbl As TableDef, tables As TableDefs
Dim strSQL As String

Set tables = CurrentDb.TableDefs
For Each tbl In tables
    If Left(tbl.Name, 2) = "BR" Then
    strSQL = "INSERT INTO PosEchoes " & _
    "(record, sequence, transmitloc, period, subcode, tracktype, echotime, posx, posy, posz, h1record, h2record, h3record, h4record, hd1, hd2, hd3, hd4, source) " & _
       "SELECT record, sequence, transmitloc, period, subcode, tracktype, echotime, posx, posy, posz, h1record, h2record, h3record, h4record, hd1, hd2, hd3, hd4, '" & _
    tbl.Name & "' AS Source FROM " & tbl.Name
   CurrentDb.Execute strSQL
   If Err Then
     MsgBox "Error: " & Err.Number & _
        "Description: " & Err.Description
   End If
 End If
Next
    
MsgBox "Appending PosEchoes data from imported tables complete!"

End Sub

Step 3

Private Sub Command6_Click()
On Error Resume Next

Dim tbl As TableDef, tables As TableDefs
Dim strSQL As String

Set tables = CurrentDb.TableDefs
For Each tbl In tables
    If Left(tbl.Name, 2) = "BR" Then
    strSQL = "DROP Table " & tbl.Name
   CurrentDb.Execute strSQL
   If Err Then
     MsgBox "Error: " & Err.Number & _
        "Description: " & Err.Description
   End If
 End If
Next
    
MsgBox "All imported tables have been deleted!"
End Sub

I tried just making a simple union query in Access to see if it worked hard-coded, but I get an error "Query is too complex", which I assume has to do with the sheer number of unions I'm trying to do.

SELECT *, 'BRRPH1330500' AS Source FROM BRRPH1330500 
UNION
SELECT *, 'BRRPH1330600' AS Source FROM BRRPH1330600 
UNION
SELECT *, 'BRRPH1330700' AS Source FROM BRRPH1330700 
UNION
SELECT *, 'BRRPH1331000' AS Source FROM BRRPH1331000 
UNION
SELECT *, 'BRRPH1331200' AS Source FROM BRRPH1331200 
UNION
SELECT *, 'BRRPH1331500' AS Source FROM BRRPH1331500 
UNION
SELECT *, 'BRRPH1341200' AS Source FROM BRRPH1341200 
UNION
SELECT *, 'BRRPH1341300' AS Source FROM BRRPH1341300 
UNION
SELECT *, 'BRRPH1341400' AS Source FROM BRRPH1341400 
UNION
SELECT *, 'BRRPH1341500' AS Source FROM BRRPH1341500 
UNION
SELECT *, 'BRRPH1341600' AS Source FROM BRRPH1341600 
UNION
SELECT *, 'BRRPH1341700' AS Source FROM BRRPH1341700 
UNION
SELECT *, 'BRRPH1341800' AS Source FROM BRRPH1341800 
UNION
SELECT *, 'BRRPH1341900' AS Source FROM BRRPH1341900 
UNION
SELECT *, 'BRRPH1342000' AS Source FROM BRRPH1342000 
UNION
SELECT *, 'BRRPH1342100' AS Source FROM BRRPH1342100 
UNION
SELECT *, 'BRRPH1342200' AS Source FROM BRRPH1342200 
UNION
SELECT *, 'BRRPH1342300' AS Source FROM BRRPH1342300 
UNION
SELECT *, 'BRRPH1350000' AS Source FROM BRRPH1350000 
UNION
SELECT *, 'BRRPH1350100' AS Source FROM BRRPH1350100 
UNION
SELECT *, 'BRRPH1350200' AS Source FROM BRRPH1350200
etc....

 

[JonFer]

Check PosEchoes for valid value restrictions and unique indexes.

For troubleshooting, you could create a temporary table and insert a record into it after each insert query completes to track the table that was just inserted and the total rows after the insert.

John

http://www.interautomateconsulting.com

 

[tubbers]

I did find one problem with the unique indexes and changed the code in Step 2 to:

Private Sub cmdAppendTable_Click()
On Error Resume Next

Dim tbl As TableDef, tables As TableDefs
Dim strSQL As String

Set tables = CurrentDb.TableDefs
For Each tbl In tables
    If Left(tbl.Name, 2) = "BR" Then
    strSQL = "INSERT INTO PosEchoes " & _
    "(sequence, transmitloc, period, subcode, tracktype, echotime, posx, posy, posz, h1record, h2record, h3record, h4record, hd1, hd2, hd3, hd4, source) " & _
       "SELECT sequence, transmitloc, period, subcode, tracktype, echotime, posx, posy, posz, h1record, h2record, h3record, h4record, hd1, hd2, hd3, hd4, '" & _
    tbl.Name & "' AS Source FROM " & tbl.Name
   CurrentDb.Execute strSQL
   If Err Then
     MsgBox "Error: " & Err.Number & _
        "Description: " & Err.Description
   End If
 End If
Next
    
MsgBox "Appending PosEchoes data from imported tables complete!"

End Sub

Now I get 427 of the tables' data appended which is much better than 8! That must have been the problem all along. I will have to see what is different about the 20 tables that were not appended.

Thanks for your help.

 

[PHV]

If by chance you have some table name with embedded space, you may replace this:
AS Source FROM " & tbl.Name
By this:
AS Source FROM [" & tbl.Name & "]"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

You may also have problems with zero-length strings ("") or Nulls in a field and these are not allowed in the corresponding field in the PosEchoes table. It would need to be in every record in the BR--- table, though.


John

http://www.interautomateconsulting.com