Opening a database with a password

[RichardMGreen]

Hi all

I've got the following code which quite happily opens a database and returns query results to the specified sheet and range.

    Dim conn As ADODB.Connection, rs As ADODB.Recordset, cmd As ADODB.Command
    Dim wsData As Worksheet, wsDst As Worksheet, ws As Worksheet, wb As Workbook
    Dim rngData As Range, rngDst As Range, qt As QueryTable
    Dim strQry As String, strSQL As String, file As String, strConn As String
'----- Check if data retrieval should be bypassed - if so, go straight to file creation -----
    Set wsData = Worksheets("Query_List")
    file = wsData.Range("H2")
'----- Clear out old data and set up userform -----
    maxquery = Sheets("Query_List").Range("A65535").End(xlUp).Row - 1
    currentquery = 0
    UserForm1.Show
    UserForm1.Label1.Caption = "Refreshing Database Queries..."
    UserForm1.ProgressBar1.Value = 0
    UserForm1.Repaint
'----- Turn off calculations -----
    Application.Calculation = xlCalculationManual
'----- Get all data -----
    Set conn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Persist Security Info=False;"
    conn.ConnectionString = strConn
    conn.Open
    Set rngData = wsData.Range("A2")
    Set cmd = New ADODB.Command
    On Error GoTo error_line
    While rngData.Value <> ""
        UserForm1.Label1.Caption = "Refreshing " & rngData.Value
        UserForm1.Repaint
        strQry = "[" & rngData.Value & "]"
        strSQL = "SELECT * FROM " & strQry
        offsetrow = 1
        cmd.CommandType = adCmdText
        cmd.CommandText = strSQL
        cmd.ActiveConnection = conn
'----- Pick up information on where data is to go -----
        Set wsDst = Worksheets(rngData.Offset(, 1).Value)
        Set rngDst = wsDst.Range(rngData.Offset(, 2).Value)
'----- Retrieve data from database and insert into correct cells -----
        Set rs = cmd.Execute
        If Not IsNull(rngDst) Then rngDst.CopyFromRecordset rs
        currentquery = currentquery + 1
'----- Make sure no data left to write and set up details for next query -----
error_line:
        Err.Clear
        On Error GoTo error_line
        Set rs = Nothing
        Set rngData = rngData.Offset(offsetrow)
        UserForm1.ProgressBar1.Value = (currentquery / (maxquery + 1)) * 100
        UserForm1.Repaint
    Wend
    Set conn = Nothing

Now, the database I need to use now has to have a password to open it, so I've been trying to update the code to accept a username and password but with no luck.

What I've tried so far:-
Changed Persistant Security Info to true
Added username and password to the Conn.Open line.

I get an error message which says:-
"Cannot start your application. The workgroup information file is missing or opened exlusively by another user"

I know no-one else is in the database and I'm not entirely sure what a workgroup information file is.

Any help greatly appreciated.

 

[RoryA]

You can't use access functions like that or Nz in queries accessed via ADO

 

[RichardMGreen]

Thanks for that, bit of a re-write of my queries then.

 

[RichardMGreen]

OK, now for another little issue.

Because of what the database contains, I need to encrypt it. That gives me 3 questions:-
1) What's the best way of encrypting the database to keep the information at least reasonably secure?
2) Will my code above still work with an excrypted database?
3) If it won't, how would I modify it so that it does?

 

[RoryA]

I honestly don't know, but I would have assumed that the database password would involve encryption of some sort?

 

[RichardMGreen]

I don't know.
There is an option within Tools/Security to "Encode/Decode Database" but I'm not sure what that does. The help file mentions Workgroup Administrator but doesn't seem to go into any real detail.

 

[RoryA]

Sounds like a question for the Access bods to me.

 

[RichardMGreen]

No problem, I'll post this over on the other board.