Connection problem using DAO
 

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

Connection problem using DAO
 

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

Connection problem using DAO
 

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

Connection problem using DAO
 

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