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

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"drinese18" wrote in message ...
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

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

"Ron de Bruin" wrote:

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"drinese18" wrote in message ...
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

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




"drinese18" wrote:

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