Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    56

    Unanswered: Any database monitoring tricks or tips?

    Hi,

    Just wondering if anyone has any additional suggestions/tips or tricks for monitoring Access databases.

    I am using the .ldb dll that Microsoft posted, but was wondering if anyone else has suggestions for database monitoring.

    I have created a tool that allows the db's here to "monitor" several hundred db's. The tool does the following:

    1. allows db admin to "load" db to monitor into treeview control- breaks out by db objects (tables, queries, forms, etc.)

    2. allows them to retrieve db properties and formats information.

    3. allows db admin to get information for each db using the MS .ldb dll

    4. allows them to create a new db, import objects from any db, version control the object, add a description, and automatically connect to the required tables.

    5. allows db admin to schedule a db backup and then perform a compact on the db as well.

    6. email integrated.

    7. Access db driven "chat" tool that allows db admins & db users to "chat". Our network folks like this approach better than the AOL and Yahoo chat systems that were bogging down the network.

    For some reason, I feel that I am missing something. Any suggestions?

    Thanks,

    John

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you just showing off John?

    I'm sure you are missing plenty of things that you could put in but I think this sort of thing tends to get driven by need. The only thing that comes to mind is something that comes up as a question every so often - "how do I kick out users if I want to perform maintenance". But if you haven't had the need then why build it in?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2006
    Posts
    56
    Hi pootle!

    LOL...No, not showing off, trying to save a company that I am doing some consulting for some money. They are working with a vendor that I feel is trying to rip them off for quite a large chunk of money (just shy of 50K) to come in and create an admin tool. The company I am working with is relatively new to the Access/database world, and they want to have "best practices" in all of their IT areas (boy, doesn't that sound familiar?). Having sat in on a couple of meetings with this vendor, I don't have a high level of trust or confidence in them.

    They have tried to position me as an "outsider" who doesn't understand the client's needs or business because I questioned the methodology they were going to use to develop the tool. The thing that caught my attention the most (and most importantly) was that they didn't know what a functional specification document was.

    My tool isn't the complete toolset, and I admit that I haven't done the full analysis that I would normally do in a development project. I have put this tool together in less than two weeks on my own time. I just want to demonstrate that it shouldn't take 25 WEEKS at 50/hr to develop a database in access.

    But, now that you mention it, I would LOVE to know how to kick users out of a db to perform maintenance.

    Thanks,

    John

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by John_Farren
    They are working with a vendor that I feel is trying to rip them off for quite a large chunk of money (just shy of 50K) to come in and create an admin tool.
    Goddam - so you're the one getting in the way of my Caribean holiday home!

    Quote Originally Posted by John_Farren
    But, now that you mention it, I would LOVE to know how to kick users out of a db to perform maintenance.
    No big secret - just takes a wee bit of work.

    Table in a BE db (not THE BE db as you want to access this before they start locking the BE) with a single YesNo field. Set to yes when you want people out. Have the client check the field every so often (timer event of load up form for example). If it is Yes, politely inform the user what is going on and close down. Set to No when maintenance done.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2006
    Posts
    56
    Thanks pootle. That is the logic that I am using in the chat application that I wrote for the administration chat tool.

    I was thinking of writing code using the .ldb dll to continually "ping" a database for the number of users logged in. When it hits 0, then have the chat form that I created push a msg to all users that the db is down for maintenance. I am also using email to send a msg reminding users to logout of the application for evening maintenance.

    I didn't know if there is a way to (from code) shutdown the application remotely, then perform the backup/compact/etc. other than to open it exclusively and close it. I thought about grabbing the pid from task manager and killing the process, but thought that might introduce a level of instability to the database.

    I also thought about writing a routine to modify the table names (effectively breaking the link with any client db), closing the db, compacting it, then renaming the tables back for client access.

    thoughts?

    Thanks,

    John

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Unless you have some fairly heavy duty requirements I wouldn't bother frankly. If you are at the point of coding something like that then I would look at coding all your maintenance (rather than performing it in person) and have it applied as a down time job with windows scheduler.

    The main problem with shutting down the clients (apart from what you have mentioned) is that there could be any number of them logged on, so you are looking at enumerating too. Using GetObject() and automating the Quit method might be more stable than killing the process from the OS.

    Is your app disconnected or linked? If disconnected you could probably do all these things fairly elegantly.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Location
    NJ
    Posts
    3
    Quote Originally Posted by John_Farren
    I am using the .ldb dll that Microsoft posted
    Please excuse my ignorance, but what .ldb dll is this you're referring to?

  8. #8
    Join Date
    Feb 2006
    Posts
    56
    No problem. Microsoft developed a .dll that retrieves information from the locking file for an Access database. The .dll has been available for download from Microsoft since Access 97. It allows you to determine the number of users of a database, how many users are logged into the database, total number of users, and if a user has a corrupted database. The .dll is msldbusr.dll and is stored in the windows directory. Do a search on knowledge base or the download center under Access 97 and you will find it.

    Thanks,

    John

  9. #9
    Join Date
    Feb 2006
    Posts
    56
    No problem. Microsoft developed a .dll that retrieves information from the locking file for an Access database. The .dll has been available for download from Microsoft since Access 97. It allows you to determine the number of users of a database, how many users are logged into the database, total number of users, and if a user has a corrupted database. The .dll is msldbusr.dll and is stored in the windows directory. Do a search on knowledge base or the download center under Access 97 and you will find it.

    Thanks,

    John

Posting Permissions

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