Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000 Report Management Strategy (Access 2000)

    I'm belatedly realizing that you can't make ANY changes to objects in a shared Access database. While I can see the reason for this in forms and code, it seems to greatly complicate the ability for users to create and share reports.

    Our clients are still using the Jet Engine database as the backend, and they are accustomed to making and sharing their own reports. So, it looks like everyone has to log out of the system in order to add a new shared report. Does every user that wants to write and share a report have to work with SourceSafe?

    Does someone have another strategy for flexible report management?

    Michael Palladino, San Francisco

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    Give each user a copy of the frontend for starters. I'd also consider having another database (again local to each user), that was just for custom reports.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    I understand that they can work on local copies for development, it's managing the distribution of new reports across the company. For shared reports, it seems heavy handed to have to shut down the system every time you decide to bold a field in a report.

    Does SourceSafe also require exclusive control of the database to check in a modified object from a development database to the production database?

    MIchael Palladino, San Francisco

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    I don't really know, since I don't work in a multi-developer environment. Maybe someone else can jump in?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    Thanks. The advantage to Access 97 was its flexibility. If you wanted full scale VB, then you would use that instead. Now it's not much different from VB, which is easier for Microsoft and a loss to the rest of us.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    Maybe it relates to the complexity of my databases, but I've found Access 97 extremely unstable when one person is changing forms or reports while someone else is using the database (even if that other person doesn't go anywhere near those forms and reports while they are being changed); it's an excellent way to end up with a corrupted front-end (I always split my databases to cut down on the problems caused by corruption). I now give users individual copies of front-ends to reduce the likelihood of corruption.

    I have assumed (purely a guess on my part) that Access 2000 insists on exclusive access when changing most things for exactly that reason.

  7. #7
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    There were definitely some caveats, but we were able to greatly minimize the problem in practice. We have the client data in one database, most of the code in a library, and the forms/reports/queries in the front end. I don't think we every experienced corruption with that setup, but sometimes a change would get lost. Current users would also not see the change until the logged off;/on, but it was great to be able to add/modify a report immediately for a user.

    MIchael Palladino

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

    Re: Access 2000 Report Management Strategy (Access 2000)

    This has been a thorn for us as well - with 2, 95 and 97 you could make changes when you felt like it and most of the time there were no problems. However we've recently been through this issue with several medium large clients running Access 97 databases (35 to 100 users) with a SQL back-end, and we did have problems with Access2k corruption from time to time. Initially, we made the front-end a read-only MDE file to minimize the problem, but when we converted to Access 2k, we decided to deploy all front-ends to the local workstation. Design changes are handled with a custom launcher installed on each workstation which checks for any design changes and downloads a new copy of whatever has been updated, including DLLs and OCXs. It seems to be working pretty well for us, though users occasionally complain that they created or changed a query and then we overwrote it with a new version. Thus we created a small custom database for users that wanted to do ad-hoc queries and that seems to satisfy them most of the time.

    Another option is to use replication and push design changes out that way. We have one large client who currently does that, but there are most of the same issues, and it causes substantial bloat in the size of your front-end database, and the management issues can get ugly. If you don't have lots of users (<10), I would probably choose to have a development master and copy design changes to the production database in off hours if possible and kick everyone out when you had to make a chage during busy periods.
    Wendell

  9. #9
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    I think the idea of a custom launcher that upates the local frontend is probably the safest way to go.

    I'm still not with the Report Maintenance aspect, though. We've been using Access for all reports in our applications. Is it time to move to something else? I haven't used Crystal for years. To what degree does it integrate into Access? Would that let us create and share reports in real time?

    Michael Palladino, San Francisco

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

    Re: Access 2000 Report Management Strategy (Access 2000)

    I missed the point that users are wanting to share reports - a launcher doesn't do much to solve that kind of problem - it works well if developers are trying to push out design changes. If your users have the front-end on their workstation, they will be able to create things in their local copy. But to share them with others they would need to export them to the master copy. And SourceSafe is an optional product here - using it introduces some significant overhead.

    Actually, you can make changes to queries and table designs (in some cases) without having exclusive control. But forms, reports, macros and VBA changes require you to have exclusive control. I've never used Crystal as we don't normally do VB stuff, but my impression is that Access is generally just as capable as it is. Maybe someone else can comment on its pros and cons.
    Wendell

  11. #11
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    I always find it interesting to hear from other application developers and what gets built into their systems. We primarily create customized systems based upon an integrated development environment of our own devising that define and integrate 100% of the data that users need for their jobs (mainly in higher education and nonprofits). Shared report maintenance is a big priority because we move them off local copies of data and spreadsheets. So, they need a way to get whatever they have entered back out of the system.

    Michael Palladino, San Francisco

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

    Re: Access 2000 Report Management Strategy (Access 2000)

    I re-read the thread and noted that you appear to be using Source Safe for what you do. But I don't think you want to try to run that on user PCs - there's lots of overhead to having it active - databases will bloat by as much as 200%. We work in a multi-developer environment, so we use it to keep ourselves from getting too tangled up (even then we occasionally do!). But when we deploy a database to a user, we remove Source Safe from it. Especially if you have a medium to large number of end-users (say >10), it is an awkward way to work as you are frequently checking out the data and miscellaneous objects and that's SLOW. I think I would create a special end-user reports database that resides on a server (so it gets backed up). Then when users create a custom report they do it in their own front-end, and if they want to share it, they export it to the custom database. You could even automate the function of exporting it so they don't have to mess with the system and know too much about menus. That would also deal with the possibility of putting it in the wrong database.

    FYI, we do custom database development as well as projects that involve OLE automation with other MS Office apps. Our customers are generally small and across a wide spectrum of industry - retail, engineering, custom manufacturing, religious. and financial. We typically use a split front-end / back-end and use add-in (or library) databases to share functions across user apps. Several of our customers run SQL Server back-ends rather than Access/Jet. It's an interesting world out there, isn't it.
    Wendell

  13. #13
    New Lounger
    Join Date
    Apr 2002
    Location
    San Francisco
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Report Management Strategy (Access 2000)

    We have a base set of modules, for higher education and nonprofits, but our service always includes customizing the system for clients. We pretty much cover everything but accounting, and we can at least import from whatever they are using so that they get all necessary data in one place.

    We haven't been using SourceSafe, but we've never had more than a couple of programmers working on a project at one time. We do have a standard library database for all projects, plus a frontend for reports/queries/customized programming and a backend for data.

    We have a report management system built into the standard product so that reports created in the database can be assigned to any screen in any module, so they don't have to open a second database to print reports, although a separate database for development is now obviously required.

    I think that if Microsoft had any concern for current Access users, they would have realized the flexibility through Access 97 was the critical differentiating factor from regular VB environments. All they would have had to do was institute even a simple "object locking" function so that you couldn't edit a report or form if it was open. That should have improved stability and would have kept the flexibility.

    Instead, they took the easy way out, and just check the "exclusive flag", and if it's not true, you're out of luck. The official justification is just self-serving.

    An interesting world, indeed...

    Michael Palladino, San Francisco

Posting Permissions

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