Toutes les réponses

  • This might be my ignorance, but as far as I am aware your best bet is to dig around in the GetSchema help files on MSDN.  When I have had to work with it, I recollect having to dig through numerous MSDN pages to find what values mean what.

    Wish I could help more.

    lundi 23 avril 2007 20:29
  • Yeah I've had a bit of a time trying to figure out all this Schema stuff.  Can you at least point me to the MSDN articles that you looked at to retrieve some info.  Perhaps that will point me in the right direction.

    lundi 23 avril 2007 20:55

  • I believe that the values returned in the COLUMN_FLAGS column are OLEDB provider specific. It's used in conjunction with the DATA_TYPE column to identify the exact data type. For example, you could have a DATA_TYPE value that identifies a column data type of INTEGER and a COLUMN_FLAG which further identifies the column as an IDENTITY or AUTOINCREMENT field. 

    he following article documents the mapping of SQL Server data types and the corresponding values of these two columns:

    Data Type Mapping with Distributed Queries 

    The following may help as well if you're working with an Excel file and the Jet OLEDB Provider:

    HOW TO: Retrieve Metadata from Excel by Using the GetOleDbSchemaTable Method in Visual Basic .NET


    mardi 24 avril 2007 12:24
  • The basic sql connection GetSchema page is here:

    Look at the bottom of the page under "Other Resources".  This is a good place to start digging.

    I would also check out this article and links within the text on this page:

    If all else fails you might have to discern it through debugging and trial and error.

    mardi 24 avril 2007 12:59
  • Well I have not really gotten anywhere.  But since I'm running Access 2003 I'll post what little data I've gleaned through trial and error.

    If your DATA_TYPE is 3 and your COLUMN_FLAGS is 90 then you have an auto-increment.  The next one I want to figure out is if it is a unique column.
    vendredi 4 mai 2007 18:44
  • I realise this is an old thread but as i've had probems finding info i thought i'd share what i've found.


    The OleDb.OleDbType enum is your best bet at converting the Type codes into .NET data types.

    Secondly for unique and primary key information are all obtained from  GetSchema("Indexes"...)


    In Access 2003 it seems that you are correct in 90 being the flag for AutoNumbers.

    jeudi 27 novembre 2008 08:37
  • According to this

    "For the COLUMNS schema rowset, the DATA_TYPE and COLUMN_FLAGS columns represent the DBTYPE and DBCOLUMNFLAGS values. For the IColumnsInfo::GetColumnInfo interface, the wType and dwFlags members of the DBCOLUMNINFO structure represent these values."

    Which leads me to find DBCOLUMNFLAGS Enumerated Type on this page

    little hunting and I find the enums listed here


    now for the integer value it will be formed by a bit mask with the enums each representing another bit of the number. Easiest is not to mess with that, but rather check the value returned against your own bit mask formed by the enum above. If you don't know how to do that... well... you might want to post on a programming forum
    • Proposé comme réponse jZe lundi 13 avril 2009 19:15
    lundi 13 avril 2009 19:02
  • using that enum your mask is just the sum over 2 ^ DBCOLUMNFLAGS_? 

    mercredi 9 février 2011 19:21
  • Have some similar problems. I can't identify AutoNumber field in my Access Table.

    It does seem that 90 being the flag for AutoNumbers. But it's not usable. I mean AutoNumber does have COLUMN_FLAGS 90 and DATA_TYPE 3, but so do other fields.

    Has anyone solved this issue?

    vendredi 29 juin 2012 10:34
  • Found the way. Feel stupid now).

    If U use adapters FillSchema() method it will do it for U. I actually don't understant why it doesn't fill information about schema in DataTable when you "really" fill it with Fill() method. wasted a lot of time trying to detect autonumbers in access. anyhow here method i wrote, hope it'll save somebody time:

    public string GetAutonumberByTableName(string mdbTableName)
                if (String.IsNullOrWhiteSpace(mdbTableName))
                    return null;
                if (this.dbConnection.State == ConnectionState.Closed)
                string res = null;
                    string statement = string.Format("SELECT * FROM [{0}]", mdbTableName);                
                    OleDbCommand command = new OleDbCommand(statement, this.dbConnection);
                    OleDbDataAdapter adapter = new OleDbDataAdapter(command);
                    DataTable schema = new DataTable();
                    adapter.FillSchema(schema, SchemaType.Source);
                    int found = 0; // the counter of autonumber columns
                    foreach (DataColumn col in schema.Columns)
                        if (col.AutoIncrement)
                            if ((++found) > 1)
                                throw new Exception("Cannot detect correctly autonumber column in "+mdbTableName+" table");
                            res = col.ColumnName;                        
                catch (Exception e)
                    // do some work with your Exception. I generated an event                  
                return res;


    mercredi 18 juillet 2012 15:17
  • Very nice, Anthony. Even AccessDataSource does not correctly reflect AutoNumber/Unique (PrimaryKey) on Access 2003 mdb. You have saved me many hours of frustration with this. Very strange behavior, in my opinion...but now that I know how to get the correct column attributes, it will help me down the road. Thanks again. Here is my VB.NET version of your nice code block....

        Private Function GetAccessDataTableSchema(ByVal theConnectionString As String, _
                                                  ByVal theTable As String)
                Dim theConnection As New OleDbConnection(theConnectionString)
                Dim theSelect As String = String.Format("SELECT * FROM [{0}]", theTableName)
                Dim theOleDBCommand As New OleDbCommand(theSelect, theConnection)
                Dim theAdapter As New OleDbDataAdapter(theOleDBCommand)
                theSchemaTable = New DataTable
                theAdapter.FillSchema(theSchemaTable, SchemaType.Source)
                With DataGridView1
                    .DataSource = theSchemaTable
                    .Visible = True
                End With
                Return True
            Catch ex As Exception
                MessageBox.Show(ex.Message & vbCrLf & ex.StackTrace, _
                                "jinzai studio CSV Importer", _
                                MessageBoxButtons.OK, _
            End Try
            Return False
        End Function

    • Modifié jinzai_studio vendredi 21 septembre 2012 05:21 add code block
    vendredi 21 septembre 2012 05:19
  • Thanks, this info was missing!
    dimanche 1 mars 2015 17:13