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
http://I.Am/DougSteele
(no private e-mails, please)


"EagleOne@microsoftdiscussiongroups"
<EagleOnemicrosoftdiscussiongroups@discussions.mic rosoft.com> wrote in
message news:AD0D31C8-1235-4032-B1E0-DC81FBF55855@microsoft.com...
> 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

On Mon, 11 Aug 2008 11:09:01 -0700, EagleOne@microsoftdiscussiongroups
<EagleOnemicrosoftdiscussiongroups@discussions.mic rosoft.com> wrote:

>(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?)

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.
--

John W. Vinson [MVP]

"EagleOne@microsoftdiscussiongroups"
<EagleOnemicrosoftdiscussiongroups@discussions.mic rosoft.com> wrote in
message news:AD0D31C8-1235-4032-B1E0-DC81FBF55855@microsoft.com...
> 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?)

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

On Mon, 11 Aug 2008 21:11:07 -0500, "Larry Linson" <bouncer@localhost.not>
wrote:

>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.

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!"
--

John W. Vinson [MVP]

Herein lies the problem. Your special requirements are equivalent to a
driver pulling into a gas station and explaining that he can't fill up his
tank until the gas pumps are moved because his gas cap is on the other side
of his car.

Security is an advanced topic that even long time experts have difficulty
with, so as a newcomer expect to have misunderstandings and difficulties
yourself.

Please explain why you think you can't use a select query with the columns in
the desired order and why you think redefining the column order in the tables
will overcome that problem.

Chris
Microsoft MVP


EagleOne@microsoftdiscussiongroups wrote:

>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.

--
Message posted via http://www.accessmonster.com

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!

"Chris O'C via AccessMonster.com" wrote:

> Herein lies the problem. Your special requirements are equivalent to a
> driver pulling into a gas station and explaining that he can't fill up his
> tank until the gas pumps are moved because his gas cap is on the other side
> of his car.
>
> Security is an advanced topic that even long time experts have difficulty
> with, so as a newcomer expect to have misunderstandings and difficulties
> yourself.
>
> Please explain why you think you can't use a select query with the columns in
> the desired order and why you think redefining the column order in the tables
> will overcome that problem.
>
> Chris
> Microsoft MVP
>
>
> EagleOne@microsoftdiscussiongroups wrote:
>
> >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.
>
> --
> Message posted via http://www.accessmonster.com
>
>

TransferSpreadsheet is easy and works great with a saved query name. You
haven't explained why you won't use a saved query. I understand you're using
VBA for the DDL (Data Definition Language for creating and changing table
structures) to hide what you're doing to the tables. I just don't understand
why you can't save a select query object in the db to use with
TransferSpreadsheet to avoid all that DDL work.

Chris
Microsoft MVP


EagleOne@microsoftdiscussiongroups wrote:

>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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

On Wed, 13 Aug 2008 07:10:01 -0700, EagleOne@microsoftdiscussiongroups
<EagleOnemicrosoftdiscussiongroups@discussions.mic rosoft.com> wrote:

>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.

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.

--

John W. Vinson [MVP]