Solved

Hide server password

Posted on 2009-04-06
4
290 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:Natchiket
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 17

Author Comment

by:Natchiket
ID: 24077270
I should qualify that, my reference to stored querydefs applies to stored passthrough querydefs
0
 
LVL 17

Author Comment

by:Natchiket
ID: 24077822
OK here's my working so far ... which *seems* to work for sybase with a DSN

Create a temporary querydef and open a recordset to validate the password.  If the password is valid then
instantiate any stored passthrough querydef (a nice simple one to be quick!)
use sendkeys to pass the password and an {enter} keystroke to the keyboard buffer
Open a recordset based on the querydef .... the ODBC password prompt briefly appears and autocompletes
voila ... all passthough querydefs work

a bit clunky ... any better ideas , anyone ?





0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 24108628
What I do is create an ADODB connection to the server, using the password, and then run queries. Each server database type requires a different connection string. The example in the snippet connects to an Access 2007 database but will not work with Access 2007. You will have to test it with Access 2003 as I do not have that version to validate the code.

Public Sub PasswordConnect()
'==========================================================
'  requires a reference to:
'    Microsoft ActiveX Data Objects 2.5(or later) Library
'==========================================================
    Dim cnn As ADODB.Connection
    Dim strDB As String, strDbPassword As String
    strDB = "\\Server\DB\myDatabase.mdb"
    strDbPassword = "myPassword"
    ' use an appropriate connection string
    ' for other connection strings see:
    ' http://www.connectionstrings.com/
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strDB & ";Jet OLEDB:Database Password=" & strDbPassword
    ' open the connection to the server
    cnn.Open
    ' run a query
    DoCmd.OpenQuery "myQuery"
    ' close the server connection
    cnn.Close
    ' clean up
    Set cnn = Nothing
End Sub

Open in new window

0
 
LVL 16

Accepted Solution

by:
Chuck Wood earned 500 total points
ID: 24108645
Oops. The phrase "will not work with Access 2007" should have been "will not work with Access 2000."
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question