Access Stored Procedure Automation Error

[duftnich03]

Hi All

I have a stored query procedure that executes fine in MS Access, but when I use the ADO library to execute said stored query procedure, and return the results to Excel, I receive an 'Automation error unspecified error -2147467259 (800004005)'.

It appears to be when it tries to open the recordset (rst. open Source:=...etc), I've included both the VBA that calls the open procedure and the open recordset procedure code itself.

Any help would be greatly appreciated.

Cheers.

Code:

Please Login or Register  to view this content.

Open recordset code:

Please Login or Register  to view this content.

[Leith Ross]

Hello duftnich03,

Unlike Access, Excel is not a RDB (Relational Database). Reading records in Excel can only be done sequentially. Your record set CursorType can only be 0 (adOpenForwardOnly) or 3 (adOpenStatic). Records in Excel are read only so the LockType must be 1 (adLockReadOnly). I have never used a stored procedure command in excel before. You may need to assign this to a string variable in your code and set the Options to 1 (adCmdText) instead of 4 (adCmdStoredProc).

Please Login or Register  to view this content.

[duftnich03]

Thanks for the info, but I use this approach in all of my Excel documents that refer to Access DBs and I have never had this issue before. As far as I understand it that LockType specifies what the Locktype in the DB is, not excel, as the code is passed to the Access DB engine.
I have other queries that are run in the same way, in the same document, that use the same 'open_record_set' procedure as above with great success. I also have some that write back in other multi user environments and they don't work unless adLockOptimistic (3) is selected.

I tried changing it to 1 at any rate, just confirm or deny my theory, and I does not work sadly.

[duftnich03]

Right, I've solved it!

The issue was in my SQL String:

Please Login or Register  to view this content.

The above runs fine in Access, but it doesn't when using the ADO libraries in VBA Excel.
However the below does:

Please Login or Register  to view this content.

As you can see the only difference is I've put [] square brackets around my 'Size' field. So this would suggest that 'Size' is a reserved word in the ADO libraries and must be wrapped in [] square brackets when refering to a field name, yet it is not a reserved word in Access and therefore doesn't require the square brackets. This is why it worked in Access, but not in Excel.

Thanks for the help