• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

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?
3 Solutions
JimR123bAuthor Commented:
I'd like to do this using my existing AccessDataSource if it's possible.
Scott McDaniel (Microsoft Access MVP - EE MVE )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.
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


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now