Statalist


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

st: RE: RE: Re: ODBC - Access files


From   "Sherriff, Martyn" <martyn.sherriff@kcl.ac.uk>
To   "statalist@hsphsun2.harvard.edu" <statalist@hsphsun2.harvard.edu>
Subject   st: RE: RE: Re: ODBC - Access files
Date   Tue, 28 Jul 2009 20:56:25 +0100

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: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Joseph Coveney
Sent: Tuesday, July 28, 2009 4:42 PM
To: statalist@hsphsun2.harvard.edu
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/



© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   What's new   |   Site index