Hello from Nevada, USA (1 Viewer)

[samye228]

Hi,

My name is Carol. I am not new to database technologies but new to Access. I have inherited an Access database in my position and need help understanding how to determine what is going on. I am using O365 version but do not know when the DB was originally created. I found a query for MSysObjects and ran it. The earliest date was 9/25/2002. At some point the DB was linked to the 2010 file type of ACCDB.

I have many questions concerning the history of the DB, how to find answers and how to clean-up. Such as:
1. When I go to Database Tools > Relationships, I see a mess of relationships and tables with strange names, [4F4DD22E-9...]. When I open it I get a message that says it is a linked table and has the extension mdb. I'd like to know how to clean up the database and get it all in the current Access format.
2. There are tables with a _1 after them and they are related to the table with the original name, example: Agency Names table has a relationship with Agency Names_1, is this needed?
3. Is it possible to clean up table names, like the one listed in item 2 above? Agency Names has a space and it would be nice to rename to a standard such as tbl_AgencyNames.
4. How can I find what modules are not used?
5. Would it be beneficial to investigate moving to SQL Server?

Please let me know the best place to post these questions.

Thank you,
Carol

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[theDBguy]

Hi. Welcome to AWF!

The first thing we'll need to understand is the purpose of the database. What is it used for? What business process is it trying to model. I would suggest starting a new thread in a different forum than the Introduction one.

 

[NauticalGent]

Welcome aboard, Carol!

 

[pbaldy]

Welcome from northern Nevada (okay, I'm in New Zealand at this moment). FYI I moved your thread to a more appropriate forum.

 

[AngelSpeaks]

Hi Carol.

 

[Pat Hartman]

Hi Carol, welcome. It is always a challenge trying to get a handle on a new application. There is a tool that will help to tie all the pieces together and identify unused parts. It's called Total Access Analyzer and you can find it at www.fmsinc.com It produces dozens of reports that will help to understand the overall structure. Your company will almost certainly purchase a copy for you.

1. I would call a psychiatrist. No one in their right mind names tables that way.
2. Anyway, to answer some of the initial questions, the tables with the "_#" suffixes are simply used when more than one relationship exists between tables and occasionally in order to reduce the crossing of lines in the diagram. I'll use a state table as a simple example. An order has a billTo and ShipTo address so you have two instances of state in the table. Each needs a separate relationship defined to the state table. The second will have a suffix of "_1". The table isn't duplicated. This is just the convention Access uses to keep track of additional relationships. Sometimes in a large diagram you have tables that are separated by a lot of space but the both need to be related to the same table. Using state again since it will frequently be the problem. If you have to draw a relationship from tblState to tblVendor which might be on the second or third page of the diagram when printed, it might make a cleaner diagram if you add tblState to the diagram closer to where it is used so the lines don't have to cross pages. Access will suffix the tblState with the next available number. In our case, it would be "_2"
3. It is possible to clean up the table and other object names but it is a daunting task for a database of any size. Don't let that stop you. Having the kind of printed reports TAA will generate will help with this. Access also has a built in "feature" that propagates name changes. I enclose the word "feature" in quotes because because there are gotchas and you need to understand how Name Auto Correct (or Corrupt as some people call it) works to use it effectively. There are addins such as MZ-tools that have features that will change names but that type of feature is even more dangerous because the change is global and you have to use long enough strings so that you don't accidentally change names you didn't mean to change. Once you get to this point, you can ask and someone will find the code that renames all the controls on a form so that they have prefixes if you want to go that route. I don't believe the code changes references to the controls so that would require a mass change - but always do these very carefully and always save first and make a backup.
4. See the documentation for TAA
5. Moving to SQL Server should only be considered in two situations. One - you have more than about 20 users and are running into slowness due to contention. TWO - the tables are so large that they are sluggish to search. Both problems can be reduced by efficiency changes in the Access app itself. This should be pretty far down on your list.

I'm hoping the application is broken into a FE (front-end which is the forms/reports/queries/code) and the BE (tables only). The BE is hosted in a shared folder on the server. The master copy of the FE is also hosted on the server but is copied down to the user's C: drive for daily use. If there isn't a standard distribution method, we can help with that. Usually the users would click on a desktop shortcut. The shortcut will run a bat file or open a distribution database. Either will copy the "master" from the server to the local PC and open it)

The picture you posted shows that NO Referential integrity has been enforced. That means that you will find lots of bad data so that is where I would start. I would try to enforce RI. If there is an error, you need to clean up the data before you can move on. Once you have RI fixed, you can make sure that the FE is being properly distributed so that each user uses his own personal copy of the FE. The FE should NEVER be shared. You'll find lots about this topic as you dig into things.

Once these two things are cleaned up, you can work on converting the FE to an .accdb first. If the app is running OK using O365, then you are unlikely to have a problem converting the FE to an accdb. However, you need to ensure that all users are also using O365 or some Access version that is A2010 or newer. One thing you should also do at this time is to ensure that EVERY code module has Option Explicit as the second line AND that the "always use event procedures" option is checked - Access Options/Object Designers. This forces the user to define all variables and ultimately eliminates potential runtime errors caused by typos in the code.

After the FE has been converted to an .accdb, you can convert the BE.

As you get into specific questions, review the topic headings and post the question in the one that seems the most appropriate. If you can't decide just use general. Most experts review all new questions regardless of topic so your question is unlikely to be lost. Just stick with the "Microsoft Access Discussion" grouping. NEVER use the "Microsoft Access Reference" group for posting questions. That is where experts and others post samples and general advice or FAQs. Feel free to join us to discuss politics - we're pretty good about listening to each other instead of resorting to name calling Or, use the Watercooler if you want to talk about random things.

 

[Pat Hartman]

@samye228 since you are proficient in some other technology, do not make the mistake of thinking Access is the same. Access is a RAD (Rapid Application Development) tool. It does a great deal for you. This is very hard for experienced developers to become accustomed to. Your job is to understand the "Access way" and let Access be Access while you put your feet up and drink your pina colada. You can in theory build a fully functional Access application with no code. In reality, you actually always need validation code to ensure that bad data doesn't get saved so at some point you will need to write VBA. The hard thing for you will be to not write VBA first. First, you should try to find some VBA function or internal property setting or perhaps a query to handle your problem. Your last option will be code. Being proficient in two other programming languages, coming to grips with the Access event model was my biggest challenge. VBA as a programming language is pretty trivial. But figuring out which form event to use to control what I wanted to control, not so trivial.

The problem is that you can also build a fully functional application using only code and never using any of the Access RAD tools. This simply a waste of time and if you require this kind of control, move to a different platform where you have to do everything yourself. You'll be happier in the long run.

It is important to understand the event model of forms. I built a tool to help with this and it comes with a couple of videos that explain how and where to do validation. You might want to watch the videos if you have some time and play with the database to see which events fire when you do certain things. In my opinion, there is no more important form level event than the BeforeUpdate event. This is the last event that fires before a record is saved and this is the event where you put your validation code to STOP Access from saving bad data. Think of this event as the flapper at the bottom of a funnel. You close the flapper to keep the record from being saved and you leave it open to let the record fall through to be saved. Access makes it a personal mission to save data and in the beginning, you will not understand all the things that trigger Access to save. You will find that you have no worries about losing data, but you need to worry about not saving invalid data. The form's BeforeUpdate event is the solution. My hair might still have color if 25 years ago when I started with Access someone pointed out this simple fact to me.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

I'm sure you are familiar with functions so bookmark the list of VBA functions grouped by category. This is the best way to find a VBA function
Access Functions (by category) - Microsoft Support

 

[The_Doc_Man]

Hello, Carol, and welcome to the forum.

I was going to answer your questions but Pat beat me to it. She is a good resource for lots of knowledge as she has lots of experience in various types and sizes of project. Many others of us also can offer help. May I offer a suggestion or two?

You said you were familiar with database technologies. If you are familiar with normalization, great. If not, search this forum (using the SEARCH button in the upper right of the page) for 'normalization.' OR if you use a web browser, search for 'database normalization' because the word 'normalization' also is applied in math, chemistry, medicine, politics and international relations, ... a bunch of topics.

We have several "topic" forums and you can post in any of them. Try to narrow your questions to the match the topics. For instance, don't post a Table question in a Module forum. Other than that, we are wide open and ready to help. Any questions are fair game.

Jon posted a link in his welcome message. It is a short article. You can also look at the bottom of the page, to the right, to find a Help link and a Terms and Rules link.

One last thing that might help: Once you post a question, the forum has some simple matching logic that tries to find five articles that seem like they are related to your question. The "Similar Threads" list can therefore be a useful resource.