I have an access database that is pasword protected.

I have a client who needs to have data qqueried and then formatted in a particular way, and to that I need to execute multiple queries, all of which are very similar, but differ in the date ranges and amount values being reported upon.

I have this set up and working as a single query running from within Excel, with a controilling macro feeding in the verying parameters as required, as well as driving the navigation on the worksheet so that the data is retirned into the correct location. The query is one that was created using the record macro functionality, so it's a just a basic, vanilla one.

The problem I have is that the database is password protected, and to prevent asking the user to repeatedly enter the password (for each time the query is executed) I have put the password into the query. This of course creates an obvious security hole, and I'm wondering if there is some way that I can make the connection persistent, and reuse the connection for each query, rather than create a new connection each time.

This is the current query ...

CODE

    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Work\Filename.mdb;DefaultDir=C:\Work\;DriverId=25;FIL=MS Ac" _
        ), Array("cess;MaxBufferSize=2048;PageTimeout=5;PWD=password;UID=userName;")), Destination:=Range(cRange))
        .CommandText = Array( _
        "SELECT Count(Transaction.TranDate) AS 'Count', Sum(Transaction.Amount) AS 'AmtPaid'  FROM `C:\Work\Filename`.Transaction Transaction  WHERE (Transaction.TranDate Between {ts '" _
        & cStart & "'} And {ts '" _
        , cEnd & "'}) AND (Transaction.Amount Between " & cLoVal & " And " & cHiVal & ")")
        .Name = "2000-20_2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\user\Application Data\Microsoft\Queries\2000-20.dqy"
        .Refresh BackgroundQuery:=False
    End With



Thanx in advance for all your suggestions.