Re: SQLSetConnectAttr error with Excel - converting to an older database?

On Feb 23, 2:04 pm, "S Davis" <theseanda..._at_gmail.com> wrote:

> Further update, should anyone need this. This will look at an old
> (broken) query, grab the SQL from it, and assign new connection
> information that you input. If you dont know your connection
> information, it is as simple as creating a new query, and then running
> Charles' MsgBox.Connection to display it.
>
> Sub AddQT()
> Dim qt As QueryTable
> MsgBox ActiveSheet.QueryTables(1).CommandText
> MsgBox ActiveSheet.QueryTables(1).Connection
> sqlstring = ActiveSheet.QueryTables(1).CommandText
> connstring = _
>
> "ODBC;DSN=nameofdsn;UID=userid;PWD=password;SERVER=dsnid.server.com;"
> With ActiveSheet.QueryTables.Add(connstring, _
>         Destination:=Range("I1"), Sql:=sqlstring)
>     .Refresh
> End With
> End Sub

Great that it work for you.

A bit OT. Example of creating a new querytable from a tab delimited file.
With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\OracleLog \Report.txt", Destination:=.Range("A1"))

.Name = "Page" & Format(intSheetCount)
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False

    '.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1)

    '.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With
Range("A2").Select
ActiveWindow.FreezePanes = True

It is possible to use the same type of approach for an Oracle database query also.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Feb 23 2007 - 13:17:47 CST