Connection problem using DAO

[drinese18]

I'm not too familiar much with the DAO syntax, I basically want to download
information from a SQL database into certain columns of a spreadsheet my code
can be seen 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("S&P 500 PKA NET TR 15%")

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 S&P_500_PKA Ethical, S&P_500_PKA_Ethica_Indx_Div WHERE order
by index_id"
Set rs = db.OpenRecordset(Sql, dbOpenSnapshot)

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

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

Set sb = Nothing
Set rs = Nothing
End Sub


I'm not sure what is wrong with my code but basically its not downloading
into those columns, what am I doing wrong? Can anyone help me with this
please?
Any help would be greatly appreciated

 

[Ron de Bruin]

Check out this page, maybe you find good info there about DAO
http://www.erlandsendata.no/english/index.php?t=envbadac

 

[drinese18]

That kind a gave an idea about DAO but regardless it didn't really help much
because my problem lies within SQL itself, the thing is I don't remember how
to use DAO and SQL together in order to download data from the SQL database

 

[chelovik]

Try this code ... I've taken the liberty of replacing your call to a database
with a DAO.Connection instead, and am using a querydef to access your data.
I've also modified your query, which you hadn't completed, so give this a
go....

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("S&P 500 PKA NET TR 15%")

Dim wrkodbc As Workspace
Dim conSpice As DAO.Connection
Dim rs As DAO.Recordset
Dim qry As DAO.QueryDef
Dim r As Range
Dim connstr As String
'Dim ts As String

connstr = "ODBC;DSN=SPICE;UID=eqiqry;pwd=eqiqry;SERVER=PSDR1.MHF2.MHF.MHC;"
Set conSpice = wrkodbc.OpenConnection("Connection1", dbDriverNoPrompt,
False, connstr)

Set wrkodbc = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
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

sqlStatement = "SELECT S&P_500_PKA Ethical, S&P_500_PKA_Ethica_Indx_Div "
& _
"FROM <fill_in_your_Tablename> " & _
"ORDER By index_id"

Set qry = conSpice.CreateQueryDef("")
qry.Sql = sqlStatement

Set rs = qry.OpenRecordset

i = 3
While Trim(data.Cells(i, 1)) <> ""
data.Range("A" & i & ":C" & i).Value = _
Array(rs!index_id, rs!index_dividend, rs!close_index_dividend)
rs.MoveNext
i = i + 1
Wend
rs.Close
conSpice.Close

Set conSpice = Nothing
Set rs = Nothing
End Sub