How can I get the field definitions from an AccessDataSource?

Posted on 2009-02-20
Last Modified: 2012-05-06
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?
Question by:JimR123b
    LVL 53

    Assisted Solution

    LVL 22

    Assisted Solution


    Author Comment

    I'd like to do this using my existing AccessDataSource if it's possible.
    LVL 84

    Accepted Solution

    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.

    Author Comment

    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

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now