Microsoft·451 discussions

Effective use of MS Access 2010

Locked

Tags: Microsoft

Greetings all,

I have questions about the use of MS Access 2010 in the real world. I just started a job 4 days ago where I am responsible for “all things IT” at a local manufacturing company, but my job right now is mostly MS Access administration. By “administration,” I mean building new forms using VBA as needed, fixing reports that no longer work, etc.

QUESTION(S): I have learned to build Access forms and reports from queries that just query a few tables directly, but this database builds them (reports at least) from queries that query multiple queries that query some tables and queries that query still more queries. Is that typical in the real world? They have complained that performance is slow, and I wonder if that is part of the problem. Also, the primary database links to multiple other Access databases where many of the tables actually reside. I’m wondering if they are pushing the limits of MS Access’ capabilities, or maybe just have a poorly designed database. It is also true that multiple people have had their hands in this thing over the last few years and it appears there have been many “Band-Aid” fixes to the database that resulted in duplicate info, fields and maybe tables that are no longer used, etc. And NOBODY put any comments in their underlying code for these forms! Grrr. I’m thinking this thing should be re-built from the ground up for normalizing and design consistency, but that is not something I have the skills for at this point, let alone familiarity with the db and general operations… maybe in a year or so….

Background so you know who/what is asking and perhaps tailor your response accordingly: I am a 45-year-old college student 1/2 way through to a bachelor’s degree in database/web development with only classroom experience in PHP/MySQL, Java, Access, etc… ok, a little prior “hands-on” in Access. I learned Visual Basic about 10 years ago and never used it again, but I’m getting along with VBA so far. InfoTech has not been my full time profession in the past, but I’ve had my hands in it to some degree over the years and it is where I am headed full-bore now. I’m also a self-taught networking nerd. I excelled in the classroom, but I recognize that the real world is a different animal.

Topic

Effective use of MS Access 2010

In reply to Effective use of MS Access 2010

chris106, From your background, you sound plenty capable to come up to speed quickly. However, adding years to your training will not fix this. It is apparent that you’ve inherited a nightmare! I’ve seen this again and again in my 20 years of IT. When companies won’t allow proper time for requirements gathering and extensive testing prior to go-live, this is what happens. And, as more and more hands come in to touch (“fix”, “optimize”) the code, it is harder to discern what might have broken during the fix…because there is never enough time to test and get users to sign-off on the application.

I’ve also been a manager who was thrown into the cubicle to fix code from some long gone developer. If it is your responsibility to handle “all things IT”, then you will need to push the reset button. The powers-that-be need an honest assessment from you. Manufacturing data is complex and mission critical. If you have duplicate “data”, then you don’t have “information” upon which to make strategic/tactical business decisions. I would think it far easier and ultimately less error-prone to analyze the Access Forms and Reports that the users actually rely on in daily operations. That would give you a list of tables to consider (you can copy the structure of those tables into a new database and create the relationships). Have a nightly backup of the current database if possible (i.e., if the number of users/records supports that frequency). Now you can begin to assess only the code/queries that would be needed for the Forms and Reports the users agreed upon. It won’t be easy, but you will be able to know you’ve done your job with excellence, and all the stakeholders will know they can rely on the integrity of the data. I will follow this post to see if you have good news! Regards, Mary

[Author]

Replies