Excel VBA ODBC ERROR

[harwooddale]

Hi,
I am trying to query an oracle database using ODBC.
I am passing an input variable from an input box to the
sql and it fails when I reaches the refresh background query line.
Any Ideas?
The code I am using is

Dim Message, Title, Default, MyValue
Message = "Enter the Table to query" ' Set prompt.
Title = "InputBox" ' Set title.
Default = "1" ' Set default.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in ORACLE901i};SERVER=SERVER;UID=USER;PWD=PASSWD;DBQ=DBQ;DBA=W;APA=T;EXC=F;XSM=Defaul" _
), Array( _
"t;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;" _
)), Destination:=Range("A1&quot)
.Sql = Array("select * from " & MyValue & " ;&quot
.FieldNames = True
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=True
.SavePassword = True
.SaveData = True
End With
End Sub


Thanks in advance

James

 

[xlbo]

It's failing there 'cos that's the line that actually executes the query. Typically the areas that cause this are either the connection string or the SQL string
Without knowing what "myValue" is, I can't really comment on your SQL string but it looks simple enough. Try losing the ; at the end - none of my "ODBC type SQL query thingies" have ; at the end....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[TheEntertainer]

James,

Try looking at thread707-550473.

It wouldn't happen to be in Excel 2000? I was having a similar problem and XLBO pointed in in the right direction.


An alternative which I now use is:

Sub OpenConnectionX()
Dim counter As Integer
Dim rstTemp As Recordset
Dim WRKODBC As Workspace
Set WRKODBC = CreateWorkspace("NewODBCWorkspace", _
"USERID", "PASSW", dbUseODBC)

Dim dbsTEST As Database
Dim conPubs As Connection

Set dbsTEST = WRKODBC.OpenDatabase("TEST&quot

Set conPubs = WRKODBC.OpenConnection("TEST", _
dbDriverNoPrompt, True, "ODBC;DSN=TEST;&quot

MsgBox "Opening Connection to " & dbsTEST.Name

textall= Whatever your SQL is

On Error Resume Next
Set rstTemp = dbsTEST.OpenRecordset(textall, , dbReadOnly)

' Output results to Excel
Do While Not rstTemp.EOF
With rstTemp

.MoveNext



If rstTemp.EOF = False Then
ActiveSheet.Range("a" & counter).Value = rstTemp.Fields(0)
ActiveSheet.Range("b" & counter).Value = rstTemp.Fields(1)
ActiveSheet.Range("C" & counter).Value = rstTemp.Fields(2)
ActiveSheet.Range("D" & counter).Value = rstTemp.Fields(3)
ActiveSheet.Range("E" & counter).Value = rstTemp.Fields(4)
ActiveSheet.Range("F" & counter).Value = rstTemp.Fields(5)
ActiveSheet.Range("G" & counter).Value = rstTemp.Fields(6)
ActiveSheet.Range("H" & counter).Value = rstTemp.Fields(7)
ActiveSheet.Range("I" & counter).Value = rstTemp.Fields(8)
counter = counter + 1
Else
End If
End With
Loop

Clear memory

rstTemp.Close
conPubs.Close

Set WORKOBDC = Nothing
Set conPubs = Nothing

TheEntertainer