SQL statement ?

[brraj]

hi there,

I want to get all the table names in MS access.
So what will be SQL statement ?

thanks ahead
rajeev

[olin]

Unfortunately, It seems no SQL statement can do this work. I am also looking for some time
So, my way is to create a root table first containing all the table names in this database. then you can access any table by read this table.

[broesli]

Do you use DAO ?

if yes, this snippet of code did the trick for me:

database.Open("dbase.mdb");

int iRecords = 0;
int iTotTables = database.GetTableDefCount();
CDaoTableDefInfo tInfo;
CDaoTableDef tb(&database);

int iCount = 0;
for (int n = 0; n < iTotTables; n++)
{
database.GetTableDefInfo(n, tInfo);
LONG attr = tInfo.m_lAttributes;

tb.Open(tInfo.m_strName);
LONG CheckSysObj = tb.GetAttributes();

if((CheckSysObj & dbSystemObject) == 0 && (attr & dbHiddenObject) != dbHiddenObject)
{
try
{
iRecords = tb.GetRecordCount();
//display tablenames
AfxMessageBox(tInfo.m_strName);
}
catch(CDaoException &e)
{
e.ReportError();
}
}
tb.Close();
}
database.Close();

[brraj]

hi broesli ,

Your code works just fine. thank you very much.

regards
rajeev

[bigBA]

or if you use ADO:

you can get a recordset with information about all tables with the OpenSchema Method of the Connection object

read this:
http://msdn.microsoft.com/library/de...openschema.asp

there are also examples for vc++, vj++ and vb

[broesli]

no problem,

glad I could help!