Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Unanswered: Create Table or Use Recordset Only

    I'm kinda thinking out loud on this but I'm hoping some of ya'lls wisdom and experience will rub off on me.

    I have an Access App that runs of the intrinsic Jet DB that you all love. In an effort to make it easier to use for some of my coworkers and to also create a much needed degree of separation between the users and the data, I am building a disconnected front end. This front end will limit the view for the users to their issues and will allow then to run a couple reports for themselves. It will also eliminate some issues I have had with people accessing the data directly.
    For my own reasons, I have opted to make life hard on myself and not use the standard Linked Tables.

    The way I see it, I have two approaches I could take with actually loading the records I want them to have.
    A) On opening the application, I build tables made of the specific records I want a given user to have access too. At this time, only one user can be assigned to each record. It is worth noting that this could change in the future and that issue would have to be contended with then. The method would require a pause at load time but I think any other processes would run more smoothly. It would allow for an easy rollback on crash because I would copy the new or updated records down at quit. That does mean that if you logged in and did 100 things and it crashed, you could lose it all. Ideally, I would build it so that if the original data couldn't be found at startup, you could access you previously loaded records and it would wait for the orignal data to become available again before loading modified records. This approach may help with the "on crash" scenario previously described.
    Probably the worst part of this approach is that you have multiple sets of data, potentially. However, it would be built entirely with that in mind. It would require that I develop a robust compare and update setup.

    B) On opening the application, a recordset is created. With each change of a record or addition of a record, the recordset is refreshed. This means that I have to access the original data more frequently but there aren't a lot of people using this thing (20ish) so it shouldn't be a problem, rarely would any two people be accessing at exactly the same time. In the "on crash" scenario described above, a person SHOULD only lose one record's worth of effort. I would assume all processes would slow down as a result of frequent updates to the original data.

    Please feel free to share your thoughts. If I'm missing something or am just plain loopy for even considering one of these, let me know. I can take it.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Might be a silly question but...
    Why not use Access built in security?

    Or perhaps views are what you might be needing?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    Never a silly question.

    I can use Accesses built in security to solve the, "DON"T TOUCH MY FREAKING DATA!!" riddle :-) Although I have had some issues in my setup of security groups. Learning point for me.
    It doesn't solve a couple other issues we have had, namely that people keep losing or screwing up their shortcuts. I had built a disconnected interface using linked tables and it works so there is no urgency or anything. That also keeps most of the folks out of the original data.
    I just wanted to take it a step further and treat it like a fa' reelz (for real) database as I think we may be looking at porting it to a real database in the next few months and I need to start thinking that way.

    OR....
    I'm just making stuff way too hard on myself, again.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nah, s'all good!

    So just for clarification you're proposing to have a different front-end for each user?
    This is what determines the security
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    Yes.
    They will all be identical but you get to have a little mde on your machine where ever you can remember to find it. That gets the data from the one source that is out on a shared drive (unfortunately I buried it in an unneccessarily complicated place when I first built it over a year ago).

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by georgev
    Or perhaps views are what you might be needing?
    George,
    It looks like Views are not supported in Access, at least with Jet, if at all. Do you have experience to the contrary?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by starkmann
    George,
    It looks like Views are not supported in Access, at least with Jet, if at all. Do you have experience to the contrary?
    They are but they are hopeless. No better than stored queries.

    Check out this article and the other two referenced ones:
    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by starkmann
    Yes.
    They will all be identical but you get to have a little mde on your machine where ever you can remember to find it. That gets the data from the one source that is out on a shared drive (unfortunately I buried it in an unneccessarily complicated place when I first built it over a year ago).

    there are stunts you can pull using profiles that will enforce the shortcut to access using a specific user group and a specific database... have a look at group policies and access command line switches
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    Thanks guys. I'm reading pootle reference now. I had been using the Advanced JET functions page
    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
    and wasn't getting anywhere with the views so far.

    I will have to learn more about controlling profiles and user groups.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I forgot they're not supported well, I was just... Nevermind.
    A view in Access is generally just a query
    Query1(Leavers) = "SELECT * FROM people WHERE termination_reason IS NOT NULL"
    You can se this to be a record source for a form just like you would with a view.
    Wow, read that back and it's not very clear... Enjoi anyway
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2007
    Posts
    348
    It made sense to me. :-)
    Thanks for the help and guidance.

Posting Permissions

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