Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2003
    Posts
    167

    Question Unanswered: read-only linked Access table?

    I have an Access200 front-end I'm placing on a shared network drive for a coworker to use to query against another Access db. The query requires the linking of an Access source table that I do NOT want altered. I've done searches on the web and this group and cannot find a way to set a linked Access tables as read-only.

    Figuring that an ODBC connection would allow a read-only link, I tried that, but apparently you cannot link 2 JET db's with ODBC.

    Has anyone run into this problem trying to share data from Access while preserving its integrity?

    Thanks.

  2. #2
    Join Date
    Apr 2004
    Posts
    8

    Re: read-only linked Access table?

    1) Make sure that you have a primary key field in the table you wish to link. If not, add one.

    2) in the source database, create a query based on the required table. Select all of the fields you wish to be visible to the user, including the primary key field (this is essential)

    3) Group By all fields.

    4) Save the Query.

    5) In your client database (used by the user) create another query...

    Something like this....

    SELECT *
    FROM qryData IN 'S:\Backend.mdb';

    Where qryData is the name of the query created in the source database
    and 'S:\Backend.mdb' is the path to the database.

    When you open the local query in the client, you should see all records but not be able to edit them.

    Protect the source SQL with a function to create the query temporarily and delete it afterwards. If the database is made into an MDE it should be safe.

  3. #3
    Join Date
    Nov 2003
    Posts
    167
    Thanks, Frustrated.

    This works great when I test it - the question is: will the SQL reference to the backend's network location work for a coworker without access to that network location?

    For instance, will a user be able to execute:

    SELECT BackendQuery.INDEX, BackendQuery.TestData
    FROM BackendQuery IN '\\Testpath\Backend.mdb'
    GROUP BY BackendQuery.INDEX,BackendQuery.TestData;

    ..if the user does not have read access to '\\Testpath\Backend.mdb'?


    Thanks for your help!

  4. #4
    Join Date
    Apr 2004
    Posts
    8
    hmmm - I think they would have to be able to access the location where the back-end database was located.

    would it not be possible to set the user up in such a way that they had access to the location, but without a physically mapped drive that would give them obvious access from their desktop?

    Is this an issue or are you considering this as a means of stopping them from accessing the back-end database outside of the client application?

  5. #5
    Join Date
    Nov 2003
    Posts
    167
    The coworker only needs a noncritical, but fluid index from a table/db that contains confidential data. None of the index numbers can be changed, and none of the other data should be viewed.

    My first (silly) thought is to place a separate db with a linked table to the needed index/subset - but this runs into exactly the same problem. Once the link is utilized, it still has to reference the restricted path.

    I'm hoping to find a solution outside of Access Security, as my experience with Access security has been unpleasant. One major gripe with it is that a user granted read-only access to a table under access security needs write access to the network path containing the db (because Access has to write the ldb file); so while the user can't edit the table, theres nothing stopping them from deleting/renaming the entire db through explorer. I've never been able to figure out why Access does not let the db administrator specify where the ldb file is written, thereby allowing true read-only rights to the db. Maybe this has been fixed in later versions.

    Anyway, enough griping.

    Your solution using SQL rules to restrict access is great. I'll see if I can find a workaround to apply it to sensitive data.

    Thanks!

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmmm, I have been trying different workarounds for the exact same reason: if a user has the full install of Access and knows a few tips and tricks, all they need is the path to the 'restricted' DB (which is fairly easy to find) to gain full access to the tables, regardless of the security setup.

    The ldb thing is a problem - as it requires the users to have read/write.

    I don't know your level of coding skills, but here's a method I have been exploring:

    I wrote a little VB executable to be distributed. The VB program connects to the back-end and verifies the user's rights. Then, it launches Access and opens the front-end with the "/Runtime" command line option - this FORCES the DB to be opened in "Application" mode. In this mode, the user can only use the tools you have given them (look into "Startup Options"). This prevents the user from opening the file in "Bypass" mode and keeps the actual location a secret.

    Since the VB executable is compiled into an EXE, the user is unable to "peek under the hood" and get the path to the back-end.

    Hope this helps, have fun!

  7. #7
    Join Date
    Nov 2003
    Posts
    167
    Interesting...

    How do you keep the user from directly opening the front-end (ie - skipping the vb app)?

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    They don't know where it is and it is disguised.

    The network where my application is being used is big enough that somebody would have to look pretty hard to find it.

    In addition, the filename does not have to be .MDB or .MDE - using the command line to open the file allows using any name and extension you want.

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Re: read-only linked Access table?

    Originally posted by onansalad
    I have an Access200 front-end I'm placing on a shared network drive for a coworker to use to query against another Access db. The query requires the linking of an Access source table that I do NOT want altered. I've done searches on the web and this group and cannot find a way to set a linked Access tables as read-only.

    Figuring that an ODBC connection would allow a read-only link, I tried that, but apparently you cannot link 2 JET db's with ODBC.

    Has anyone run into this problem trying to share data from Access while preserving its integrity?

    Thanks.
    Why not just creat some ASP pages for the User to veiw in Explorer

    then on software need on User PC
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    good idea!

    if they're only lookng, and not changing, don'te even use Access.

    Other options include:

    Use VB to present the data (VB 6.0 and newer has some pretty handy data objects, like the "flexgrid"

    Use Excel - Excel is remarkably easy to program in, and you can password protect your code to keep the source DB hidden

    Use just about any Office application, for that matter. Code in Access is portable, just add the reference and precede all Access commands with the ojbect variable you assigned to Access.

  11. #11
    Join Date
    Apr 2004
    Posts
    8
    A lot of these ideas sound okay, but if we are still talking about the data being held in tables in an Access database, why not stick with a pure Access application and secure it. Properly secured and in an MDE format, it is impossible to use the bypass key at all. In MDE format, the code modules cannot be viewed either.

    You code take the remote query idea a stage further and use the SQL expression as a recordset source, opening the recordset in code populating your view programmatically.

    Using these ideas together make it almost impossible (I am sure there are ways I don't know about) for someone to identify the path to the true datasource from the database.

    Using a password on the source database will reduce the risk further. you can still rename the source database extension to something obscure and the above techniques will still work.

    If you would like sample code for completely locking up the front end from would-be prying eyes, let me know.

  12. #12
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    yes, I would like to see it.

    I have been trying to come up with creative ways to protect unauthorized access.

    By the way,on your comment about MDE files:

    If I have a full install of Access and I can get to the actual MDE file, then I can open it and bypass the startup routine. I will not be able to change any of the objects (queries, forms, etc.) but I will be able to open the tables and change the data. This is a huge gaping hole in MDE's security.

  13. #13
    Join Date
    Nov 2003
    Posts
    167

    Lightbulb

    Actually,

    TCACE's idea (vb app calling a front-end calling a backend) could work very well if you design the front-end as a secured Access db. The username password is compiled into the vb-app. Apart from accidentally deleting the front-end db (or decompiling the vb app), the data is safe. If the front-end is deleted, restore it from the last backup.

    My case has an additional twist in that my user should not even view other data in the table, but she will need to generate/retrieve a new primary key value from my backend. Since generation of a new key requires a new record (write access), I'm having to split the process onto a custom front-end for my user in a location she has read/write access. I will then manually import data associated with that key entered by the coworker into the backend at a regular interval.

    In effect, my table's primary key will have a 1-to-1 relationship with the coworker's front-end table. This creates an "island of information" in that the same key is being tracked in 2 locations - but it's the only way I could think of to preserve confidentiality. Anomalies could pop up - especially if the coworker changes assocated attributes after I import them into the backend - but there are ways to avoid that.

    Thanks again for all your help!

  14. #14
    Join Date
    Apr 2004
    Posts
    8
    See enclosed document.

    Hope this is of some help to someone out there :-)

    Let me know what you think,

    regards

  15. #15
    Join Date
    Apr 2004
    Posts
    8
    hmm - attachment didnt show up...let me try again...
    Attached Files Attached Files

Posting Permissions

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