Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    linking to secured Access DBs (2000)

    I have a couple of DBs secured with their own workgroup files and I'd like to link to a couple of tables in another secured DB. Linked table manager can't handle it so can I do it with code? Or am I going to have to create recordsets using ADO etc?

    TIA
    Mark

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

    Re: linking to secured Access DBs (2000)

    See for example the Access Security FAQ, downloadable from ACC: Microsoft Access Security FAQ Available in Download Center.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking to secured Access DBs (2000)

    I don't understand why you can't link to another database using Linked Table Manager - we do it routinely. The trick is that you have to use the same .MDW file for all databases.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    Hans, thanks. I'll take a look at the FAQ when I get a few minutes.

    Wendell: I agree using the same mdw would remove the problem but it's just not feasible. Joe Bloggs may have full permissions in one of our DBs but read only in another. There's probably some way of coding around that but its way to much effort. LTM definitely doesn't work when using different mdw files

    Mark

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

    Re: linking to secured Access DBs (2000)

    Using the same .mdw file, Joe Bloggs can have different permissions in different databases. The .mdw file stores the names of users and groups, and the composition of the groups. Each database stores the permissions assigned to users and groups for the various database objects.

  6. #6
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    <hr>Each database stores the permissions assigned to users and groups for the various database objects<hr>
    I didn't know that! It never occured to me to think in those terms but now that I do it's fairly obvious.

    On a side issue if I try and 'unify' my security files how easy is it to change mdw files for a given DB?

    Many thanks
    Mark

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

    Re: linking to secured Access DBs (2000)

    I've never done that, so I'm not sure. I suspect that you will have to remove security while using the original .mdw (grant full permissions to the Users group and the Admin user account, put the Admin user back in the Admins group and remove the password from the Admin user), then switch to the common .mdw and redo security. But perhaps someone with more experience in this area has a better idea.

  8. #8
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    I've gone throught the FAQ and managed to open a db secured with a different MDW. I can make a recordset and attach to a aform no problem bu twhat I would like to do is use a qry out of the newly opened DB as a rowsource in a listbox. I think I have to link the source table from the secured DB but I'm struggling to find out how. From the FAQ it looks like I need to create a tabledef but I can't find the syntax anywhere for the .connect part that includes the necessary for the MDW.

    Can anyone point me in the right direction?

    TIA
    Mark

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking to secured Access DBs (2000)

    Hans is correct - and that's a point that many people miss when they begin working with Access User Security. The permissions for objects are stored in the database that contains the objects, not in the MDW file. Now to your problem:

    You cannot make a link to a query - only tables can be linked to (at least as long as your are linking to Access tables - SQL or ADP views are another story). So if you are linking dynamically a table and want to create a query based on that table, then you need to create a querydef, not a tabledef. But as a matter of principal, you can create the link to the table manually, create the query, and make it the data source for your form, then delete the link. Then when you wish to open the form, you can create the link, open the form, and then destroy the link when the form is closed.

    Just out of curiosity, why can't the link be a permanent one???
    Wendell

  10. #10
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    <hr>Just out of curiosity, why can't the link be a permanent one???<hr>

    How can I do this? LTM won't let me link to the table because it's associated with a different MDW. Or am I missing something fundamental?

    Mark

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: linking to secured Access DBs (2000)

    Sorry, I presumed you had decided to take Hans' advice and set up a single MDW. Unless you do that you won't be able to work with the data in any event (or else you database is not properly secured). Access security is effective at the object level whether you use linked tables or local tables, and opening a recordset in code will fail if you don't have permission to view the table. The only objection to using a single MDW file is that the administration task can get to be a bit overwhelming if you have hundreds of users. But in general we nearly always use a single MDW for a given client. Just the emphasize Hans' point, there is no connection between MDWs and databases. The MDW is simply a repository for user, group, and password storage, and all permissions are stored in the database for the UserIDs that it knows about. As long as you create the same UserIDs in both MDWs you can use different MDW fiiles. But under normal condidtions, in a single Access session you can only be connected to one MDW file.
    Wendell

  12. #12
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    Thanks, Wendell.

    I'll look more closely at how to change security files for my DBs.

    Mark

  13. #13
    Star Lounger
    Join Date
    Mar 2003
    Location
    Ilkeston, Derbyshire
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: linking to secured Access DBs (2000)

    I'm going around in circles trying to find out how to change security files for a given DB and I'm running out of time due to fast approaching holidays
    Can someone tell me how to...
    1 remove database security from a DB secured with an mdw
    2 apply a different mdw

    TIA
    Mark

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

    Re: linking to secured Access DBs (2000)

    About (1): see my reply <post#=386684>post 386684</post#> or the Security FAQ I referred to in my first reply. It contains a paragraph about removing security.

    About (2): join the other .mdw, or (preferably) open the database through a shortcut that uses the .mdw. The target of such a shortcut looks like this:

    "C:Program FilesMicrosoft OfficeOfficeMSAccess.exe" "C:AccessMyDatabase.mdb" /wrkgrp "C:AccessMySecureWorkgroup.mdw"

    with the appropriate paths and file names substituted.

Posting Permissions

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