Results 1 to 15 of 15
  1. #1
    Join Date
    Sep 2006
    Posts
    60

    Unanswered: user logging causes slowdown?

    Hi all - haven't been on here for quite a while. Hope you're all doing well.

    I'm working on a new contract, haven't even seen the Access db yet, though (still waiting for account setup - it's at a Navy base).

    The program manager says that the growing number of concurrent users is making the db very slow. I do know, from the documentation, that the db has a hidden form that logs each new user in the system. Fogetting about record lock issues, can this user-logging be slowing down the system? Any experiences from you all ? Would taking out this user-logging ability speed up the db?

    Thanks in advance!!

    Mike

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    anything that is done will slow an application, although whether its significant is a different thing.

    I suspect the real culprit though is liable to be poor design.. Access using JET (or bound controls) runs out of grunt somewhere around 15..25 users. if the number of concurrent users is rising Id suspect it could need a complete redesign to use unbound controls and populating data from recordsets controlled by you / the program
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Mike,

    I have a book at home that'll help. The JET in MS Access starts to crash when it hits its 2gig limit which can be causing an issue. I forgot the fix right off the top of my head but I know there is one. I believe another fix that I read for a problem, much like the one you're saying, is to split the back-end into multiple back-ends, though I don't know how that'll work but that is also in the other book that I left at my house.

    I will look at it when I get h ome in about 9 hours and post the fixes they say.

    B

  4. #4
    Join Date
    Sep 2006
    Posts
    60
    Guys -
    * yes, the db has the critical size of 2 GB.
    * I'm unable to look at the db right now, but I suspect that it's designer, who has already left, used bound controls. I only have about 2 months to do here, so re-designing may be out the question.
    * Grafixx, waiting for your tip - I'll have a look here tomorrow.

    Thanks to both of you for your input!!

  5. #5
    Join Date
    Sep 2006
    Posts
    60
    Grafixx, come to think of it - what's the name of the book you're referring to? Maybe I already have it.

  6. #6
    Join Date
    Aug 2006
    Posts
    559
    It's a book called, "Fixing Access Annoyances".

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If the database is 2gig in size, I would highly recommend making the forms unbound (and get SQL Server). Having a bound form always open by multiple users is not the best thing for an MSAccess mdb with Access tables and many users in it via a network. A network hiccup is all it takes to corrupt the mdb. You can also see the dbforums data bank (in the MSAccess forum) for a vbscript to clone the frontend which helps with multiple users. I've used this very successfully for a front-end mdb/mde with linked tables. It would probably be a great quick fix for you verses redesigning. As long as you're working with linked tables (Access or SQL Server), you can use it. The front-end will clone itself and launch the cloned file with the user's login name attached so each user is essentially in their own mdb/mde file. It eliminates problems you would encounter with bound forms and multiple users in the same mdb/mde.
    Last edited by pkstormy; 06-18-07 at 21:12.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Mike,

    I scanned the two pages from the book with what I was talking about in my previous posts. It's a lil' off but it still has all the contents of the pages on the scan. It's two pages (2 files) in PDF format.

    Hope it'll help with your issue.

    B
    Attached Files Attached Files

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by mike21152
    Guys -
    * yes, the db has the critical size of 2 GB.
    If they won't dump access as the backend I suggest moving on. You're in for a world of hurt.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    Sep 2006
    Posts
    60
    Hi guys..sorry for my delay. Been busy.

    Many thanks for your input(s). This is a short-termer so right now, I'm concentrating on looking at the code underneath everything, using my laptop. The program won't run (natch), because the previous developer had calls to a server which I don't have access to here.

    I think the suggestion of cloning the front end etc, would give the clients heart attacks because they are military/civilian. Right now the big concern seems to be sending emails from the app so that's what I'm researching, CDO from Access. The other concerns are what happens when two (or more) users submit a record at the same time, and just getting the damn thing deployed.

    And finally, as for moving on, yeah, baby! Waiting to hear more about a nice long-term (well, 6 mos to perm) Federal contract!

  11. #11
    Join Date
    Sep 2006
    Posts
    60
    Graffixx01 - thanks so much for the pdf's!!

  12. #12
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    installing sql server express might be a good stepping stone for them.

    It's free. It would immediately give you up to 4 GB of data storage, AND you'll get a much more reliable database engine. No corruption issues, and you won't have the database bloating issue. Plus a lot of bells and whistles (stored procedures, views, and more secure to boot)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  13. #13
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    If you value having a strong resume with references for each contract, you'll strongly suggest moving away from access so you don't have to explain to your next employer why your project went ka-boom three months after you moved on.

    There's something to be said for considering whether the longevity of your project will extend beyond the longevity of your contract.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  14. #14
    Join Date
    Sep 2006
    Posts
    60
    The clientele is pretty adamant that SQL Server is not on the table, while Oracle IS.

    The db is more or less finished..to suggest they move away from Access is under consideration since there's talk of migrating the db to their intranet (SharePoint) in the future. Right now, they want a robust Access db - in the user tests, it performed well enough, apparently, but, it would require a HUGE amount of time for a complete redesign with unbound fields, given the number of forms, queries, etc.

    I didn't design and develop it, but I'll do what I can to minimize the flaws in it, if I can.

  15. #15
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It looks like the clientele have their mind made up about using Oracle...I see this all the time where the software developer's dictate what kind of database software you should buy. Seems kind of closed minded to me (although I'm a fan of SQL Server).

    Perhaps you should get a SQL Server consultant and an Oracle consultant together in your discussions and talk about the benefits/drawbacks of each. Deciding on the best database server software should be your decision, not the software developers (unless of course they're going to maintain the backend db as you suggested on their intranet). If I'm going to maintain the backend db, I'd prefer to have something I know how to maintain and fits all the companies frontend software needs.
    Last edited by pkstormy; 06-22-07 at 15:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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