Option Explicit

Dim strFilePath As String
Dim strFolder As String
Dim strQuery As String
   
Sub makeQueryTable()
   
    strFilePath = ThisWorkbook.FullName
    strFolder = ThisWorkbook.Path
    strQuery = "SELECT s1.품목,  s1.규격,  s1.단가 FROM DBinput1  s1, DBinput2  s2 WHERE s1.품목 = s2.품목"

    Dim rngTarget As Range
    Set rngTarget = ActiveSheet.Range("B2")
   
    Dim qt As queryTable
    If ActiveSheet.QueryTables.Count >= 1 Then
        For Each qt In ActiveSheet.QueryTables
            qt.Delete
        Next qt
    End If

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=" & strFilePath & " ;DefaultDir=" & strFolder & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5" _
        ), Array(";")), Destination:=rngTarget)
        .CommandText = Array(strQuery)
        .Name = "abyulQuery001"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Sub ShowDB1()
    strQuery = "SELECT * FROM DBinput1"
    Call changQuery
End Sub

Sub ShowDB2()
    strQuery = "SELECT * FROM DBinput2"
    Call changQuery
End Sub

Sub ShowDBAll()
    strQuery = "(SELECT * FROM DBinput1) UNION ALL (SELECT * FROM DBinput2)"
    Call changQuery
End Sub

Sub ShowDoubleCode()
    strQuery = "SELECT s1.품목,  s1.규격,  s1.단가 FROM DBinput1  s1, DBinput2  s2 WHERE s1.품목 = s2.품목"
    Call changQuery
End Sub

Private Function changQuery()
    If strFilePath = "" Then strFilePath = ThisWorkbook.FullName
    If strFolder = "" Then strFolder = ThisWorkbook.Path
    With ActiveSheet.QueryTables(1)
        .Connection = Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=" & strFilePath & " ;DefaultDir=" & strFolder & ";DriverId=790;MaxBufferSize=2048;PageTimeout=5" _
        ), Array(";"))
        .CommandText = Array(strQuery)
        .Refresh BackgroundQuery:=False
    End With
End Function