[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 197
  • Last Modified:

Access 2003 Conversion from 97


I have a database that I converted from Access 97 format to Access 2003 format using Tools-->database utilities --> convert database....

It converted with no hangups, but I have an issue.  I have a report that I export as text.  In Access 2003 the report is truncated after 120 characters per line.  Why does it do this and how can I work around it?

Thanks in advance!

1 Solution
Take a look at the report.  Make sure the text field is allowed to "grow"
dschrishuhnAuthor Commented:

By "grow" Do you mean expand the size of the report by dragging the margins in design view?

The design is exactly the same as it was in 97 format and it exports correctly.  The only thing that changed was the conversion to the 03 format.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
He means there is a property on a report text box ..  Can Grow (and Can Shrink).  Is that set to Yes?

But those properties also existed in 97.  But maybe something got hosed in the conversion.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

dschrishuhnAuthor Commented:
They were set to 'no', but so was the original, in 97 format.

I changed them to 'yes' and got closer to the results I need.  It gave me all of the data but added a line break at 120 characters, before finishing on the next line.  Is there a way to get this all on the same line?

Thank you both for your help so far!
Jeffrey CoachmanCommented:

This is why most devlopers don't export the Report, they export the Report's underlying RecordSource (The table or query)
Furthermore, this "Export Report to Text" option is no longer available in Access 2007.
So it would be best for you to look for an alternative.

Something like this:
    DoCmd.TransferText acExportDelim, , "YourTableOrQuery", "C:\YourFolder\YourFile.txt", True

Or use something like this, if you need some formatting around the data (query):
    DoCmd.OutputTo acOutputQuery, "query43", acFormatTXT, "C:\oeqrf2.txt"

See the help files on "TransferText" and "OutputTo"

FWIW, I have never had any issue with truncation or Line breaks with TransferText.


dschrishuhnAuthor Commented:
Thanks for the suggestions and  thanks for the info about 2007.  By the time we finish the 2003 upgrades, we're going to probably look at that.

TransferText does not work in this case since I'm with a report.  I think it only applies to Tables and Queries.

OutputTo worked as it should, but it still inserts a line break after 120 characters.

Why does it want to start a new line?
Jeffrey CoachmanCommented:
<TransferText does not work in this case since I'm with a report.>
What does "I'm with a report." mean?
I stated that instead of "being with a report", you shjould be exporting ther Report's underlying recordsource (The table or query that the report is based on.)

Did you test the first code snippet?
Again, It has worked perfectly for me for years:
NO truncation and NO Line breaks.
Use the code I posted and export the Reports underlying recordset.

Are you saying you tried the code and it did not work?

Please clarify

dschrishuhnAuthor Commented:
Sorry for the confusion.

I ran the TransferText and it gave me a CSV-text file. The datasource is a table.

I think I need to export the report in this case because of the design.

Thanks for your suggestions so far.

Jeffrey CoachmanCommented:
1. I still do not understand why you are exporting the data to text, but are concerned with Formatting?

2. Try exporting to Excel.

3. Again, this "Export to text" feature is not available in Access 2007.

4. Try making the text Field's datatype Memo.

5. Try makning the Report landscape and making the textbox longer.

6. See these Report Formatting tips from the Helpfile of the OutputTo Action:
You can size the controls by pointing to Size on the Format menu, and then clicking To Fit.
You should avoid overlapping controls or placing controls too close together.
You can align the controls on the report by pointing to Align on the Format menu, and then clicking the appropriate command. Controls that aren't aligned in the same row may be placed in separate rows in the output file, causing additional spacing.


dschrishuhnAuthor Commented:
The system being used is being grandfather-ed out.  Thanks for your time and suggestions
Jeffrey CoachmanCommented:

The Export To Excel functionality is back in Service Pack 2 for orffice 2007.




Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now