storing database table names list after "odbc query <my dsn>, dialogue(complete)

[chifundo kanjala]

I would like to store the names of the tables in an access database that I am accessing in Stata through odbc. I want a work around that does not require granting permissions to mysystables to Admin in access. I am thinking since stata lists the table names, there is a way to storew the list of table name. any pointers?

[Joseph Coveney]

The names of the tables are automatically stored by Stata in an array member variable of a Stata ADO object called __ODBC_INFO, which is created by the odbc command. (Note that this is not a Mata object—Stata has two object-oriented programming languages built into it.) You can retrieve the names of database tables from the __ODBC_INFO object using syntax that is appropriate for Stata ADO object-oriented programming (see help class).

The example below shows how to list the names of a Microsoft Access database's tables and how to store the names of the tables in a Stata dataset. Start at the "Begin here" comment.

.ÿversionÿ14.2

.ÿ
.ÿclearÿ*

.ÿsetÿmoreÿoff

.ÿ
.ÿ//ÿGetÿsampleÿMicrosoftÿAccessÿdatabaseÿfile
.ÿcopyÿhttps://www.dur.ac.uk/resources/cis/comms/ASampleDatabase.accdbÿF:\ASampleDatabase.accdb

.ÿ
.ÿ//ÿAddÿaÿcoupleÿofÿnewÿtablesÿforÿuseÿinÿillustration
.ÿlocalÿdsnÿMSÿAccessÿDatabase;DBQ=ASampleDatabase.accdb;DefaultDir=F:\;

.ÿforvaluesÿiÿ=ÿ1/2ÿ{
ÿÿ2.ÿÿÿÿÿÿÿÿÿodbcÿexec("CREATEÿTABLEÿDummy`i'ÿ(my_keyÿCHAR(6)ÿNOTÿNULLÿPRIMARYÿKEY,ÿmy_dataÿINTÿNOTÿNULL);"),ÿdsn("`dsn'")
ÿÿ3.ÿ}



.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿ//ÿObtainÿtheÿnamesÿofÿtheÿtablesÿinÿtheÿdatabase
.ÿodbcÿqueryÿ"`dsn'"

DataSource:ÿMSÿAccessÿDatabase;DBQ=ASampleDatabase.accdb;DefaultDir=F:\;
Pathÿÿÿÿÿÿ:ÿF:\ASampleDatabase.accdb
-------------------------------------------------------------------------------
AssetÿItems
Dummy1
Dummy2
-------------------------------------------------------------------------------

.ÿ
.ÿ//ÿSeeÿtheÿStataÿobjectÿwhereÿtheÿtableÿnamesÿetc.ÿareÿstored
.ÿclassutilÿdir
.__ODBC_INFO

.ÿ
.ÿ//ÿSeeÿtheÿmemberÿofÿobjectÿwhereÿtheÿtableÿnamesÿareÿstored
.ÿclassutilÿdescribeÿ__ODBC_INFO

odbc__infoÿ.__ODBC_INFO:
ÿÿÿÿÿÿÿÿarrayÿÿÿÿÿÿÿÿ.TABLEÿÿÿÿÿÿÿÿÿÿÿ(.arrnels=3)
ÿÿÿÿÿÿÿÿarrayÿÿÿÿÿÿÿÿ.VARIABLESÿÿÿÿÿÿÿ(.arrnels=0)
ÿÿÿ.Declare:
ÿÿÿÿÿÿÿÿstringÿÿÿÿÿÿÿ.ODBC_ERR_MSGÿÿ=ÿ""

.ÿ
.ÿ//ÿGetÿtheÿnumberÿofÿtablesÿwhoseÿnamesÿareÿstored
.ÿlocalÿtable_tallyÿ`.__ODBC_INFO.TABLE.arrnels'

.ÿ
.ÿ//ÿListÿtheÿstoredÿtableÿnames
.ÿforvaluesÿiÿ=ÿ1/`table_tally'ÿ{
ÿÿ2.ÿÿÿÿÿÿÿÿÿlocalÿtable_nameÿ`.__ODBC_INFO.TABLE[`i']'
ÿÿ3.ÿÿÿÿÿÿÿÿÿdisplayÿinÿsmclÿasÿtextÿ"`table_name'"
ÿÿ4.ÿ}
AssetÿItems
Dummy1
Dummy2

.ÿ
.ÿ//ÿPutÿtheÿtableÿnamesÿinÿaÿvariableÿofÿaÿStataÿdataset
.ÿquietlyÿsetÿobsÿ`table_tally'

.ÿquietlyÿgenerateÿstrÿtable_nameÿ=ÿ""

.ÿforvaluesÿiÿ=ÿ1/`table_tally'ÿ{
ÿÿ2.ÿÿÿÿÿÿÿÿÿquietlyÿreplaceÿtable_nameÿ=ÿ"`.__ODBC_INFO.TABLE[`i']'"ÿinÿ`i'
ÿÿ3.ÿ}

.ÿ
.ÿlist,ÿnoobs

ÿÿ+-------------+
ÿÿ|ÿÿtable_nameÿ|
ÿÿ|-------------|
ÿÿ|ÿAssetÿItemsÿ|
ÿÿ|ÿÿÿÿÿÿDummy1ÿ|
ÿÿ|ÿÿÿÿÿÿDummy2ÿ|
ÿÿ+-------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

[chifundo kanjala]

Many thanks Joseph Coveney, I am sorted