Excel Problem

[drinese18]

I am basically trying to get data from an SQL database, you can see my code
below:

Sub SPICEdownload_indexvalue()
'On Error GoTo datapullerr
Dim wk As Workbook
Dim data As Worksheet, para As Worksheet
Dim Sql$
Set wk = ThisWorkbook
Set data = wk.Sheets("SPICE idxval")

Dim wrkodbc As Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim r As Range
Dim ts As String
Set wrkodbc = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set db = wrkodbc.OpenDatabase("Spice", , ,
"ODBC;DSN=SPICE;UID=EQIQRY;pwd=eqiqry;SERVER=PSDR1.MHF2.MHF.MHC;")
ts = ""
i = 3
While Trim(data.Cells(i, 1)) <> ""
ts = ts & IIf(ts <> "", "or ", "") & "(Index_id = " & Trim(data.Cells(i,
1)) & " and index_date='" & _
Format(Trim(data.Cells(i, 2)), "dd-MMM-yyyy") & "') "

i = i + 1
Wend

Sql = "select a.index_id, a.index_date, a.close_index_value,
b.index_dividend from daily_index_values a, index_dividend b where" & ts & "
a.index_id = b.index_id order by index_id"
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)

i = 3
While Trim(data.Cells(i, 1)) <> ""

data.Range("A" & i & ":C" & i & "" & i) = Array(rs!index_id,
rs!index_date, rs!close_index_value, rs!index_dividend)
rs.MoveNext
i = i + 1
Wend
rs.Close
db.Close

Set sb = Nothing
Set rs = Nothing
End Sub


Private Sub CommandButton1_Click()
SPICEdownload_indexvalue
End Sub

My code works partially, if I put a more simple SQL statement in it to just
pull up values from one table it works ok and posts the values to the Excel
sheet, but when I try to create a query through the statement, it basically
brings up an error, the SQL statement work perfectly alone, but when it is
incorporated into Excel it just brings up an error at the Snapshot section,
I've tried everything and I am currently at a loss, can anyone help me with
this, any help will be greatly appreciated,

Thank you.

 

[HatesIT]

change it to
---
Debug.Print Sql
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)
---
and look at the Sql string.
I think you at least need a space after where

where" & ts &
where " & ts & < ---

Plus, shouldn't the date be tagged with # or is it really a text field?

hth

 

[Matthew Pfluger]

Hey HatesIT,

Can I ask what profession you are in? <grin>

Matthew Pfluger
Design Engineer

 

[HatesIT]

I make reports of reports, doesn't everyone?

Hey HatesIT,

Can I ask what profession you are in? <grin>

Matthew Pfluger
Design Engineer

 

[drinese18]

I tried it but it's still bringing up the same error, the error basically
says OBDC called failed, in which the connection string to me is right
because I've tried it in another code and it works ok and the SQL statement
is correct as well since I've tried that seperately and it works perfectly,
but once everything is incorporated together it brings up that error and when
I debug it, it refers to this line of code:

Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)

So still not sure

 

[HatesIT]

I'll try to stick with you...

what is the error? Could be me but I didn't see it...

can you post the SQL?
when you say you've tried the SQL and it worked, where do you try it from?
did you try tagging the dates with # as in #1/24/2007#?
does your more simple SQL use a date filter?

 

[drinese18]

the error basically says, ODBC call failed, my sql statement is:

select a.index_id, a.index_date, a.close_index_value, b.index_dividend from
daily_index_values a, index_dividend b where " & ts & " a.index_id =
b.index_id order by index_id

I tried it using Oracle and it works ok, no I didn't tag the dates with that
because it is not necessarily a specific date, the dates basically are drawn
from a column within the spreadsheet, so when i enter the dates there and the
id's it basically should bring up the values and dividend from those tables
according to the id and date

 

[HatesIT]

I'll assume you have some reason why your not showing the full SQL.
If it works without the " & ts & " then I would say the problem is in your
ts string build.
Other than that all I could offer is to try a different type option or run
it with the default. That should be "dbOpenForwardOnly"

You could also try and re-post this over in data.odbc but watch out for the
tumbleweeds...