Due to a recent 'crackdown' in IT security all Access databases which connect to company servers must not have server passwords embedded in the connection string of attached tabledefs and stored querydefs. With regard to tabledefs this is straightforward as the tables can simply be re-attached without dbAttachSavePWD as part of the attributes property.
However, since querydefs don't have an attributes property this appears to be more problematic. For stored querydefs it seems that the only solution is to set the connection string to the DSN (or at least leave out the password) and then open one querydef
to force the default Login prompt to appear. Once the user has successfully applied the password Access caches it and then subsequent querydefs opened on the same DSN automatically have the password applied.
This raises a problem of automation control, because it would be nice to capture the password in an Access form and then supply it to the dialogue box automatically, however this raises the prospect of the dreaded sendkeys and what to do if the user supplies the wrong password. Can anyone suggest either a neat solution or an unequivecal statement that only temporary querydefs can be used in this situation.