VBA table Update doesn't write all data?

[CrazyJ]

First time post and long time viewer.
I have a split database that pulls information from two sources. I combine the data through several queries and then write a table with the finished product for use in Excel charts.

There are 230 records in qry_FTC_3 and only 187 records write. The difference is all records with a Year of 2020, also those records are all from one of the two data sources. I receive no errors, the data just doesn't write. I'm guessing somewhere up the chain of queries, the new data has something that doesn't match, but doesn't cause an error in Merge Queries or MS Access 2016?

Private Sub Command42_Click()
On Error GoTo Err_Handler

Dim sql12 As String

sql12 = "Delete tbl_FTC.* FROM tbl_FTC;"
CurrentDb.Execute (sql12)
Me.Refresh
Me.Requery

sql12 = "INSERT INTO tbl_FTC ([Day], [CostCode], [CostType], [IDNo], [Name], [Hrs], [UOM], [Costs]) SELECT [Day], [CostCode], [CostType], [IDNo], [Name], [Hrs], [UOM], [Costs] FROM qry_FTC_3;"
Debug.Print (sql12)
CurrentDb.Execute (sql12)

Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description
Resume Exit_Handler
End Sub

 

[CJ_London]

not really enough info to suggest a solution other than perhaps your data contains duplicates which are disallowed by a unique index in tbl_FTC

Note that day and name are both reserved words, using them as field names can produce unexpected results

Have you tried running the query by copying the sql into a query to see what happens then?

 

[CrazyJ]

not really enough info to suggest a solution other than perhaps your data contains duplicates which are disallowed by a unique index in tbl_FTC

Note that day and name are both reserved words, using them as field names can produce unexpected results

Have you tried running the query by copying the sql into a query to see what happens then?

Unfortunately no duplicates, each line is unique. The only data not writing is from the one source. I just checked your suggestion, and data was visible. I am also checking data types from the table to ensure numbers are numbers and dates are dates. No luck so far, I will change the reserved names to see if that helps. Thanks for the response.

 

[CJ_London]

another thing to try. Identify a record in qry_FTC_3 that has not imported and try to copy paste it into tbl_FTC - making sure the columns are in the same order. Hide columns that are not used. If it didn't import before, chances are an error will be generated,


You can also add dbfailonerror to your code so your code will generate an error

CurrentDb.Execute (sql12, dbfailonerror)

and perhaps disable your error handling for now

 

[CrazyJ]

another thing to try. Identify a record in qry_FTC_3 that has not imported and try to copy paste it into tbl_FTC - making sure the columns are in the same order. Hide columns that are not used. If it didn't import before, chances are an error will be generated,


You can also add dbfailonerror to your code so your code will generate an error

CurrentDb.Execute (sql12, dbfailonerror)

and perhaps disable your error handling for now

I was able to paste everything without error. Columns are in the exact same order. When I created the table I did it with "Make Table" to ensure everything was the same. Also tried your code, I had to write it like below to work, is this correct? I will try to remove each column one by one and see if it one that is causing the problem. Thanks for the suggestion.

CurrentDb.Execute (sql12), dbFailOnError

 

[CrazyJ]

I was able to paste everything without error. Columns are in the exact same order. When I created the table I did it with "Make Table" to ensure everything was the same. Also tried your code, I had to write it like below to work, is this correct? I will try to remove each column one by one and see if it one that is causing the problem. Thanks for the suggestion.

CurrentDb.Execute (sql12), dbFailOnError

Ok, I ran each column one by one and then ran all columns, removing one column left to right one by one. None of the data from the once source will write. I am going to try writing ONLY that data and see if I can. Not sure what the system doesn't like about doing a UNION with the different data sets.

 

[CJ_London]

perhaps take a closer look at your union query - it will remove duplicates if you are using UNION SELECT. To keep duplicates, use UNION SELECT ALL

if it is one 'source' that is the issue, check the source, perhaps there are illegal characters.

Also, change the order of your union query - you may have hit a limit somewhere along the way. Although if you run the query and see 230 records that is not likely the be the case.

With regards my suggestion to use

CurrentDb.Execute (sql12, dbfailonerror)

try

CurrentDb.Execute sql12, dbfailonerror