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.
Now, edit the query and add a column. Reposition the column so that it’s the second column.
Now the query looks like this
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.
Now refresh, and your columns are ordered how you specified.
In VBA, use the PreserveColumnInfo property of the QueryTable object.
This does not appear to work for Import Text option. Preserve Column Sort/Filter/Layout is not available, sadly.
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)
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.
“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.