For many of us, Access serves as an easy to use ETL (extract, transform, load) tool. That is, Access allows us to extract data from many sources, then reformat and cleanse that data into consolidated tables.
Many of us also automate our ETL processes with the use of macros that fire a series of queries. This typically works quite well in most cases.
There are, however, instances where our ETL process requires us to add primary keys to temporary tables in order to keep data normalized during processing.
In these situations, most people do one of two things. They stop the macro in the middle of processing to manually add the required primary keys. Or they create a permanent table solely for the purpose of holding a table where the primary keys are already set.
There is a third option that, in my opinion, doesn’t get enough attention – dynamically create the primary keys using the SQL ALTER TABLE statement. With an ALTER TABLE statement, you can change the structure of a given table with a simple SQL statment. Of all the useful actions the ALTER TABLE statement allows you to do, creating key constraints is the one I find myself using most often.
Here’s how:
Step 1: Start a new query in design view and switch to SQL view.
Step 2: Enter an SQL statement similar to this:
Alter Table [TableName] Add Constraint ConstraintName Primary Key(Field1, Field2)
Here, we are creating a compound key on Field1 and Field2.
Step 3: Save the query
Once you save, you’ll be able to see your newly created ALTER TABLE statement along with your other queries.
Simply add your new Alter Table query to your macro where appropriate.
There you have it. You’ll never have to worry about creating of primary keys mid-process again.
Nice post! It’s somewhat comforting to know others have queries “7a, 7b, 7c…” in their ETL flows…
I use a slightly modified version of your suggestion.
Instead of the “make table, alter table” flow, set up a ready-made table complete with keys, preferred field names, etc. ahead of time. Then the flow becomes “delete * from table, insert into table”.
There are a couple reasons I like to do it this way. One, the pre-fab table can be set up with keys ahead of time, so incoming data that does not conform will cause a failure one step earlier. Two, normalized field names can be established in the ETL (rather than modifying the source data queries, which typically come from an external DB). This makes downstream data massaging a little easier.
This does require the extra step of setting up the receiving table first, but you can do this with a make table query, then quickly change the make query into an append query for subsequent use.