We recently converted one of our datawarehouses to ORACLE.

I am now having difficulty converting several Excel Macros that use VBA
generated SQL statements to execute. e.g.:

Sub gogetem()
'Fields appropriately dimmed
'Loads Warehouse Data to the DownLoad sheet

curwrkbk = Format(Sheets("MISC").Cells(18, 2), "mmddyy") & "_this_File.xls"
curwrkbk = "\\crpAtlFnp03\Accounting\" & curwrkbk

K_List = contract_list 'Function builds list of contracts to have data
returned

'Retrieve data from data warehouse
'Get the detail data
'Initialize period date variables
'User selects report "AS of Date" from drop down and selection stored in:

pdate = Sheets("Misc").Cells(17, 4).Value


Sql_Str = "SELECT LEASE_TYPE, CONTRACT_NBR, LL_NET_CBR,
LL_REMAINING_PRETAX_INC, " & _
"RESIDUAL_NET, CONTRACT_BALANCE, UNEARNED_FINANCE, UNEARNED_RESIDUAL
" & _
"FROM IL_REPORT_CONTRACTS_FINAL_CURRENT " & _
"WHERE (PROPERTY_DATE= to_date('" & pdate & "', 'yyyy-mm-dd'))AND "
& _
"(CONTRACT_NBR IN (" & K_List & "))" & _
"ORDER BY CONTRACT_NBR;"

Sheets("Download").Select
Range("A2").Select
With Selection.QueryTable
.Connection = _
"ODBC;DRIVER={Microsoft ODBC for Oracle};UID=" & IAM & ";PWD=" &
MyPWD & ";SERVER=CDMP.com;"
.Sql = Sql_Str

.Refresh BackgroundQuery:=False

End With

ActiveWorkbook.SaveAs Filename:=curwrkbk

End Sub



The debugger kicks in at the " .Refresh BackgroundQuery:=False"

With a SQL Syntax error.

pdate format must be "yyyy-mm-dd", and my understanding is that the TO_DATE
function is required to override ORACLE default format of :date Timestamp"

K_List format is:
('123-0000007-000','123-0000008-001',...)

I run the query in ACCESS without difficulty when K_List is in a separate
table.
Access bombs with "Exceeding 1,024 character limit for query grid" error
when I keep IN(list) format in grid layout

When I try to run as an SQL Pass-through I get an ODBC error indicating
"this operator must be followed by Any or ALL", but nothing to indicate what
"this operator" may be.

Any help would be greatly appreciated as we are soon to convert all out data
warehouses to ORACLE and I have tons of similar queries that will need to be
revised as well...

TIA

BAC