Retrieving Access Database Tables

[RSH]

I am struggling a bit trying to get at all of the Table names in a given
Access database.

I have the code below which should be retrieving the information...i am just
having a bit of trouble displaying the Table names out of the datatable.

Thanks for any assistance you might be able to offer!
Ron



private void browse_Click(object sender, EventArgs e)

{

OpenFileDialog openFileDialog1 = new OpenFileDialog();

openFileDialog1.InitialDirectory = "c:\\";

openFileDialog1.Filter = "Access Databases|*.mdb";

openFileDialog1.FilterIndex = 2;

openFileDialog1.RestoreDirectory = true;

if (openFileDialog1.ShowDialog() == DialogResult.OK)

{

String strFilename;

strFilename = openFileDialog1.FileName;

cnAccess = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data
Source=" + strFilename + ";Persist Security Info=False");

cnAccess.Open();

string[] restrictions1 = new string[] { null, null, null, "TABLE" };

System.Data.DataTable DataTable1 = cnAccess.GetSchema();

for (int i = 0; i < DataTable1.Rows.Count; i++)

{

checkedListBox1.Items.Add(DataTable1.Rows[0].Table.Columns[0].ToString());
<----- This is where I need to display the Table names

}

}

}

 

[Ben Dewey]

Last time i did this I found that using the Access Application gave my more
detailed information. the only problem is that the user/server will have to
have Office Access installed.

public ArrayList GetTableNames()
{
if (ConnectionString == null)
throw new InvalidOperationException("ConnectionString must be set before
trying to retrieve the Table Names.");
_fileName = GetFileNameFromConnectionString(ConnectionString);
if (_fileName == null)
throw new ArgumentException("Invalid Connection String.",
"ConnectionString");
ArrayList tableList = new ArrayList();
ApplicationClass AccessApp=null;
Database CurrentDb=null;
try
{
AccessApp = new ApplicationClass();
AccessApp.OpenCurrentDatabase(_fileName, false, null);
CurrentDb = (Database)AccessApp.CurrentDb();
foreach(TableDef table in CurrentDb.TableDefs)
{
if ( !table.Name.StartsWith("MSys") )
tableList.Add( new Table( table.Name, table.Fields.Count) );
}
return tableList;
}
finally
{
if (CurrentDb != null)
CurrentDb.Close();
if (AccessApp != null)
{
AccessApp.CloseCurrentDatabase();
AccessApp.DoCmd.Close(AcObjectType.acDefault, null, AcCloseSave.acSaveNo);
AccessApp.DoCmd.Quit(AcQuitOption.acQuitSaveNone);
AccessApp = null;
}
}
}

public ArrayList GetFieldNames(string tableName)
{
if (ConnectionString == null)
throw new InvalidOperationException("ConnectionString must be set before
trying to retrieve the Table Names.");
_fileName = GetFileNameFromConnectionString(ConnectionString);
if (_fileName == null)
throw new ArgumentException("Invalid Connection String.",
"ConnectionString");
ArrayList fieldList = new ArrayList();
ApplicationClass AccessApp=null;
Database CurrentDb=null;
TableDef table=null;
try
{
AccessApp = new ApplicationClass();
AccessApp.OpenCurrentDatabase(_fileName, false, null);
CurrentDb = (Database)AccessApp.CurrentDb();
table = CurrentDb.TableDefs[tableName];
foreach(dao.Field field in table.Fields)
{
fieldList.Add(new Field(tableName, field.Name, field.Type.ToString() ) );
}
return fieldList;
}
finally
{
if (table != null)
table = null;
if (CurrentDb != null)
CurrentDb.Close();
if (AccessApp != null)
{
AccessApp.CloseCurrentDatabase();
AccessApp.DoCmd.Close(AcObjectType.acDefault, null, AcCloseSave.acSaveNo);
AccessApp.DoCmd.Quit(AcQuitOption.acQuitSaveNone);
AccessApp = null;
}
}
}
private static string GetFileNameFromConnectionString(string
connectionString)
{
string connectionStringExpression = "Provider=(?'provider'.[^;]+);Data
Source=(?'file'.[^;]+);";
Match m = Regex.Match(connectionString, connectionStringExpression);
if (m.Success)
return m.Groups["file"].Value;
else
return null;
}

 

[Ben Dewey]

Here is the older script from that project uses MSysObjects, but you can't
get much info about the fields or the field types.

public ArrayList GetTableNames()
{
if (ConnectionString == null)
throw new InvalidOperationException("ConnectionString must be set before
trying to retrieve the Table Names.");
ArrayList tableList = new ArrayList();
OleDbCommand cmd = null;
try
{
if (conn == null)
conn = new OleDbConnection(ConnectionString);
conn.Open();
cmd = new OleDbCommand("SELECT Name, 0 as TotalColumns FROM MSysObjects
where Type = 1 AND NAME NOT IN
(\"MSysObjects\",\"MSysACEs\",\"MSysQueries\",\"MSysRelationships\",\"MSysAccessObjects\",\"MSysAccessXML\")",
conn);
using( OleDbDataReader dr = cmd.ExecuteReader() )
{
while (dr.Read())
{
tableList.Add( new Table( dr["Name"].ToString(),
long.Parse(dr["TotalColumns"].ToString()) ) );
}
}
return tableList;
}
finally
{
if (cmd != null)
{
cmd.Dispose();
}
if (conn != null)
{
if (conn.State == ConnectionState.Open)
conn.Close();
}
}
}