Joseph,
Many thanks for your help. I can now read the Access files. I just assumed that I would need extra quotes for the space in the directory name.
Martyn
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Joseph Coveney
Sent: Tuesday, July 28, 2009 4:42 PM
To: [email protected]
Subject: st: RE: Re: ODBC - Access files
Martyn Sherriff wrote:
I am able to read Access files via ODBC if I set the dsn up via the control panel, but as I have a large number of files I would prefer to do it directly from the command line (based on www.ats.ucla.edu/stat/Stata/faq.odbc.htm):
odbc load, dsn("MS Access Database;DBQ="c:\Students\David B\rafeala.accdb"") table("t11")
but this fails with the error message "option dsn() incorrectly specified".
Have I made a syntax error or do I have to use the control panel with Access? I have no trouble reading Excel files from the command line.
I am using Vista/ Stata 11.
--------------------------------------------------------------------------------
I've spent all day today with Stata 11 reading Microsoft Access 2007 files via ODBC just as you wish to do, so I know that it can be done easily.
The only thing that I can see in your command syntax is the presence of two many double quotation marks.
Try something analogous to this:
local dsn MS Access Database;DBQ=rafaela.accdb;DefaultDir=C:\Students\David B\;
* odbc query "`dsn'", verbose schema
odbc load, table(t11) dsn("`dsn'") clear
etc.
In this context, you don't need the extra double quotation marks around the directory, even when it has spaces, because you'll be using quotation marks in the -dsn()- option.
I like the new -merge- syntax, even though I got gently chastised by Stata today for absent-mindedly using the "old" syntax, but don't be afraid of using SQL in the data access for your joins--take advantage of whatever data modeling and design that you've been given in the database; in a do-file, you can put stuff like
local dsn MS Access Database;DBQ=Working.accdb;DefaultDir=<whatever>\;
#delimit ;
odbc load, exec("
SELECT G.* FROM Selections AS S
LEFT JOIN GeneralSummary AS G
ON S.clinic_id = G.clinic_id AND S.patient_id = G.patient_id
") dsn("`dsn'") clear;
#delimit cr
The do-file can be created on-the-spot in the do-file editor so that -#delimit ;- works. It helps to see the SQL statements in a more natural layout. (You have to eschew using semicolons to end the SQL statements, though, with this tactic.)
Joseph Coveney
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
