Re: are there limitations of SQL used in text driver via ADO

Thanks Dan.. do you know where I can get a list of commands which JET
supports?

"Daniel Crichton" wrote:

KaptainKidd wrote on Wed, 29 Mar 2006 23:57:02 -0800:

I have the following SQL query which I run from excel vba using ADO

SQL Query

select
Portfolio,
Sum(case when scenario_Name = 'EB_-0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Equity-10',
Sum(case when scenario_Name = 'EB_0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Equity+10',
Sum(case when scenario_Name = 'VB_-0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol-10',
Sum(case when scenario_Name = 'VB_-0.05' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol-5',
Sum(case when scenario_Name = 'VB_0.05' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol+5',
Sum(case when scenario_Name = 'VB_0.1' then
scenario_MV-Scenario_Stressed_Value else null end) as 'Vol+10'

From
filename.csv

Group By
Portfolio

VBA Code

Set cnx = New ADODB.Connection
Set rs = New ADODB.Recordset

cnx.CursorLocation = adUseClient
cnx.Open "DSN=ReechRisk"
'cnx.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
RiskFileloc & _
";Extended Properties='text;HDR=YES;FMT=Delimited'"

' Load up sql from file
Open filename.csv For Input As #1
Do Until EOF(1)
strSQL = strSQL & Input(1, #1)
Loop
Close #1

rs.Open strSQL, cnx, adOpenDynamic, adLockBatchOptimistic, adCmdText

If I remove the case statements in the sql and just have a simple select
query then it seems to work. I have other pieces of code which are similar
to this and it works just fine, so I am not sure if the sql used in odbc
Text driver is not compatable..

The error I get when I run this is

Run time error '-2147217900 (80040e14)':
[Microsoft][ODBC Text Driver] Syntax error (missing operator) in query
expression
'Sum(case when scenario_name = 'EB_-0.1' then
scenario_mv-Scenario_stressed_Value else null end)'


"Stephen Howe" wrote:

"KaptainKidd"
<KaptainKidd@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

news:1B708828-31A0-4562-A38E-60BAE5863FAF@xxxxxxxxxxxxxxxx
I am using a
select.. case.. type query on a text file, I am
connecting
via

ADODB.connection but keep getting an error when I try to open the

recordset

any ideas

Post the error your getting and your
Connection/Recordset code

Stephen Howe


You are using Case statements with the Jet provider. Jet does not support
Case (or many other ANSI SQL constructs). You would use "Iif" with Jet, eg.

select
Portfolio,
Sum(Iif(scenario_Name =
'EB_-0.1',scenario_MV-Scenario_Stressed_Value,null)) as 'Equity-10',
....


Iif takes 3 values - the first is the expression to be evaluated, the second
the returned value if the expression is True, the third is the returned
value if False.

Dan



.

Relevant Pages

  • Re: Question for Joe Fallon--determining Data type
    ... then the data type for the column will be Text. ... means 255 characters max (that's a Jet limit). ... where MSDE or SQL Server is the main database (in these cases Jet ... Is 'Spreadsheet' an MS euphemism for 'Excel' or does it support other ...
    (microsoft.public.access.externaldata)
  • Re: Off Topic - Access to Postgres
    ... In both Jet and Oracle back ends, I always write my SQL dynamically anyway and contrary to what I think you implied in your initial response to Hank, I don't think writing Jet SQL (for Jet BE or c/s linked table approaches) nor PTQ SQL in VBA makes an app more difficult to manage or increses development time significantly. ...
    (comp.databases.ms-access)
  • Re: Calculate days between 2 dates ignoring weekends?
    ... Access 2007 Help: SQL reference ... Yes that's SQL-92 syntax but Jet doesn't support it! ... I would like entry level SQL-92 compliance for Jet. ... ANSI-89 Query Mode, making it more suited to newsgroup ...
    (microsoft.public.access.formscoding)
  • Re: GROUP BY vs DISTINCT
    ... In Jet, the second query uses the same plan than MS SQL ... MS SQL Server uses 1 UET! ...
    (microsoft.public.access.queries)
  • Re: Local tables or linked local database?
    ... If you send stupid SQL to SQL Server you're going to get a huge ... dataset, too, so I don't see how the developer is *not* responsible. ... The only difference between Jet and a server is that Jet has to get ... Jet will do an index merge and then retrieve only the ...
    (comp.databases.ms-access)