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

[SimonAndersson]

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.

[p45cal]

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…

[SimonAndersson]

Hi p45cal,

Thank you for replying.

I tried to record me changing the command text and it made following 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.

[AliGW]

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).

[p45cal]

Try something along these lines:

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.

[SimonAndersson]

THANK YOU SO MUCH! It works perfect!

- Simon.