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 & "" & 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
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 & "" & 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