Linked Tables

[samye228]

Hello,

I am interested in understanding about linked tables. I have inherited an Access database that was created approximately in 2002. When the format changed the database mdb tables were linked to the new Access version. Is this common practice? Would it be beneficial to update these tables?

Also I have a table with what looks like a GUID. If I look at the relationships of the database, I see that table is related to a named table with the same fields. What would cause this? Is there a way to "fix" this?

There are also several tables that have relationships with a _1 after it. I'm assuming that is probably a default name when someone created the relationship. Example, Agency Names is related to Agency Names_1. How do I find out if this relationship is necessary?

The database does not follow any conventional naming standards. Is it worth it for me to try and update table names, forms, queries etc? If so, how would one go about this?

Thank you.

Thank you,
Carol

 

[theDBguy]

Hi. I'll try to answer some of your questions.

When the format changed the database mdb tables were linked to the new Access version. Is this common practice? Would it be beneficial to update these tables?

Newer Access applications shouldn't have any problems connecting to data stored in an older version (unless it was created with the 97 version). If you like, you could upgrade the data storage to newer Access version, but that's entirely up to you.

Also I have a table with what looks like a GUID. If I look at the relationships of the database, I see that table is related to a named table with the same fields. What would cause this? Is there a way to "fix" this?

One possible cause is it might have been a part of a "replication" database. I'm not sure if it's needed to be fixed. What was the database used for? Who and how do they use it? That might tell you if that table is needed or not.

There are also several tables that have relationships with a _1 after it. I'm assuming that is probably a default name when someone created the relationship. Example, Agency Names is related to Agency Names_1. How do I find out if this relationship is necessary?

Again, this is something you can answer by "knowing" the business process the database application is trying to model. I would typically see a similar setup when there are self-referential relationships. For example, employees and their supervisors or children and their parents.

The database does not follow any conventional naming standards. Is it worth it for me to try and update table names, forms, queries etc? If so, how would one go about this?

I think that's a personal choice too. If you think it will help you in the future to spend the time now to fix the naming convention, then you should do it. As to how, may be there are tools for doing something like that. However, I cannot recommend any right now.

Hope that helps...

 

[jdraw]

Further to theDBGuy's comments, I would ask how important is this database to the business? If critical, and if you are the "maintainer/team", then it seems imperative that you or someone be trained accordingly. Often, there is a patron/manager who depends on some these "applications" and it is him/her who can judge the value of same. That person may have knowledge to help answer your questions (at least in part).

Does the organization have any data management standards and procedures? Any manuals (user guide, design/technical manual...)? Anyone knowledgeable of the application still with the company?
How did this "inheritance" happen? Why you?

Welcome to the forum!

 

[Galaxiom]

Naming standards are very important

Please elaborate the kind of standards are you talking about.

 

[samye228]

Hello,

Thank you all for your responses. Just to clear up about posting twice. I received an email per my Intro email and it was suggested I post the questions in the appropriate forum. I apologize for the inconvenience. Thank you Pat Hartman for your detailed information in the intro section, it is much appreciated.

The application is vital to the business unit as it supports the statute of the state agency. There are no "experts" but once I figure out my approach, I can probably get some funds to work with a consultant. This is state government, very hard to get funding.

What I've been working on mostly is cleaning up the data because at some point we are hoping to get an enterprise solution that will meet the entire state's departments needs. The app contains information about all Nevada state-owned buildings.

I can add standards as I start updating/organizing things. The standards would be naming conventions, i.e. qry_ for Query, tbl_ for Table, etc.

This DB was created by someone who knew nothing of data standards or processing so it's a legacy system that needs to be moved desperately and really interface on an enterprise level with other state systems such as financial, lands, etc.

It sounds like my best option is to leave as is and work on massaging the data so it contains what we need.

Kind Regards,
Carol

 

[GPGeorge]

Hello,

Thank you all for your responses. Just to clear up about posting twice. I received an email per my Intro email and it was suggested I post the questions in the appropriate forum. I apologize for the inconvenience. Thank you Pat Hartman for your detailed information in the intro section, it is much appreciated.

The application is vital to the business unit as it supports the statute of the state agency. There are no "experts" but once I figure out my approach, I can probably get some funds to work with a consultant. This is state government, very hard to get funding.

What I've been working on mostly is cleaning up the data because at some point we are hoping to get an enterprise solution that will meet the entire state's departments needs. The app contains information about all Nevada state-owned buildings.

I can add standards as I start updating/organizing things. The standards would be naming conventions, i.e. qry_ for Query, tbl_ for Table, etc.

This DB was created by someone who knew nothing of data standards or processing so it's a legacy system that needs to be moved desperately and really interface on an enterprise level with other state systems such as financial, lands, etc.

It sounds like my best option is to leave as is and work on massaging the data so it contains what we need.

Kind Regards,
Carol

I hope you have a solid, consistent and reliable backup protocol in place. One should never work on "the master" production copy of an mdb or accdb. And, as a rule of thumb, one can't have too many backups and a system for maintaining them.

 

[Galaxiom]

The database does not follow any conventional naming standards.

The "conventions" are not important especially the nonsense about prepending field names with a datatype.

What is important is that the names are meaningful. It would be a waste of time to change them unless they are meaningless.

If you do need to change any names use the Replace facility in the Total Deep Search tool in Vtools. Turn off NameAutoCorrect in Access before using the tool. Review the names before allowing them to change let you get false matches.

 

[The_Doc_Man]

Just to toss my two cents' worth, the best naming convention to implement & follow is the one that (a) makes sense to you and (b) isn't so complex that you can't follow it effectively. Whether you use qry_ or q_ or don't even use underscores for query names, just follow your own guidance.

When reviewing this app, the advice about deciding the future before expending too much time on making changes? Solid gold advice. But IF you decide to stay with Access, then remember that your purpose is to track the business model, not control it. In a sense, you are building a model or a simulation of some procedures that would be used to manage the property. IF you are going forward with this, never build something that forces the tail to wag the dog. I come from a physical sciences background. We have a rule... if reality disagrees with your model, there is 99.9999% chance that the model is wrong. Therefore, whatever you do, remember that you have to always respect reality.

 

[Isaac]

The "conventions" are not important especially the nonsense about prepending field names with a datatype.

What is important is that the names are meaningful. It would be a waste of time to change them unless they are meaningless.

If you do need to change any names use the Replace facility in the Total Deep Search tool in Vtools. Turn off NameAutoCorrect in Access before using the tool. Review the names before allowing them to change let you get false matches.

Convention does not necessarily mean what you seem to think.

Meaningfulness could be a convention, depending on what the company values or considers quality.

Anything could be a convention.

Naming things thoughtfully is very important, @samye228 , and I agree with your instinct that called this out.

However, it can be difficult to do post-implementation but is a good lesson learnt for later.

 

[HealthyB1]

What is important is that the names are meaningful. It would be a waste of time to change them unless they are meaningless.

If you do need to change any names use the Replace facility in the Total Deep Search tool in Vtools. Turn off NameAutoCorrect in Access before using the tool. Review the names before allowing them to change let you get false matches.

V-Tools seems to be a great tool. However is there a user manual for it please? I am trying to figure out how to use the DIB Pictures for controls on my forms.