union all dbf file/ table that start with a given pattern

Question

I'm currently using Microsoft OLE DB Provider and VB.net currently using this Query to create a view:

select * from tableYear2019
UNION 
select * from tableYear2020

Which works well to combine all. However the program recreate's another DBF file/ table every year.

Note: Both tables have same structure. It's my first time working with DBF files.

Answer 1

If you mean that you want to dynamically build the select string based on what year it is then this might work. I wouldn't spend too much time on this because things will change long before your code wears out.

This uses a string builder from System.Text (add an Imports at top of file) and an interpolated string (preceded by $).

Private Function BuildSelect() As String
    Dim year = Now.Year
    Dim years = Enumerable.Range(2020, year - 2019).ToArray
    Dim sb As New StringBuilder
    sb.Append($"select * from tableYear2019 ")
    For i = 0 To years.Length - 1
        sb.Append($"Union select * from tableYear{years(i)} ")
    Next
    Return sb.ToString
End Function