• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Access Database -Two Passwords involved

I have databases, that I deal with.  It belongs to a workgroup:UserId:="carol armstrong", Password:=""

And this work:

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:=""

Now it has another password set with Security but everything I added doesn’t work .

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:=""
cn.Open Password:="OPPS"

Any help would greatly be appreciated.

Thank you!






0
ca1358
Asked:
ca1358
  • 3
  • 3
1 Solution
 
Leo EikelmanCommented:
you don't have to specify a user for the second password?

Leo
0
 
Leo EikelmanCommented:
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
0
 
ca1358Author Commented:
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
 
 
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Leo EikelmanCommented:
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
0
 
ca1358Author Commented:
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

0
 
PSSUserCommented:
Try this:

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.Properties("Database Password")="OPPS"
cn.Open UserId:="carol armstrong", Password:=""

Alternatively the new line might need to be:
cn.Properties("Jet OLEDB:Database Password")="OPPS"

If neither of these work you could try putting an ADO data control on a form and using the wizard to build your connection string. You can invoke the wizard by clicking "Custom" (I think this is what it's called) in the object properties window.

When you create the connection the standard window will ask for username and password, which is where you need to enter the System Database username and passwords. There is then a button, labelled either extended or advanced, I can't remember which, which allows you to enter the System database filename and also any database passwords. This will give you an idea of the property name that you need to set. I'm afraid I don't have VB on my home PC and I'm not lilkely to get much chance to have a look at work until the end of the week.
0
 
ca1358Author Commented:
Thank you all for your help!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now