Results 1 to 6 of 6

Thread: How do I change "Command text" in "Connection Properties" in a Database query

  1. #1
    Neophyte SimonAndersson's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    365

    Question How do I change "Command text" in "Connection Properties" in a Database query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi guys,

    I have pretty much no experience with VBA programming, so I hope one of you can be helpful.

    I use a windows PC and microsoft office 365.

    In my job I am using Database queries to get accounting data from our ERP to Excel and a defined table.
    The outcome of those queries are defined of the "Command text" in the "Connection properties".
    And my question is, do any of you know a way of changing this Command text, using VBA macros?

    I need to be able to change either the entire text or specific parts of it, from 1 number to another (some times 2 digits and some time 3 digits).

    The example could be, that the Command text is "SELECT REGNSKAB16.ACCGROUP, REGNSKAB16.ACCOUNT, REGNSKAB16.NAME, REGNSKAB16.COSTCENTER, REGNSKAB16.ACCYEAR, REGNSKAB16.OBJECTIVE1, REGNSKAB16.OPENBABAS, REGNSKAB16.JANUAR, REGNSKAB16.FEBRUAR, REGNSKAB16.MARTS, REGNSKAB16.APRIL, REGNSKAB16.MAJ, REGNSKAB16.JUNI, REGNSKAB16.JULI, REGNSKAB16.AUGUST, REGNSKAB16.SEPTEMBER, REGNSKAB16.OKTOBER, REGNSKAB16.NOVEMBER, REGNSKAB16.DECEMBER, REGNSKAB16.JAN2016, REGNSKAB16.FEB2016, REGNSKAB16.MAR2016, REGNSKAB16.ACCTYPE
    FROM DKMAHE01.SGLQDATFIN.REGNSKAB16 REGNSKAB16
    "

    And I would like the text to change to for an example: "SELECT REGNSKAB11.ACCGROUP, REGNSKAB11.ACCOUNT, REGNSKAB11.NAME, REGNSKAB11.COSTCENTER, REGNSKAB11.ACCYEAR, REGNSKAB11.OBJECTIVE1, REGNSKAB11.OPENBABAS, REGNSKAB11.JANUAR, REGNSKAB11.FEBRUAR, REGNSKAB11.MARTS, REGNSKAB11.APRIL, REGNSKAB11.MAJ, REGNSKAB11.JUNI, REGNSKAB11.JULI, REGNSKAB11.AUGUST, REGNSKAB11.SEPTEMBER, REGNSKAB11.OKTOBER, REGNSKAB11.NOVEMBER, REGNSKAB11.DECEMBER, REGNSKAB11.JAN2016, REGNSKAB11.FEB2016, REGNSKAB11.MAR2016, REGNSKAB11.ACCTYPE
    FROM DKMAHE01.SGLQDATFIN.REGNSKAB11 REGNSKAB11
    "

    To describe it, the first command text makes the query download data from company 16 and the second command text from company 11.

    Is this possible? If so, how?

    The connection string is: "DSN=AS400;" and the command type is: "SQL", and it is placed in "Sheet1".

    I look forward to reading and testing your answers.

    - Simon.

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,999
    Articles
    0
    Excel Version
    365
    Record a macro either of you creating such a query in Excel from scratch, or (preferably) of you adjusting the command text in connection properties of the query.
    I think it should record something. If so post the resulting macro code here.
    [I'd expect to see among the recorded code the text .CommandText = followed by something, and it's that something I'd be looking to adjust programmatically.
    I'd maybe start with a string such as:
    "SELECT REGNSKAB¬.ACCGROUP, REGNSKAB¬.ACCOUNT, REGNSKAB¬.NAME, REGNSKAB¬.COSTCENTER, REGNSKAB¬.ACCYEAR, REGNSKAB¬.OBJECTIVE1, REGNSKAB¬.OPENBABAS, REGNSKAB¬.JANUAR, REGNSKAB¬.FEBRUAR, REGNSKAB¬.MARTS, REGNSKAB¬.APRIL, REGNSKAB¬.MAJ, REGNSKAB¬.JUNI, REGNSKAB¬.JULI, REGNSKAB¬.AUGUST, REGNSKAB¬.SEPTEMBER, REGNSKAB¬.OKTOBER, REGNSKAB¬.NOVEMBER, REGNSKAB¬.DECEMBER, REGNSKAB¬.JAN2016, REGNSKAB¬.FEB2016, REGNSKAB¬.MAR2016, REGNSKAB¬.ACCTYPE FROM DKMAHE01.SGLQDATFIN.REGNSKAB¬ REGNSKAB¬"
    then go about replacing all instances of the "¬" character with your 2 or 3 digit number.]

    Ideally attach the actual workbook (even if it can't connect to your ERP).
    There may be a better way with Power Query too…
    Last edited by p45cal; 2021-09-24 at 02:22 PM.

  3. #3
    Neophyte SimonAndersson's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    365

    Reply to Thread

    Hi p45cal,

    Thank you for replying.

    I tried to record me changing the command text and it made following code:

    Code:
    Sub Change_Command_text()
    '
    ' Change_Command_text Macro
    '
    
    
    '
        With ActiveWorkbook.Connections("Query from AS400").ODBCConnection
            .BackgroundQuery = True
            .CommandText = Array( _
            "SELECT REGNSKAB11.ACCGROUP, REGNSKAB11.ACCOUNT, REGNSKAB11.NAME, REGNSKAB11.COSTCENTER, REGNSKAB11.ACCYEAR, REGNSKA" _
            , _
            "B11.OBJECTIVE1, REGNSKAB11.OPENBABAS, REGNSKAB11.JANUAR, REGNSKAB11.FEBRUAR, REGNSKAB11.MARTS, REGNSKAB11.APRIL, RE" _
            , _
            "GNSKAB11.MAJ, REGNSKAB11.JUNI, REGNSKAB11.JULI, REGNSKAB11.AUGUST, REGNSKAB11.SEPTEMBER, REGNSKAB11.OKTOBER, REGNSK" _
            , _
            "AB11.NOVEMBER, REGNSKAB11.DECEMBER, REGNSKAB11.JAN2016, REGNSKAB11.FEB2016, REGNSKAB11.MAR2016, REGNSKAB11.ACCTYPE" & Chr(13) & "" _
            , "" & Chr(10) & "FROM DKMAHE01.SGLQDATFIN.REGNSKAB11 REGNSKAB11")
            .CommandType = xlCmdSql
            .Connection = "ODBC;DSN=AS400;"
            .RefreshOnFileOpen = False
            .SavePassword = False
            .SourceConnectionFile = ""
            .SourceDataFile = ""
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
        End With
        With ActiveWorkbook.Connections("Query from AS400")
            .Name = "Query from AS400"
            .Description = ""
        End With
        ActiveWorkbook.Connections("Query from AS400").Refresh
        Range("A1").Select
    End Sub
    In the "Command text section" I changed it manually to REGNSKAB11 from REGNSKAB16. But I would like if the code could extract from a specific cell or something like that, so no matter if it says REGNSKAB11,
    REGNSKAB16 or REGNSKAB34 in the specific cell, it would insert the specific string as above.
    Additionally I could have a cell that had the entire Command text, that had dynamic numbers in the end of every "REGNSKAB" that the VBA code could use as reference.

    i am just brainstorming, surely you'll have a great solution.

    I cannot attach the Excel fil unfortunately.

    I look forward to hear from you.

    - Simon.
    Last edited by AliGW; 2021-09-27 at 09:24 AM. Reason: Code tags added.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,711
    Articles
    0
    Excel Version
    MS365 Sub (Insider) Win11
    I've added code tags for you as required by our forum rules - please remember to do this yourself in future. Thanks.

    Why are you unable to attach a workbook? Are you having issues with how to do it? If not, then you can provide a small subset of your real data (desensitised).
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,999
    Articles
    0
    Excel Version
    365
    Try something along these lines:
    Code:
    Sub Change_Command_text()
    StrStart = "SELECT REGNSKAB¬.ACCGROUP, REGNSKAB¬.ACCOUNT, REGNSKAB¬.NAME, REGNSKAB¬.COSTCENTER, REGNSKAB¬.ACCYEAR, REGNSKAB¬.OBJECTIVE1, REGNSKAB¬.OPENBABAS, REGNSKAB¬.JANUAR, REGNSKAB¬.FEBRUAR, REGNSKAB¬.MARTS, REGNSKAB¬.APRIL, REGNSKAB¬.MAJ, REGNSKAB¬.JUNI, REGNSKAB¬.JULI, REGNSKAB¬.AUGUST, REGNSKAB¬.SEPTEMBER, REGNSKAB¬.OKTOBER, REGNSKAB¬.NOVEMBER, REGNSKAB¬.DECEMBER, REGNSKAB¬.JAN2016, REGNSKAB¬.FEB2016, REGNSKAB¬.MAR2016, REGNSKAB¬.ACCTYPE FROM DKMAHE01.SGLQDATFIN.REGNSKAB¬ REGNSKAB¬"
    CoNo = 16    'get this value from wherever you want eg.:
    'CoNo=Sheets("YourSheetName").Range("C3").value 'adjust this
    CmdTxt = Replace(StrStart, "¬", CoNo)
    With ActiveWorkbook.Connections("Query from AS400")
      .ODBCConnection.CommandText = CmdTxt
      .Refresh
    End With
    End Sub
    Note the comments in the code above.
    If you want to use more than the 2/3 digit number (the whole company code) then it should be fairly obvious what changes you'll need to make. If not, come back.

  6. #6
    Neophyte SimonAndersson's Avatar
    Join Date
    Sep 2021
    Posts
    3
    Articles
    0
    Excel Version
    365
    THANK YOU SO MUCH! It works perfect!

    - Simon.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •