How to re-order columns Programatically. Possible?

  • Thread starter Thread starter EagleOne@microsoftdiscussiongroups
  • Start date Start date
E

EagleOne@microsoftdiscussiongroups

2003

Is there a way in VBA (SQL) to re-order columns?

The mainframe data that I receive for two different data sources comes with
preset columns. I have a Schema.ini file that configures the OpenFile read
loop which populates the data into the two respective data tables

Once captured, I process the data using strictly VBA (SQL) ALTER TABLE .....
and
dBs.Execute "Update ........;" statements.

Purposly I do not use the typical Access Queries for security purposes.

Therefore, I do not have access to the Move Column capability built in to
the Query Results View Datasheet View.

Except for the original import process mentioned above, the only other
avenue that I can think of is the order in which SELECT the fields in the VBA
(SQL) Select statement.

Is there any other after-the-fact VBA (SQL) procedures which would re-order
the columns? Or any other thoughts?

(In the DB2 world I have the capability of setting column order on printouts
or saved files via QMF queries. Anything like that in the Access world?)

TIA

EagleOne
 
Relying on the order of the columns isn't usually a good idea.

Using INSERT INTO Table1 (Field1, Field2, Field3) SELECT Field3, Field1,
Field2 FROM Table2 would be my recommendation.

Of course, having to add fields dynamically to tables usually is a sign of
incomplete design.


oug Steele, Microsoft Access MVP

(no private e-mails, please)


"EagleOne@microsoftdiscussiongroups"
 
(In the DB2 world I have the capability of setting column order on printouts
or saved files via QMF queries. Anything like that in the Access world?)
Click to expand...

Yes. Use a Report based on a Query to print data. It's not at all clear what
"security" issue you're avoiding by not using Queries, which are the
fundamental essential element of any Access application.

Tables are NOT designed or intended for data presentation or printing, and the
order of fields in a table is (or should be) completely irrelevant.
 
"EagleOne@microsoftdiscussiongroups"
2003

Is there a way in VBA (SQL) to re-order columns?

The mainframe data that I receive for two different data sources comes
with
preset columns. I have a Schema.ini file that configures the OpenFile
read
loop which populates the data into the two respective data tables

Once captured, I process the data using strictly VBA (SQL) ALTER TABLE
.....
and
dBs.Execute "Update ........;" statements.

Purposly I do not use the typical Access Queries for security purposes.

Therefore, I do not have access to the Move Column capability built in to
the Query Results View Datasheet View.

Except for the original import process mentioned above, the only other
avenue that I can think of is the order in which SELECT the fields in the
VBA
(SQL) Select statement.

Is there any other after-the-fact VBA (SQL) procedures which would
re-order
the columns? Or any other thoughts?

(In the DB2 world I have the capability of setting column order on
printouts
or saved files via QMF queries. Anything like that in the Access world?)
Click to expand...

This concerns me... it rings of someone who wants their new tool to BE their
old tool, not just be "like" it or "similar". I see that all too often, and
it _invariably_ leads to frustration, stress, and strife. Others have
covered all these points, I think, but let me summarize:

1. Datasheet view (Table or Query) is not appropriate for user viewing
or manipulation of data in a database application. Data should be
presented for users to view either in an un-updateable Form or Report.
2. The order of columns in a Table is, or should be, immaterial. In the
case of non-application end-user use, Columns can be rearranged in
the Query Builder using Queries. You may have an exception if the
data you receive from two sources with different order is, in fact,
the same information and you wish to use a UNION Query to put
the information together. You can, of course, use an Append Query
(if need be, with another Query as its data source) to append data
from one Table formed from an imported source to the Table formed
from the other imported source, too.
3. In 50 years in the computer business, I have never had occasion to
need to alter design at runtime, and it would seriously concern me
to use an application in my business which did so.
4. If you mean that you don't allow your users direct access to Queries
to produce Datasheet View, then good for you... in that scenario,
there are "things that could go wrong". If you believe there would be
security problems using Queries as the RecordSource for a Form or
Report, I'd like to hear what those concerns might be. (But your
comment about using db.Execute "UPDATE..." means that you are
using Queries... Queries are SQL, just stored as an Access Object.
5. Yes, you can SELECT the Fields in any order you desire. However,
unless you are viewing the results as Datasheet View, that order just
simply DOES NOT MATTER.

Sometimes we can help when people tell us what they have and what they want
to accomplish -- many times, someone can suggest a useful approach to
accomplishing their purpose, based on deep understanding of the tools and
long experience in creating solutions with this and other tools. It is
often almost impossible to help when all they tell us is _how_ they intend
to accomplish something and that it is not working.

Larry Linson
Microsoft Office Access MVP
 
This concerns me... it rings of someone who wants their new tool to BE their
old tool, not just be "like" it or "similar". I see that all too often, and
it _invariably_ leads to frustration, stress, and strife.
Click to expand...

My phrase for that situation is "Access is not a flawed implementation of <x>,
it is a database development environment in its own right. Work with it, don't
struggle against it!"
 
For all those who have contributed thoughts, I read and appreciate them.

The tables involved will be exported into Excel. In the instantance
application, Access is just a temporary container of data exclusive of a Join
of the two tables to ascertain the unmatched (TableA to TableB) then (TableB
to TableA).

I have no control over the incoming data.

But the format of the outputed data (back into Excel as most of our users do
their subsequent analyses in same) I do control.

I have not found a VBA (SQL) way to export the results of a query via
TransferSpreadsheet or some other command to an Excel w/s. Therefore, I am
working with Table information and updating same with update queries and
Alter Table commands. Then I export the table as updated. The only open
issue is moving the field-columns to sync up TableB columns to TableA columns.

For sure, if I knew of a way to export query results via VBA (SQL) , then I
would run a query on the table which would synchronize the columns (fields)
in both TableA and TableB.

Doug Steel's thought of (will work in this instance):
"Using INSERT INTO Table1 (Field1, Field2, Field3) SELECT Field3, Field1,
Field2 FROM Table2 would be my recommendation"

A) I really would like to export the Query results via VBA (SQL) if available.

B) Doug, I appreciate you giving me an approach which will work; then also
your concerns as to why (in-the-hell) I am doing it that way!

If I new how to do Plan A I would not even consider Plan B.

Thanks to all!
 
I have not found a VBA (SQL) way to export the results of a query via
TransferSpreadsheet or some other command to an Excel w/s.
Click to expand...

From the Help for TransferSpreadsheet (emphasis added):

The TransferSpreadsheet method carries out the TransferSpreadsheet action in
Visual Basic.

expression.TransferSpreadsheet(TransferType, SpreadsheetType, TableName,
FileName, HasFieldNames, Range, UseOA)
expression Required. An expression that returns one of the objects in the
Applies To list.

TransferType Optional AcDataTransferType.

AcDataTransferType can be one of these AcDataTransferType constants.
acExport
acImport default
acLink
If you leave this argument blank, the default constant (acImport) is assumed.


SpreadsheetType Optional AcSpreadSheetType.

AcSpreadSheetType can be one of these AcSpreadSheetType constants.
<snip>

TableName Optional Variant. A string expression that's the name of the
Microsoft Access table you want to import spreadsheet data into, export
spreadsheet data from, or link spreadsheet data to, ****or the Microsoft
Access select query**** whose results you want to export to a spreadsheet.
 
Back
Top