Dear Joseph,
I pursued your thoughts on SQL exec(DROP TABLE tablename) and was able to
drop the table from within Stata.
I loaded the MS Access table into Stata via odbc, saved it in a tempfile,
then appended (in Stata) the records that expanded the table (expanded by an
additional variable) to it from a different tempfile.
Then I used -odbc exec(DROP TABLE tablename), dsn(databasename)- and dropped
the original table. After it was gone I replaced it with the expanded table
in Stata memory using -odbc insert...create-
After your suggestion, when I fist tried this I initially had an error in my
syntax for the exec(DROP TABLE...) statement, and am grateful to Kevin Crow
at Stata Corp for helping me with that.
I would not have succeeded on this without your suggestions earlier today to
use the SQL -exec- statement. Thanks very much.
I have posted a bit of the details here because the -odbc- facility of Stata
is really quite useful for interacting with data stored in external
databases; it doesn't seem to be discussed much on the list and I presume
many people might not be aware of it or of its potential utility.
Thanks again.
Buzz Burhans, Ph.D.
Dairy-Tech Group
Phone: 802-755-6842
Cell: 802-388-7214
Email: [email protected]
-----Original Message-----
From: [email protected]
[mailto:[email protected]] 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/
