Tracking changes/metadata added later?

[cj1997]

Hi everyone,

Quick question - just building my first database (so. much. pain). Will take >1 year to collect/enter thesis data. Eventually I want to make it open to others/make changes as new research is done. Can I add change-tracking etc. later (perhaps once I know ANYTHING about code etc.). Basically - can I worry about that later, or--like structure etc.--does it have to be set up before I can enter any data to begin with??

(for the initial part in which I am the only one using the db I was planning on just having it stamp the date of the original entry)


Thanks!

 

[jdraw]

I recommend you read and work through this tutorial to ensure your database structure (tables an relationships) match your needs.

http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

As for capturing data and reuse --all is possible. The key is to make sure your design represents your "business" scenario.

Good luck.

 

[cj1997]

Thank you very much for the link!

I am still tinkering out structure issues. But good to know that I can add tracking features down the road!

thanks again!

 

[Pat Hartman]

The schema can make or break your application so take care to get it right from the start. Once you enter data, you will have to do something with that data if you need to change the schema. Post ideas as they come to you.

Also, for your own sanity, pick a naming standard and stick with it. Table/column names should contain ONLY letters, numbers, and the underscore. NO embedded spaces or special characters. You also need to be careful to avoid reserved words so lean toward compound names unless you want to memorize the list of VBA, SQL, and Access reserved words.

CustNum or Cust_Num = Good
Customer # = Bad

CustName or Cust_Name = Good
Name or Cust Name = Bad

Every application has some central entities such as customers, vendors, documents, products, etc. I decide on an abbreviation for these things early on so I am consistent in its use. Even though CustName and Customer_Number are both valid/good names, they are inconsistent and will cause you (and your successors) extra thinking and work because you'll always have to remember where you've used Cust vs Customer and CamelCase vs the underscore to separate words. I personally always use CamelCase.

 

[cj1997]

Thank you for the advice! I have used the number sign in a number of cases to indicate fields that are primary or foreign keys (and therefore also numbers). Does this then make it more difficult later on with Access? If so I could always go back through and change each instance to 'Num'. I will audit all my table/column names to make sure they are consistent!

Also, I just wanted to clarify. When you talk about changing the schema are you referring to the structure of the database itself? Or were you specifically talking about adding event/changes tracking in the future? I am wondering because I am making my finishing touches to the structure right now (before starting data entry), but in the future (when other people may have access to the data or I want to update things), I want to add the ability to track changes made. I am hoping to do it later only because it will give me more time to learn code etc, and I need to start entering certain parts of the data ASAP while I have access to them.

Thanks!

 

[Pat Hartman]

Since embedded spaces and special characters are technically illegal, you will always need to encase the name in square brackets in queries. Once you get to code, you won't be able to use them at all. Programming languages simply do not support them. Access, being very forgiving, will change all the offending characters to underscores so you'll end up with names like Cust__ - notice the two underscores. One for the space and one for the #. Better to use the industry standard of somenameID as your key indicator. Also, if you had three column names - Cust $, Cust #, and Cust %, they would all generate the SAME name in VBA and Access would not be able to create events.

The schema is the structure of the tables. If you get it right, you will find it much easier to build the rest of the app. You won't have to write excessive code to make up for bad table design.

Before you turn the database over to others, you should build forms and reports to facilitate the data entry. Access is not Excel. You never have to work with raw data nor should you.