Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access data retreival (2003)

    I have developed a system whereby users can retrieve monthly accounts information that is held on a server

    The data is all held in Access, however the accounts workstream selection functionality, and the formating and display of the data is all carried out in Excel. As stated above I wish to now release this functionality onto a server to allow many users to access their data each month.

    I have password protected the excel VBA modules and written all the data extraction functionality. Can somebody please advise how I can protect the Access file so that the permitted data extracts can only by retrieved and viewed via the Excel front end.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access data retreival (2003)

    You can protect the Access database with a password (Tools | Protection | Set Database Password...). You must then provide this password in the code you wrote in Excel.

    For more robust protection, you can apply user-level security. See the following links, they provide useful information about user-level security:
    <!profile=WendellB>WendellB<!/profile>'s tutorial with a list of links: The Secrets of Security
    Security paper by Jack MacDonald (PDF format)
    Access Security FAQ (Word document for download from Microsoft)

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access data retreival (2003)

    Thanks

    Where do you include the password when opening the database from Excel? Please see the function extract below

    Function Access03InsertWIPB310DataIntoTable()
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim i As Integer
    Dim SlashFinder As Integer

    Set dbs = OpenDatabase(RootDirectory & "ManAccts" & ModThisFileCurrentPeriod.ThisFileCurrentPeriodStri ng & ".mdb")
    strSQL = "Delete * FROM WIPB310Data"
    dbs.Execute strSQL, dbFailOnError
    i = 1
    While CollectedWIPB310Data(i).Z00CodeA <> ""
    strSQL = "INSERT INTO WIPB310Data....
    i = i+1
    Wend
    dbs.Close
    Set dbs = Nothing
    MsgBox ("I'm done uploading the WIPB310")
    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access data retreival (2003)

    Like this:
    <code>
    Set dbs = OpenDatabase(RootDirectory & "ManAccts" & _
    ModThisFileCurrentPeriod.ThisFileCurrentPeriodStri ng & ".mdb", _
    False, False, ";pwd=Secret")
    </code>
    Replace Secret with your password.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access data retreival (2003)

    Thanks

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access data retreival (2003)

    Hans

    As an initial approach I wish to set a password in the manner that you have described above.

    However I am concerned as to how best to deal with any errors that may occur, which it would seem may come in 2 types:

    First: how to deal with the possibility that excel cannot find the database

    Second: how to deal with the possibility that the SQL data retrieval query fails and specially how to ensure that the database connection is closed upon failure

    Can you please let me have your advice.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access data retreival (2003)

    Both problems can be solved by adding a standard error handler to the code:

    Function Access03InsertWIPB310DataIntoTable()
    Dim dbs As DAO.Database
    Dim strSQL As String
    Dim i As Integer
    Dim SlashFinder As Integer

    On Error GoTo ErrHandler

    Set dbs = OpenDatabase(RootDirectory & "ManAccts" & _
    ModThisFileCurrentPeriod.ThisFileCurrentPeriodStri ng & ".mdb", _
    False, False, ";pwd=Secret")
    strSQL = "Delete * FROM WIPB310Data"
    dbs.Execute strSQL, dbFailOnError
    i = 1
    Do While CollectedWIPB310Data(i).Z00CodeA <> ""
    strSQL = "INSERT INTO WIPB310Data...."
    ...
    i = i + 1
    Loop
    MsgBox "I'm done uploading the WIPB310", vbInformation

    ExitHandler:
    On Error Resume Next
    dbs.Close
    Set dbs = Nothing
    Exit Function

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •