Statalist The Stata Listserver


[Date Prev][Date Next][Thread Prev][Thread Next][Date index][Thread index]

RE: st: odbc table changes in MS Access


From   "Buzz Burhans" <buzzb3@verizon.net>
To   <statalist@hsphsun2.harvard.edu>
Subject   RE: st: odbc table changes in MS Access
Date   Wed, 07 Feb 2007 12:50:31 -0500

Thanks Joseph,

You have suggested several possibilities that look promising; I'll report
back after I see if I can apply them to the current project.  The -as()-
looks promising, but I suspect it will not work for my current data project
because the process generates a new variable in some situations, so I need
to be able to expand the table rather than substitute into it.

The possibility of an -odbc exec()- that can DROP or ALTER the table could
be useful, I appreciate the idea. I'll look at the SQL Reference you
suggested.  Thanks

Buzz

-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Joseph Coveney
Sent: Wednesday, February 07, 2007 11:54 AM
To: Statalist
Subject: Re:st: odbc table changes in MS Access



Buzz Burhans wrote:

Is there any way to use Stata's -odbc- command to append to or replace an MS
Access table if the appended or replacement data has either new or different
variable names than those that already exist in the Access table?

Alternatively, is there a way to automate the deletion of an Access table
(but not the Access database) from within Stata?

Using -odbc insert,...insert- or -odbc insert,...overwrite- fails if the
table variable names are not the same in the existing table and either the
appending (insert) or the replacement (overwrite).

----------------------------------------------------------------------------
----

You can associate the Stata variables names back to the Access database
table's names on-the-fly during the append or overwrite operation using
the -odbc insert . . ., as()- option.  Try something like:

odbc insert primary_key newvariable1 newvariable2, ///
  table(. . .) dsn(. . .) insert ///
  as(primary_key oldcolumn1 oldcolumn2)

There is also a SQL statement that you can execute via -odbc exec()- that
will DROP TABLE.  And you might even be able to work something out with
ALTER TABLE, but it would probably be convoluted.  (Check the Microsoft
Access Help under "Microsoft Jet SQL Reference" tab and open the "Data
Definition Language" to find how the statements work.)  But it sure sounds
as if it would be far safer and easier to go the -odbc insert . . ., as()-
route.

Joseph Coveney

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   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