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" <(E-Mail Removed)> wrote in message news:C7EBACD2-D2E6-44A7-8A70-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message news:C7EBACD2-D2E6-44A7-8A70-(E-Mail Removed)...
> > 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