Go to Top

Insert CreatedBy and CreatedOn in any missing tables

When we design Access with SQL Server solutions we almost always add CreatedBy and CreatedOn to all of our tables, allowing our clients to see who created records in the Access frontend.

Script to add CreatedBy and CreatedOn Automatically to tables

Many times we inherit SQL databases and we need a quick way to add both fields to all tables in the database. To that end we use the following script will check if CreatedBy and CreatedOn are present in the table and if they are not, add them:

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

The script is reading from tblTablePermissions which is just a list of SQL Server tables used by the application. We use the table in our DSNless technique.

Once you have executed the script SQL Server will automatically date stamp and place the user’s network name in the CreatedBy field, allowing you to create analysis on how many new records were added to the database and by who over time.

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

One Response to "Insert CreatedBy and CreatedOn in any missing tables"

  • Ben
    March 14, 2016 - 7:06 pm Reply

    Juan, if I were to use your sample code would I need the “Apple-converted-space” markup statements or is that just an artifact that your code editor put in that I can ignore?

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.