Database

CommandText property

Returns or sets the command string for the specified data source. Read/write Variant.
You should use the CommandText property instead of the SQL property, which now exists primarily for compatibility with earlier versions of Microsoft Excel. If you use both properties, the CommandText property's value takes precedence.
For OLE DB sources, the CommandType property describes the value of the CommandText property.
For ODBC sources, the CommandText property functions exactly like the SQL property, and setting the property causes the data to be refreshed.

This example sets the command string for the first query table's ODBC data source. Note that the command string is an SQL statement.

Set qtQtrResults = _ 
    Workbooks(1).Worksheets(1).QueryTables(1) 
With qtQtrResults 
    .CommandType = xlCmdSql 
    .CommandText = _ 
        "Select ProductID From Products Where ProductID < 10" 
    .Refresh 
End With 

CommandType property

Returns or sets one of the XlCmdType constants listed in the following table. The constant that is returned or set describes the value of the CommandText property. The default value is xlCmdSQL. Read/write XlCmdType.
You can set the CommandType property only if the value of the QueryType property for the query table or PivotTable cache is xlOLEDBQuery.
If the value of the CommandType property is xlCmdCube, you cannot change this value if there is a PivotTable report associated with the query table.

Connection property

Returns or sets a string that contains one of the following: OLE DB settings that enable Microsoft Excel to connect to an OLE DB data source; ODBC settings that enable Microsoft Excel to connect to an ODBC data source; a URL that enables Microsoft Excel to connect to a Web data source; the path to and file name of a text file, or the path to and file name of a file that specifies a database or Web query. Read/write Variant.
Setting the Connection property doesn't immediately initiate the connection to the data source. You must use the Refresh method to make the connection and retrieve the data.
When using an offline cube file, set the UseLocalConnection property to True and use the LocalConnection property instead of the Connection property.
Alternatively, you may choose to access a data source directly by using the Microsoft ActiveX Data Objects (ADO) library instead.

This example supplies new ODBC connection information for the first query table on the first worksheet.

Worksheets(1).QueryTables(1).Connection:="ODBC;DSN=96SalesData;UID=Rep21;PWD=NUyHwYQI;"