Add New Column to External Data

Adding a column to an existing external data query may not give you the results you want. There’s a property of the external data query that freezes the layout. Here’s how it works: Start with a query. This one is from the Northwind database.

EDLayout1

Now, edit the query and add a column. Reposition the column so that it’s the second column.

EDLayout2

Now the query looks like this

EDLayout3

The newly added column shows last, even though we explicitly placed it second. To fix this problem, click the Data Range Properties button on the External Data Toolbar. On the External Data Range Properties dialog, uncheck the Preserve Column Sort/Filter/Layout check box.

EDLayout4

Now refresh, and your columns are ordered how you specified.

EDLayout5

In VBA, use the PreserveColumnInfo property of the QueryTable object.

6 thoughts on “Add New Column to External Data

  1. This does not appear to work for Import Text option. Preserve Column Sort/Filter/Layout is not available, sadly.

  2. hai
    i have a minor doubt
    that is

    I am doing a small project in VB.6 with Ms Access
    but my point is how to add a column to table(by using query in vb6)

  3. Sandhya: If I understand your problem correctly, you want to modify the database schema. You might want to check with an Access programming newsgroup as to how one does that rather than asking in Dick’s XL-focused blog.

    To query a db schema one would start with the OpenSchema method of a ADO connection. Whether updating the schema uses the same start point is something you will have to investigate yourself.

  4. “how to add a column to table(by using query in vb6)”

    Execute SQL DDL against an ADO Connection e.g.

    Conn.Execute _
    “ALTER TABLE MyTable” & _
    ” ADD MyNewColumn VARCHAR(255)” & _
    ” DEFAULT ‘{{NK}}’ NOT NULL;”

    Jamie.

    –

  5. Thanks for this info, I was looking for this info for a time, I recreated files and connection because of this, now I can insert new columns and my other file till returns the column position in the right order.

    Ervin

  6. Thanks, it worked flawlessly!

    It was a big problem because when I added a column to external data file (somewhere in the middle of the table), the column appeared at the very end of table in the file that is sourcing external data. After unchecking the preserve column sort/filter/layout and refreshing the file, it works as it should. Thank you.

    Andy


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.