How can I get the field definitions from an AccessDataSource?

I have an MS Access database (mdb) file and I am writing an Asp.Net application (in C#) that I use to access and modify the data in the Access database.

I would like to be able to interrogate, through my Asp.Net application, the field (column) definitions of tables in the database, so that I can check that the correct fields exist in the database, and that they are the correct data type.

I seem to remember that I can use some kind of SQL SELECT statement to query the field (column) definitions of a table. Is this right? If so, can you please give me an example of such a SQL statement please?
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
And ADO Recordset will return a Fields collection, which give you basic information such as Name, Type etc. I'm not sure you'd bet the same info back from the AccessDataSource, however ... you could try it and see. With an ADO recordset:

Dim rst As ADODB.Recordset
Dim i As integer

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable WHERE 1=0", YourConnection

For i = 0 to rst.Fields.Count - 1
  Debug.Print  rst.fields(i).Name
Next i

I realize you aren't returning an ADODB recordset, but perhaps this will help you somewhat.
pivarConnect With a Mentor Commented:
JimR123bAuthor Commented:
I'd like to do this using my existing AccessDataSource if it's possible.
JimR123bAuthor Commented:
I've worked out a full answer using a combination of your answers...
        public DataColumnCollection GetColumns()
            List<string> Result = new List<string>();
            AccessDataSource DataSource = CreateDataSource();
            DataSource.SelectCommand = "SELECT * FROM " + TableName() + " WHERE 1=0";
            DataView DataView = (DataView)(DataSource.Select(DataSourceSelectArguments.Empty));
            return DataView.Table.Columns;

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.