Trying to export flat file

[Christine Wolak]

I am trying to export a flat file of data, from a query. I've already done
the hard part - the query produces a single column of data that is the entire
formatted line for the file. But I'm having trouble getting Export to work.
The help text talks about an Advanced button, which isn't on the Export
dialog anywhere. I found a post that talked about building the specification
using Import (where the Advanced button DOES exist) and then using it on
export. I tried that, but I still get the same error as I was getting when I
tried to export as a delimited file, namely "Operation is not supported for
this type of object". My query is a pass-through query - is that why this
isn't working? Is there a way to work around that? At this point, the only
acceptable solution I've found is "run the query, select the whole column,
Ctrl-C, open notepad, Ctrl-V, delete the first line (the name of the column)
and save. But this is really a substandard solution to the problem. Does
anyone have any help for me? If it's not obvious, I'm an Access newbie, and
I'm using Access 2003. Thanks!

 

[Steve Schapel]

Christine,

If you go to the File|Export menu, enter the file and path for your
export, and then click the 'Export' button, you will be taken into the
Export Text Wizard, and there *is* an 'Advanced' button there. Wait
until you get to the final screen of the wizard, and then go to
'Advanced' and save your specification there.

 

[Christine Wolak]

The problem is, I do NOT get an export wizard at all. I get a single dialog
box, that says "Encode Query1 as", and I have four choices: Windows, MS-DOS,
Unicode, and Unicode (UTF-8). And none of these choices give anything near
what I need. If there is some option I need to enable to get to the wizard,
I would LOVE to know what that is!

 

[Ed Robichaud]

Try something like:

DoCmd.OutputTo acOutputQuery, "qryMyQueryName", acFormatXLS,
"MyExcelFileName.xls", True


Substitute your own query name and the name you want for the export file.
Either run this from the VBA Immediate window, or add a command button to a
form and put that code in the OnClick event.

-Ed

 

[Christine Wolak]

I tried building that in a macro, using text format instead of XLS, and I get
the same thing as if I export with the File->Export command, which is
something that looks like this, instead of a 128-char flat file:

 

[Ed Robichaud]

The CSV file that you quote is returning 1 field per record, and
incidentally that field must be in your query more than once to produce the
unique label "Exp1000". Is your query set to display more fields than just
the street address? Are you expecting that one field to be 128 characters?
-Ed

 

[Christine Wolak]

My query returns one big, long, concatenated string in a single field,
comprising about 20 fields all concatenated together. The total length of
this single field is 136 characters (not 128).

 

[John Nurick]

Hi Christine,

1) If the import/export wizards seem to be misbehaving, it never hurts
to re-register their DLLs.

ACCWIZ.DLL
msexcl40.dll (Excel)
msxbse40.dll (dBase)
mstext40.dll (Text, CSV, tab-delimited))
In Access 97, the filenames contain 35 instead of 40.

2) Use DoCmd.TransferText rather than DoCmd.OutputTo.

 

[Ed Robichaud]

Not sure why that output is being clipped, but you could try using a
schema.ini file that sets the output criteria you want. It would look like:

[myExportFile.txt]
ColNameHeader=False
CharacterSet=1252
Format=Delimited( )
Col1=CourseNumber Char Width 140

Cut and paste the above into a text file named "schema.ini" and put that
file in the same folder as your mdb.

-Ed

 

[Ed Robichaud]

Oops! line #5 was incorrect; here is the corrected version:

[myExportFile.txt]
ColNameHeader=False
CharacterSet=1252
Format=Delimited( )
Col1=myFieldName Char Width 140

-Ed

 

[John Nurick]

[myExportFile.txt]
ColNameHeader=False
CharacterSet=1252
Format=Delimited( )
Col1=myFieldName Char Width 140

Christine wants a fixed width import. Should be

Format=FixedLength

 

[Christine Wolak]

Sorry for the delay - I stopped getting notifications of updates to this
thread.

Anyway, I found out the problem. It is because this is a pass-through
query. I could not find any way around this problem, so I ended up changing
this to a linked table.

I can understand not being able to do an IMPORT to a pass-through query, but
I was trying to do an EXPORT. I figure the code is probably shared and it's
an all-or-nothing kind of thing for Export/TransferText. Unfortunately this
FORCES me to expose the data to updates by the client - exactly the thing
that Access is trying to stop by not allowing the TransferText method on
passthrough queries in the first place!

So, does anyone know how I can lock this linked table down, to not allow
updates? If it matters, this table links to a view in SQL Server 2000, so I
don't know if updates would actually "take" anyway. But I want to protect
the data any way I can.

Thanks!
Christine Wolak