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.

 

[Sektor]

I think this:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Persist Security Info=False;User Id=User1;Password=Secret;"

 

[RichardMGreen]

Sorry, no joy with that one.
Still giving me the same error.

 

[RoryA]

Is it just a database password (in which case there is no user id required) or a userid/password combination, in which case you have workgroup security applied?

 

[RichardMGreen]

Hi Rorya - when I open the database I get asked for a password, no username. I've done a bit of digging and it looks like it's been set using the Set Database Password option under Tools/Security/Set Database Password (it's Access 2003).

 

[RoryA]

In that case it should be:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & file & ";Jet OLEDB:Database Password=MyDbPassword;"

adjusting password as required.

 

[RichardMGreen]

Just tried that, but it's giving me the same error.
Do I need to put the password in quotes at all?

 

[RoryA]

No, no quotes. What's the exact code you have now? (I've tested the syntax I posted and it works fine for me).

 

[RichardMGreen]

Ignore that, I'd still got extras on the conn.open line (for username and password).
I've just removed those and it works fine.

Thanks again.

 

[RichardMGreen]

Now another little oddity.
I'm using the code in post #1 (with a few tweaks to take account of the database password) to return the recordsets of queries I'm running on data.

All the queries are set up in the same manner but one just seems to force an error when I run it using the Excel code.

If I run the query in the database it returns what I would expect without a problem.

I've checked the query name is typed correctly and it is.

Anyone any ideas?

::edit::
The error I'm getting is "Undefined function 'Monthname' in expression".
Monthname is, however, a perfectly valid function in Access.