C# Finding all tables in access file

I recently found a need to access table names found in an access file dynamically. I found some code on the net to do this. But I discovered a bug, the code I was using would only read static tables. The files I was getting could contain linked and pass-through tables. So after some more searching I found the best way was to get all the tables (system, access) etc and then filter out only what I needed. So after some coding here is the solution. This is sitting in a function


public static DataTable GetTableNames( string accessFile )
{

OleDbConnection con = null;

try
{
// create a connection string
String connect = String.Format( "Provider=Microsoft.JET.OLEDB.4.0;data source={0}", accessFile );

// open the connection
con = new OleDbConnection( connect );
con.Open();

// get all the tables in the file, system, etc
DataTable dtAllTables = con.GetOleDbSchemaTable( OleDbSchemaGuid.Tables,
new object[] { null, null, null, null } );

DataTable filterTable = dtAllTables.Clone();

// filter all out everything but normal, link, and passthrough tables
DataRow[] filter = dtAllTables.Select( "TABLE_TYPE IN('TABLE','LINK','PASS_THROUGH')" );

// use the datarow array to build a new table of just our desired tables
foreach( DataRow row in filter )
{
filterTable.ImportRow( row );
}

return filterTable;

}//end try
catch( Exception ex )
{
Logger.Write( ex.ToString() );
return null;
}
finally
{
if( con != null )
{
con.Close();
}
}
}

Comments

Popular Posts