Solved

Access 2003, modify export spec

Posted on 2016-10-11
11
30 Views
Last Modified: 2016-10-11
I have a saved export spec that I need to modify. I need to make one of the fields a memo instead of just text. The source table is definitely memo and storing the data correctly. I changed the table data type after I had already saved the spec.

I went in to use the export spec so I could tweak it and re-save, but nowhere does it give me options to specify data types. How to?
0
Comment
Question by:QMBB
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 35

Expert Comment

by:PatHartman
ID: 41839063
Is the export not working the way it is?
Is the data being truncated?
What is the format of the export file?
0
 

Author Comment

by:QMBB
ID: 41839081
File format is .txt, tab delimited.

Yes, that field's data is being truncated at 255.
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41839093
This is probably a bug in Access.  I've never tried to export a memo field to a txt file.  You may have to write the records yourself using the native VBA methods.  Construct the string "textData" and then create a loop to process an input query.

Open fileName For Output As #fileNo

open your query
    loop through query
        build textdata
        Write #fileNo, textData
    next record

Close #fileNo

OR look up FSO (FileSystemObject)  for other techniques.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41839099
Is the export being made from a table or via a query?

Kelvin
0
 

Author Comment

by:QMBB
ID: 41839115
There was already a memo field in the same table when I created the spec, and it IS making it into the txt file intact, so the spec is definitely accounting for the fact that it's a memo field. It's just that after I changed a different field from text to memo, after I had already saved the spec, that field is still being exported as text 255.

Export is being done via code:

txtExportName = "C:\AmazonFeeds\data.txt"

DoCmd.TransferText acExportDelim, "AmazonInventoryCreateData", "Data2", txtExportName, True

    
    
          ' If an error occurs, close the files and end the macro.
      On Error GoTo ErrHandler

      ' Open the destination text file.
      DestNum = FreeFile()
      Open "C:\AmazonFeeds\feed.txt" For Append As DestNum

      ' Open the source text file.
      SourceNum = FreeFile()
      Open "C:\AmazonFeeds\data.txt" For Input As SourceNum

      ' Include the following line if the first line of the source
      ' file is a header row that you do now want to append to the
      ' destination file:
      ' Line Input #SourceNum, Temp

      ' Read each line of the source file and append it to the
      ' destination file.
      Do While Not EOF(SourceNum)
         Line Input #SourceNum, Temp
         Print #DestNum, Temp
      Loop

CloseFiles:

      ' Close the destination file and the source file.
      Close #DestNum
      Close #SourceNum
      
    DoCmd.Hourglass False
    MsgBox "Done"
    
      Exit Sub

Open in new window

0
 
LVL 19
ID: 41839127
you do not get to choose data type in specs -- only field delimiters  (such as comma) and text delimiters (such as ", ', or {none}), or if it is fixed width, the start column and field width along with field name

I suspect the memo column that is working ok actually has <=255 characters
0
 

Author Comment

by:QMBB
ID: 41839135
I confirmed that the finished file has way more than 255 from that pre-existing memo field. So the pre-existing one IS exporting as memo for sure.
0
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 250 total points
ID: 41839155
look at MSysIMEXColumns

while you cannot edit that table directly ... you can 1. create a make-table query with records for a particular spec (defined in MSysIMEXSpecs), 2. edit the table then 3. update/append records in MSysIMEXColumns
0
 

Author Comment

by:QMBB
ID: 41839158
I figured it out... I had to create a totally new one instead of just trying to save the existing one as I exported. Obviously something in Access backend that holds that table name to the original spec and you don't get to alter it no matter what the table data types are now. So, I created a whole new spec, plugged the new spec name into the code, and it works fine.
0
 

Author Closing Comment

by:QMBB
ID: 41839162
Both of the comments I selected were valid solutions to the end result.
0
 
LVL 19
ID: 41839166
glad you got it ~
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question