SQL Query in VBA with variable number of columns

[RickSchultz]

Hi! I'm trying to generate code that allows me to use a fixed SQL query, embedded in VBA code, to pull data from a database.

However, depending on the user, some of the columns may not exist in their version of the database. Where the column doesn't exist, I'd like to just have a '' (a blank).

I've declared a variable for each column that may/may not exist. The query works fine if the column DOES exist. However, I get an SQL error if the column does not exist. However, the error indicates a syntax error, that is, my code is passing in bad data to the query.

So, my question is, how do I pass '' into the query where the column doesn't exist? Here's my code:

        ' Create a recordset object.
        Dim rsDB As New ADODB.Recordset
        Dim Acnt3 As String    <----THESE ARE THE VARIABLE COLUMNS
        Dim Acnt4 As String
        Dim Acnt5 As String
        Dim Acnt6 As String
        Dim Acnt7 As String
        Dim Acnt8 As String
        If Sheet3.Range("E5").Value <> "''" Then  <---MY ATTEMPT TO PASS '' TO THE QUERY
           Acnt3 = Sheet3.Range("E5").Value
           Else: Acnt3 = "''"
           End If
        If Sheet3.Range("E6").Value <> "''" Then
           Acnt4 = Sheet3.Range("E6").Value
           Else: Acnt4 = "' '"
           End If
        If Sheet3.Range("E7").Value <> "''" Then
           Acnt5 = Sheet3.Range("E7").Value
           Else: Acnt5 = "''"
           End If
        If Sheet3.Range("E8").Value <> "''" Then
           Acnt6 = Sheet3.Range("E8").Value
           Else: Acnt6 = "''"
           End If
        If Sheet3.Range("E9").Value <> "''" Then
           Acnt7 = Sheet3.Range("E9").Value
           Else: Acnt7 = "''"
           End If
        If Sheet3.Range("E10").Value <> "''" Then
           Acnt8 = Sheet3.Range("E10").Value
           Else: Acnt8 = "''"
           End If
           
        With rsDB
            ' Assign the Connection object.
            .ActiveConnection = cnDB
            ' Extract the required records.
            .Open "select '" + DBName + "' as 'DatabaseName', perioddt as 'Document Date' ,perioddt as 'Post Date' ,perioddt as 'Due Date'" _
            + ",'' as 'Sales Person ID' ,'' as 'UserID for Posting User' ,ACTNUMBR_1 as 'Segment 1' ,ACTNUMBR_2 as 'Segment 2'" _
            + "," + Acnt3 + " as 'Segment 3','' as 'Segment 4', '' as 'Segment 5', '' as 'Segment 6', '' as 'Segment 7', '' as 'Segment 8'" _
            + ",'' as 'Customer or Vendor ID' ,'' as 'Document Number' ,'Budget' as 'Document Type'" _
            + ",YEAR1 as 'Entry Year' ,DEX_ROW_ID as 'Entry Number', DEX_ROW_ID as 'Entry Line Number' ,BUDGETAMT as 'Amount','Budget Entry' as 'Description'" _
            + ",'Financial' as 'Series' ,'None' as 'EntityType' ,BUDGETID as 'TransType' from gl00201"

Thanks for looking at this. I really appreciate the great help I've had here!

 

[agrawal.meet]

what database are you using. Use system table syscolumn to find out if the column exists. If exists then append the column to your string otherwise remove it.

 

[Blade Hunter]

try replacing "''" with """" (4 double quotes instead of double single single double)

 

[RickSchultz]

Agrawal,

I'm afraid each of the columns must exist in the query, even if it may not exist in the database, as it WILL exist in the database for some users.

Blade,

I'll give that a shot this evening. Thanks!