How to determine MS Access field size via OleDb

Question

The existing application is in C#. During startup the application calls a virtual method to make changes to the database (for example a new revision may need to calculate a new field or something). An open OleDb connection is passed into the method.

I need to change a field width. The ALTER TABLE statement is working fine. But I would like to avoid executing the ALTER TABLE statement if the field is already the appropriate size. Is there a way to determine the size of an MS Access field using the same OleDb connection?

Answer 1

Not sure if I understand your question completely.
But you could query the table for 0 rows (SELECT 1 FROM myTable WHERE 1= 0)

And you could use recordet's field collection, refer to that field by name or index and use field's property like size, type etc.

Does that help?

Answer 2

It is possible to use schemas. Here are a few notes.

Sub ListFieldDescriptions()

Dim cn As New ADODB.Connection
Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection

    Set rs = cn.OpenSchema(adSchemaColumns, _
            Array(Empty, Empty, "TableNameHere"))
    While Not rs.EOF
        Debug.Print "     " & rs!Column_Name
        Debug.Print "     " & rs!CHARACTER_MAXIMUM_LENGTH
        Debug.Print "     " & rs!Data_Type

        rs.MoveNext
    Wend

    Set cn = Nothing

End Sub

Note: adSchemaColumns=4, Data_Type adWChar=130

Based on: http://wiki.lessthandot.com/index.php/ADO_Schemas

Answer 3

Here's what I came up with based on shahkalpesh's answer:

var command = new OleDbCommand("SELECT FIELD FROM TABLE", connection); 
var reader = command.ExecuteReader(CommandBehavior.SchemaOnly); 
var schema = reader.GetSchemaTable(); 
var size = Convert.ToInt32(table.Rows[0]["ColumnSize"]);

Answer 4

Andy's works better for me... have to use OleDB for my implementation. And his is only grabbing schema which is much cleaner.