[SOLVED] How to concatenate a string line in Select clause in DB connection of VBA Excel?

How to concatenate a string line in Select clause in DB connection of VBA Excel?

I have a simple DB query that selects data from a worksheet and pass it to 2 different worksheets over 2 query stages. I believe it can be simpler, but I'm trying to embed (VField) inside the select clause to avoid repeating it again to add the extra 2 fields.

Here's the code I have to embed the VField in the select clause.

 Option Explicit
Sub Demmo()
'Declare Variables
Dim con, Rs As Object, sql As String
Dim VField As String
On Error GoTo errClose
'Connection string
Set con = CreateObject("ADODB.Connection")
With con
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
    "Extended Properties=""Excel 12.0 Xml;HDR=Yes;IMEX=1"";"
    .Open
       End With
'Select clause and VField = nothing to feed summary worksheet
sql = "SELECT T1.[ID],T1.[ID Name], " & _
      VField & _
      "T1.[country],T1.[Unit reference] " & _
      ",sum(T1.[Total]) as [Total] " & _
      "FROM [Data$] T1 " & _
      "WHERE T1.[ID Name] = 'XX' " & _
      "GROUP BY T1.[ID],T1.[ID Name], " & _
      VField & _
      "T1.[country],T1.[Unit reference]"
'set recordset to execute connection string
         Set PRs = PConn.Execute(sql)
'Pass the data from recordset to summary sheet
Sheets("Summary").Cells(1, 2).CopyFromRecordset Rs
'reference VField to Inv.Number and Inv.Date fields
    VField = "T1.[Inv Number],T1.[Inv Date], "
'Re-run select clause (sql) to feed Detilas worksheet
         sql = sql ' still read VField as nothing and i need sql to read it as declared 
         Set PRs = PConn.Execute(sql)
'Pass the data from recordset to Details sheet
Sheets("Details").Cells(1, 2).CopyFromRecordset Rs
'close record set, connection and free memory
        Rs.Close
       con.Close
     Set Rs = Nothing
     Set con = Nothing
 End
'close recordset on error
errClose:
        If Not (Rs Is Nothing) Then
        If (Rs.State And 1) = 1 Then Rs.Close
        Set Rs = Nothing
           End If
 MsgBox "Error " & Err.Number & " (" & Err.Description & ")  " & Err.Source
End Sub

Solution

If you create a function:

Function GetSql(VField As String) As String
    GetSql = "SELECT T1.[ID],T1.[ID Name], " & _
       VField & _
      "T1.[country],T1.[Unit reference] " & _
      ",sum(T1.[Total]) as [Total] " & _
      "FROM [Data$] T1 " & _
      "WHERE T1.[ID Name] = 'XX' " & _
      "GROUP BY T1.[ID],T1.[ID Name], " & _
      VField & _
      "T1.[country],T1.[Unit reference]"
End Function

Then you can call (eg):

Set PRs = PConn.Execute(GetSql(""))

Set PRs = PConn.Execute(GetSql("T1.[Inv Number],T1.[Inv Date], "))