VBA, ODBC Authentication, and Passthroughs

Anonymous
2011-02-23T16:19:35+00:00

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

Microsoft 365 and Office | Access | For home | Windows

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

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-02-23T21:03:00+00:00

    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/

    Was this answer helpful?

    0 comments
  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

  3. 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