Dynamic change of field name in Export Specification

[Ray]

Hello all,
here is my problem:

i have created a macro to automate a monthly data pull,
create a crosstab query, and export to a text file. I have
also created an export specification for the text
transfer. The exported file contains 2 field headers,
field1 header never changes, but each month field2 changes
(it is a year/month field
header: '2003/11', '2003/12', '2004/1', etc.)

the specification that i set up expects 2 fields headers,
but since each month the header changes, the spec will not
work with this dynamically changing field header.

i believe what i have to do is this (but i am not sure how
to do it):

have the macro (or VB code) dynamically create and delete
a new export spec each month, or

somehow change the name of the field in the
MsysIMEXcolumns table, or

create a query where the header is dynamically named after
the current year and month of the data pull

any suggestions? any help is greatly appreciated

Ray

 

[John Nurick]

Hi Ray,

I must be missing something. Are you exporting the crosstab query or
another query?

Editing the system tables is not usually recommended because they are
undocumented. I suppose that what you want could be done just by running
a query to update the one record you're interested in, but I don't know
what the side-effects might be.

Any safe solution will require VBA rather than a macro. For instance,
write code that generates a schema.ini file
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp)
each time with the field names you want.

But if you're doing that you might as well use VBA to (a) create a new
text file, (b) write the headers directly to a text file, (c) open a
recordset on the query you want to export and (d) iterate through the
recordset formatting each record as a string and writing it to the
output file.

 

[Guest]

John,

thanks for replying, yes i am trying to export a crosstab
query to a txt file every month, and one field header
changes depending on the month and year. I thought i could
do it with a macro and and export spec, but because the
field header changes monthly i cannot reuse the spec.

i realize it is dangerous to mess with the system files,
but i thought if i can just change the one field in the
IMEX file i can reuse the spec and be able to use the
macro,

i think you are right though, i will have to use VBA to
automate either a shema file, or use your suggestion for
using code to write the query to a txt file

i am not too familiar with VBA, but I guess it is time to
learn! thanks for your help!

ray

-----Original Message-----
Hi Ray,

I must be missing something. Are you exporting the crosstab query or
another query?

Editing the system tables is not usually recommended because they are
undocumented. I suppose that what you want could be done just by running
a query to update the one record you're interested in, but I don't know
what the side-effects might be.

Any safe solution will require VBA rather than a macro. For instance,
write code that generates a schema.ini file
(http://msdn.microsoft.com/library/default.asp? url=/library/en-us/odbc/htm/odbcjetsdk_98.asp)
each time with the field names you want.

But if you're doing that you might as well use VBA to (a) create a new
text file, (b) write the headers directly to a text file, (c) open a
recordset on the query you want to export and (d) iterate through the
recordset formatting each record as a string and writing it to the
output file.

Hello all,
here is my problem:

i have created a macro to automate a monthly data pull,
create a crosstab query, and export to a text file. I have
also created an export specification for the text
transfer. The exported file contains 2 field headers,
field1 header never changes, but each month field2 changes
(it is a year/month field
header: '2003/11', '2003/12', '2004/1', etc.)

the specification that i set up expects 2 fields headers,
but since each month the header changes, the spec will not
work with this dynamically changing field header.

i believe what i have to do is this (but i am not sure how
to do it):

have the macro (or VB code) dynamically create and delete
a new export spec each month, or

somehow change the name of the field in the
MsysIMEXcolumns table, or

create a query where the header is dynamically named after
the current year and month of the data pull

any suggestions? any help is greatly appreciated

Ray

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.