Run-time error '-2147467259 (80004005)' / unable to locate problem

Hi,

I have one table and two queries:

Trades: TradeID, StrategyID, AssetTypeID, Identifier, Nominal, Shares, Contracts, Currency, ReferencePortfolioCode, Ticker, RedID, Maturity, AverageCleanPrice, CurrentCleanPrice, AverageClosingPrice, ClosingDate, TargetPrice, Coupon, CouponFreq, StopLoss, Rationale, TimeStamp, Description, TotalPerformance, TotalPerformanceEUR, TickValue, TickSize, UploadOpen, UploadClose, BuySellProtection, MarketValueOpen, MarketValueClose, MarketValueCurrent, Position, ILB_Factor, ILB_Factor_AtOpen, TotalPerformanceCP1, TotalPerformanceEURCP1, MarketValueCP1, CP1Price, SecurityDes, EquityTicker, ABSPoolFactorAtOpen, ABSPoolFactorCurrent, ABSPoolFactorCP1, FxAtBuy, CurrentCleanPriceBid, CurrentCleanPriceAsk, CashMsg, CDS_MV_Open_Clean, CDS_MV_Open_Clean_CP1, CDS_MV_Close_Clean, CDS_MV_Current_Clean, Seniority, IlliquidAssetBidAsk

AssetClass03_NotClosed: AssetType, PortfolioCode, Identifier, TradeDate, Contracts, PMCode, TradeID, StrategyID

AssetClass05_NotClosed: AssetType, PortfolioCode, Identifier, Position, Description, Currency, ReferencePortfolioCode, TradeDate, PMCode, TradeID, StrategyID

I try to read (excel 2007 - vba) the db via these sql codes:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

The first line of code works fine, second line produces an error:

Run-time error '-2147467259 (80004005)':
Method 'Open' of object '_Recordset' failed

I am using ADODB, the error occurs in this line:

Please Login or Register  to view this content.

Why the second command crashes? The commands structures are similar.

Thanks for any help,
Lloyd

Any ideas?

Hi Norie,

I am sorry, sure this makes it easier:

Please Login or Register  to view this content.

Lloyd

Yes, by reading the db from excel, only the 2nd query crashes.

By executing both sql-statements in access there is no problem.

Any ideas?

Hi Norie,

I'm not sure that you mean, this is the code around the function call:

Please Login or Register  to view this content.

Lloyd

Hi Norie,

yes I did. It works fine!

Hi Norie,

no, you got me wrong!

I copied the conents of sql from

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

into access and excuted it maually. this works fine. By calling the statement from excel, the query still crashes.

Llloyd

My general rule of thumb when debugging sql from Excel is start with the dates. It might not be the culprit in this case, but it's always an easy thing to rule out and it can cause to iffy errors, be explicit in passing your dates - I don't know what your date function does, but it looks dodgy -either way, pass your dates as strings and format them as yyyy-mm-dd, so

Please Login or Register  to view this content.

Hi Kyle,

just for testing I have removed the restriction with the date:

Please Login or Register  to view this content.

The error still occurs!

redDate just lags the actual date:

Please Login or Register  to view this content.

Lloyd

Try changing:

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

It depends on the version of Jet you are using and whether it's being run in ANSI or non-ANSI mode - must admit I had to look this one up - I didn't know it; but I tend to play safe with my field names and always have a 3 letter prefix anyway that relates to the table when naming them, prevents issues like these and make it easy to identify tables/fields in complex queries.

I never use access these days, strictly sql server

P.s I found the list here http://support.microsoft.com/kb/248738/EN-US

Yep, that's it.

Norie, Kyle, thank you very, very much!!!