VBA, ODBC Authentication, and Passthroughs

Question

VBA, ODBC Authentication, and Passthroughs

Anonymous

My company is in the process of meeting certain guidelines to make steps toward going public. One of those steps involves analysts to have to change their ODBC passwords every 30 days. Not awful for adhoc type deals, but I have a reporting box that kicks off 20 to 30 access databases a day. Not convenient for me by any stretch.

My question/theory is:

If I created one Excel spreadsheet with Username/Password fields, linked it in my existing Access db's, can I use those Excel Username/Password fields as variables in VBA to pass through for ODBC authentication? This would mean I would only have to update the Excel doc once vs every passthrough query in every db. Can this VBA process save me a step each time I create a passthrough query from having to manually configure the ODBC info in the properties section of the query? If this process would function, does anyone have any guideance that they could provide in the syntax?

If I'm not making sense, let me know and I'll try to reiterate differently.

Thanks,

Chris

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Answer 1

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 3

Chris Unger wrote in

news:*** Email address is removed for privacy ***

m:

If I created one Excel spreadsheet with Username/Password fields, linked it¶ÿin my existing Access db's, can I use those Excel Username/Password fields as variables in VBA to pass through for

ODBC authentication?¶ÿ This would mean I would only have to update

the Excel doc once vs every passthrough query in every db.¶ÿ Can

this VBA process save me a step each time I create a passthrough

query from having to manually configure¶ÿthe ODBC info in the

properties section of the query?¶ÿ If this process would function,

does anyone have any guideance that they could provide in the

syntax?

Theoretically, it sounds like you should be able to do what you

suggest. I don't have any real suggestions on coding it up, but I

can't see why it wouldn't work.

However, I would expect that it would actually violate the rules on

passwords. That is, it sounds like circumvention of the security

rules.

Is there any way to unify the usernames your apps need to run as?

Or, at least, to reduce it to a mere handful?

David W. Fenton http://www.dfenton.com/ contact via website only http://www.dfenton.com/DFA/