Access Database -Two Passwords involved

you don't have to specify a user for the second password?

Leo

I also wonder if it would make any difference if you added the username and password to the connection string.  Opened it.  then changed the connection string to include the second username/password and try opening it again.

Leo

I tried vaiours combination, still nothing.

''''''''''''''''''''''''''''''''''''
 ' connect to the Access database
   Set cn = New ADODB.Connection
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"
cn.Properties("Jet OLEDB:System database") = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\Users\NEW082804.MDW"
cn.Open UserId:="carol armstrong", Password:=""

''''''''''''''''''''''''''''''''''''''''
' connect to the Access database
    Set cn = New ADODB.Connection
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"
cn.Properties("Jet OLEDB:System database") = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\Users\NEW082804.MDW"
cn.Open UserId:="", Password:="OPPS"

Any other suggestions?

Thank you for your help
 
 

In the code above, is that the actual code in your app?

because you should only create a new ADODB.Connection once.

>> Set cn = New ADODB.Connection


Leo

Here is the whole thing, still no luck.  It stops at

cn.Open UserId:="carol armstrong", Password:=""

error

"Not a valid password"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Option Explicit
Private rsCombined_ATE_Mark_to_Market_Client_approval_date As ADODB.Recordset

'---------------------------------------------------------------------
Private Sub Workbook_Open()
'---------------------------------------------------------------------
Dim cell As Range
Dim LastRow As Range

   
    pzLoadList2Lists
    pzLoadList3Lists
       
    Application.DisplayAlerts = False
   
    kList1 = data.Range("A1").Value
    klist2 = data.Range("A2").Value
    klist3 = data.Range("H2").Value
   
    'this poulates the Data Validation lists
    Set cell = dv.Range(kList1)
    fzCreateValidationList1 cell
    fzCreateValidationList2 cell.Offset(1, 0), 1, cell
    fzCreateValidationList3 cell.Offset(2, 9), 1, cell
   
    'this populates the combo boxes
    fzPopulatList1
    fzPopulatList2 1
    fzPopulatList3 1
   
    Application.DisplayAlerts = True

'cboPrimary = ""
'cboSecondary = ""
'cboThird = ""



    Sheets("TempTable").Select
    Range("A1:AD57").Select
    Selection.ClearContents
   
   

    Sheets("Pricing Tool").Select
    Range("A22:C22").Select
    Selection.ClearContents
   
    Sheets("Pricing Tool").Select
    Range("A24").Select
    Selection.ClearContents
   
    Sheets("Pricing Tool").Select
    Range("d24").Select
    Selection.ClearContents

Sheets("Clients").Select


   
   
    ADOFromExcelToAccess
   
   

Sheets("Pricing Tool").Select

End Sub
Private Sub Workbook_Activate()
TradeLimit.Show vbModeless
End Sub

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim i As Integer
Dim DBPath As String
Dim dbVolume As DAO.Database

' connect to the Access database
    Set cn = New ADODB.Connection
   
 ''''''''''''''''''''''''''''''''''''
 ' connect to the Access database
 'Workgroup Security
 
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"
cn.Properties("Jet OLEDB:System database") = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\Users\NEW082804.MDW"
cn.Open UserId:="carol armstrong", Password:=""

''''''''''''''''''''''''''''''''''''''''
'Password Security
   
cn.Provider = "Microsoft Jet 4.0 OLE DB Provider"
cn.ConnectionString = "Data Source=\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume.mdb"
cn.Properties("Jet OLEDB:System database") = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Jennifer\Users\NEW082804.MDW"
cn.Open UserId:="", Password:="OPPS"

 

'''''''''''''''''''''''''''''
Set rsCombined_ATE_Mark_to_Market_Client_approval_date = New ADODB.Recordset
    i = 2
    With rsCombined_ATE_Mark_to_Market_Client_approval_date
        .Open "[Combined_ATE_Mark_to_Market_Client_approval_date]", cn, adOpenDynamic, adLockOptimistic, adCmdTable
        If Not .BOF Then .MoveFirst
        Do While Not .EOF
            Cells(i, 1).Value = .Fields(1)  'CompanyNumber in First column
            Cells(i, 2).Value = .Fields(0)  'CompanyName in 0 column
            Cells(i, 3).Value = .Fields(12)  'TradeLimit Available
            .MoveNext
            i = i + 1
        Loop
    End With

 DBPath = "\\Dtcnas-ilsp002\mandatory\Analysts - Working Files\Carol\Demo\volume"
    Set dbVolume = OpenDatabase(DBPath)

rsCombined_ATE_Mark_to_Market_Client_approval_date.Close
   dbVolume.Close
    Set rsCombined_ATE_Mark_to_Market_Client_approval_date = Nothing
   Set dbVolume = Nothing

Dim LastRow As Object

Set LastRow = Worksheets("clients").Range("A6000").End(xlUp)


 LastRow = Range("A6000").End(xlUp).Row
   
    ActiveWorkbook.Names.Add Name:="Clients", RefersTo:="=Clients!$A$2:$A$" & LastRow
End Sub

Thank you all for your help!