unable to run stored procedure using data from linked server via VBA

[QueenKirsty]

Setup:
1. I have a SQL Server 2008 db (called VantageTrans) with a linked server (a Progress db called VANTAGE - the back end of our financial system).
2. I have a stored procedure (spUpdateTblForFile) on VantageTrans (the SQL Server db) that pulls data out of the tables in VANTAGE (the linked server) and dumps the manipulated data into tables in VantageTrans.

Issue:
The stored procedure runs fine on when run directly on the server db (VantageTrans) but will not run when called from VBA. I have tried calling it from Access (.accdb and .adp) and from Excel using the ADODB.Connection / ADODB.Command functions but it just hangs. Initially it produced a timeout error but I set the connection string and command timeouts to 0 and that removed the error.

I can run stored procedures successfully from VBA that only use the VantageTrans data tables so I am confident that the VBA code is correct.

PLEASE can anyone suggest what the issue with the linked server might be? I wondered if it might be a permissions thing when trying to connect to the linked server but I don't know how I can check his out.

All help would be gratefully appreciated! :-)

[ck9663]

It's this little thing called DELEGATION. Here, read this...

Good Luck!!!

~~ CK

[QueenKirsty]

You're a total star! I will try that as soon as I get in tomorrow!

Thank you :o)

[NeoPa]

Don't forget to set the Best Answer when you find it's all you need Kirsty ;)