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:

SQL Server Hosting

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.