Unable to refresh QueryTable w/ Access security in place

[supers]

I have a series of Excel workbooks serving as report containers for an Access database. We are in the process of migrating this from one to many users and, as a result, need to add user-level security. While all other functionality seems unaffected by the change, when we try to open the Access queries in Excel using VBA to refresh the QueryTable, the following message appears:

"ODBC Microsoft Access Driver Login Failed"

Even when I try to log into the database after receiving the error message, I get the same error. But, when I refresh the QueryTable manually with the user interface, it seem to work just fine.

 

[supers]

Correction:

I am also unable to manually refresh the QueryTable.

 

[JoanneM]

Just to humour me guys

do you or are you generating your queries to run with owner access option set to yes?

if not try ammending one of your queries to run with owner access set to yes and see if it works.

I may be teaching you to suck eggs if so back to the drawing board guys

Jo

 

[supers]

I am not sure what you mean by setting owner access to yes. Is this a setting in Access, Excel or in the VBA?