In my oldest post about the odbc load command I mentioned that you can pretty easily read data from MS Access into Stata, and even execute SQL commands from within Stata, but I dismissed the whole thing a bit too lightly. A recent look on Google Analytics showed that some people came here looking for that sort of information and, judging by how fast they left, they didn't find what they were looking for. So here's another try.

Some context first: one of my ongoing projects is a suite of Stata do-files for helping certain daily newspapers optimize their subscriber retention, using survival analysis. I have several clients. Some of their needs are common, so they can be met with one do-file, others are not so common, so they need custom code. I use global macros as bridges between custom and common code. You need this introduction to see what I'm doing in my example below.

One client is sending me a weekly snapshot of its subscriber database in the MS Access format, in a file called Submaster_YYYYMMDD.mdb. Suppose that that file resides in c:/data/my access file comes from here/. Then I must read it into Stata and save it as subsYYYYMMDD.dta in c:/data/my stata file goes here/. Now suppose that I spread this project across several do-files, so I use some internal names for these file paths in the form of global macros defined once:


global datafrom "c:/data/my access file comes from here/"
global datato "c:/data/my stata file goes here/"

Also suppose that I save the date of arrival part of the file name, YYYYMMDD, as a global macro:


global t "YYYYMMDD"

Finally, I don't want my lines to be too long, so I use local macros to compress things a bit whenever that looks practical. Now let's have a look at the actual example:


capture confirm file "${datato}subs${t}.dta"
if _rc!=0 {

   local mdbpath "${datafrom}"
   local mdbname "Submaster"
   local source "MS Access Database;DBQ=`mdbpath'`mdbname'_${t}.mdb"
   capture confirm file "`mdbpath'`mdbname'_${t}.mdb"

   if _rc==0 {
      #delimit ;
      local vars "
      ${${paper}index},
      prod_code,
      start_orig,
      last_start_date,
      last_stop_date,
      expire_date,
      rate_table,
      paper_code,
      rate,
      credit_card_usage";

      drop _all;
      odbc load,
      exec("SELECT `vars' FROM `table' WHERE (prod_code='1D' OR prod_code='3D')")
      dsn("`source'");
      save "${datato}subs${t}", replace;
      #delimit cr
   }
}

First, I check if the Stata file subsYYYYMMDD.dta exists in the path ${datato} with "capture confirm file". If it does not, then I check if the Access file Submaster_YYYYMMDD.mdb exists in the path ${datafrom}. If yes, then I read it and save subsYYYYMMDD.dta. The exec() part of odbc load is for running SQL syntax. Just to make sure that you appreciate the coolness of this: any SQL line that you could execute in Access, no matter how complicated, can be put between the exec() parentheses. Mine is simple -- I wrote the SQL commands in all caps for clarity -- but yours needn't be. You can also combine Stata locals with SQL syntax like I did.

I switch to the semicolon delimiter when it helps readability. It allows the line with SQL syntax to wrap around, and the listing of my `vars' local as one word per line. This, of course, is optional. The checks for the existence of either file are also optional. They keep my do-files from exiting with an error if I happen to run them on a day when either file does not exist -- either because the source file Submaster has not arrived yet, or because it hasn't been translated yet into Stata.