SQL Problem

D

drinese18

I am having trouble with my sql statement, I am basically trying to download
some information from an SQL database, everytime I try to download something
an error occurs at the Snapshot part of the statement, 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=DSN;UID=username;pwd=password;SERVER=server;")
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 & "
and a.index_date = b.index_date order by index_id"
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)

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

data.Range("A" & i & ":C" & i & ":D" & 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

Basically my error comes up in this line of code:

Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)

I've tried everything that I know to try and debug it but it is still coming
up with the same error, if anyone can help me as soon as possible that would
be great, thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Problem 7
Connection problem using DAO 3
SQL and Excel problem 0
Excel 16 bit limitation 2
Downloading into a column 0
SQL and Excel 0
DAO connection problem 0
Data type conversion error 0

Top