c# - how to go through ms access db and get list of tables?

Question

Im copying data from a ms access db to sql server like this...

string sSQLTable = table;
string myExcelDataQuery = "Select * from " + sSQLTable;
string sSqlConnectionString = connStr;
string sClearSQL = "DELETE FROM " + sSQLTable;
SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
SqlConn.Close();
OleDbConnection OleDbConn = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", fileName));
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
bulkCopy.DestinationTableName = sSQLTable;
while (dr.Read())
{
    bulkCopy.WriteToServer(dr);
}
OleDbConn.Close();

But this only does it for one table name...how do i put this in a loop and get each table name and call this function to copy data for each table?

Darin Dimitrov · Accepted Answer · 2012-02-23 10:10:20Z

1

You may take a look at the following article which illustrates how to retrieve a list of available tables by querying the schema.

answered Feb 23, 2012 at 10:10

Darin Dimitrov

1.0m276 gold badges3.3k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ravi Vanapalli · Accepted Answer · 2012-02-23 10:22:52Z

0

You can run below query but need to have Administrative priviledge.

SELECT Name FROM MSysObjects WHERE Left([Name],1)<>"~" AND Left([Name],4)<>"MSys" AND Type In (1,4,6) ORDER BY Name

answered Feb 23, 2012 at 10:22

Ravi Vanapalli

9,9703 gold badges36 silver badges43 bronze badges

Collectives™ on Stack Overflow

c# - how to go through ms access db and get list of tables?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related