Member Avatar for ashokrajendran

Hi all,

Im new to c#, please any one tell me how to get the column names of a table of ms access(.mdb). Here i used odbc connectivity to access the database, i got the list of tables in the database, but i dont know how to get the column name of a tables. Can any one give me a solution for the Query.

Thanks in Advance,
AshokRajendran :)

  • 3 Contributors
  • 6 Replies
  • 2K Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by lonelyrider

Dani AI

Generated

, ’s idea works, but you don’t need to pull any rows just to learn the column names. Ask the driver for metadata instead. ODBC exposes a schema view that returns column info (names, types, nullability) for any table. This is faster and avoids MSysObjects entirely. See the ODBC GetSchema docs and the ODBC Columns schema definition. (learn.microsoft.com)

Example with ODBC (notice the table restriction in slot 3 and reading COLUMN_NAME). Sort by ORDINAL_POSITION if you want the physical order:

using (var cn = new OdbcConnection(connectionString))
{
    cn.Open();
    var restrictions = new string[] { null, null, "YourTableName", null };
    DataTable cols = cn.GetSchema("Columns", restrictions);
    foreach (DataRow r in cols.Select("", "ORDINAL_POSITION"))
        Console.WriteLine((string)r["COLUMN_NAME"]);
}

Tip: for Access tables with spaces or reserved words, keep the brackets when you later query data (e.g., SELECT ... FROM [Order Details]). If you prefer discovering columns from a reader, execute a no-data query and read the schema only, e.g., ExecuteReader(CommandBehavior.SchemaOnly) followed by GetSchemaTable(); this returns column metadata without scanning rows. (learn.microsoft.com)

On your MSysObjects question: by default you will see a “no read permission” error when you try to query it via ODBC. Rather than touching system tables, stick with GetSchema("Tables")/GetSchema("Columns"). If you truly must read MSysObjects, open the .mdb in Access and grant read permission, for example: GRANT SELECT ON MSysObjects TO Admin; (run from an Access session using CurrentProject.Connection.Execute). Microsoft documents the GRANT statement syntax; HansUp’s post explains why running it from Access (not ODBC) is often required. (learn.microsoft.com)

Bonus for : favor name-based access over ordinals; column order changes, names do not.

Recommended Answers

All 6 Replies

Member Avatar for kvprajapati

Hi all,

Im new to c#, please any one tell me how to get the column names of a table of ms access(.mdb). Here i used odbc connectivity to access the database, i got the list of tables in the database, but i dont know how to get the column name of a tables. Can any one give me a solution for the Query.

Thanks in Advance,
AshokRajendran :)

...
...
OdbcConnection cn=new OdbcConnection("your connection string");
OdbcCommand cmd=new OdbcCommand("select * from tablename",cn);

cn.Open();
OdbcDataReader dr;
dr=cmd.ExecuteReader()

'First column name
string first=dr.GetName(0);

'Second column name
string second=dr.GetName(1);
...
...
dr.Close();
cn.Close();
.....

Another ways is:

...
...
OdbcConnection cn=new OdbcConnection("your connection string");
OdbcDataAdapter adp=new OdbcDataAdapter("select * from tablename",cn);
DataTable dt=new DataTable();
adp.Fill(dt);

// first column name
string first = dt.Columns[0].ColumnName;
...
..
 ....
Member Avatar for ashokrajendran

Thank you very much adatapost

Can you help me in one more issues...
I'm using C#.Net...

How to access the msysobjects table of a .MDB file.. I'm Getting the error as unable to read.. Read permission Problem..

Kindly help asap.., :)

Member Avatar for kvprajapati

I am sorry!!!. Now, you can see I have changed.

Member Avatar for ashokrajendran

alright adatapost...
i got the logic...
my another issue is...
How to access the msysobjects table of a .MDB file.. I'm Getting the error as unable to read.. Read permission Problem.. Access denied problem...

Thanks
AshokRajendran

Member Avatar for kvprajapati

hey ashok, you play a game. You said that ---

Here i used odbc connectivity to access the database, i got the list of tables in the database.

Member Avatar for lonelyrider

OH great way of doing that i didnt know that too. Thats why i always used dr[0] based indexing instead of the names. Thanks for putting this question it helped me a lot.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.