OLEDBConnection Properties
 

How does one specify OLEDBConnection properties and then use it to create a
QueryTable? The idea is to create a Query Table based on data retrieved via
an OLE DB driver and to update a pivot table in another sheet based on the
created QT (the latter I can do with no problems). The following does not
seem to work, since there is no way that I can find to set a reference to the
actual connection.

Thanx,
Sergey

Dim cn As OLEDBConnection
Dim qt As QueryTable
Dim sqlstring As String

Worksheets("Sheet2").Activate

For Each qt In ActiveSheet.QueryTables
qt.Delete
Next

Dim dtFrom As String
Dim dtTo As String
dtFrom = <set parm value
dtTo = <set parm value

sqlstring = <some SQL statement using the above parms

<how do I set the reference to cn???

With cn
.AlwaysUseConnectionFile = False
.BackgroundQuery = False
.CommandText = sqlstring
.CommandType = xlCmdSql
.Connection = "OLEDB;Provider=IBMDA400;" & _
"DSN=DSNNAME;" & _
"UID=USER;PWD=PASSWORD;"
.EnableRefresh = False
.MaintainConnection = True
.RefreshOnFileOpen = False
.RefreshPeriod = 0
.RetrieveInOfficeUILang = False
.RobustConnect = xlNever
'.SavePassword = True
.ServerCredentialsMethod = CredentialsMethodIntegrated
End With


With ActiveSheet.QueryTables.Add( _
Connection:=cn, _
Destination:=Range("A1"))
.Name = "Query_Name"
.AdjustColumnWidth = True
.BackgroundQuery = False
.EnableEditing = False
.EnableRefresh = True
.FieldNames = True
.PreserveColumnInfo = False
.PreserveFormatting = True
.RefreshPeriod = 0
.RefreshStyle = xlInsertDeleteCells
.SaveData = False

.Refresh

End With

OLEDBConnection Properties
 

Nevermind. I went with just refreshing the existing querytable and
connection, not the ideal situation, but oh well.



"krysolov" wrote:

How does one specify OLEDBConnection properties and then use it to create a
QueryTable? The idea is to create a Query Table based on data retrieved via
an OLE DB driver and to update a pivot table in another sheet based on the
created QT (the latter I can do with no problems). The following does not
seem to work, since there is no way that I can find to set a reference to the
actual connection.

Thanx,
Sergey

Dim cn As OLEDBConnection
Dim qt As QueryTable
Dim sqlstring As String

Worksheets("Sheet2").Activate

For Each qt In ActiveSheet.QueryTables
qt.Delete
Next

Dim dtFrom As String
Dim dtTo As String
dtFrom = <set parm value
dtTo = <set parm value

sqlstring = <some SQL statement using the above parms

<how do I set the reference to cn???

With cn
.AlwaysUseConnectionFile = False
.BackgroundQuery = False
.CommandText = sqlstring
.CommandType = xlCmdSql
.Connection = "OLEDB;Provider=IBMDA400;" & _
"DSN=DSNNAME;" & _
"UID=USER;PWD=PASSWORD;"
.EnableRefresh = False
.MaintainConnection = True
.RefreshOnFileOpen = False
.RefreshPeriod = 0
.RetrieveInOfficeUILang = False
.RobustConnect = xlNever
'.SavePassword = True
.ServerCredentialsMethod = CredentialsMethodIntegrated
End With


With ActiveSheet.QueryTables.Add( _
Connection:=cn, _
Destination:=Range("A1"))
.Name = "Query_Name"
.AdjustColumnWidth = True
.BackgroundQuery = False
.EnableEditing = False
.EnableRefresh = True
.FieldNames = True
.PreserveColumnInfo = False
.PreserveFormatting = True
.RefreshPeriod = 0
.RefreshStyle = xlInsertDeleteCells
.SaveData = False

.Refresh

End With