Access 2003, modify export spec

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?
QMBBAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
crystal (strive4peace) - Microsoft MVP, AccessConnect With a Mentor Remote Training and ProgrammingCommented:
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
 
PatHartmanCommented:
Is the export not working the way it is?
Is the data being truncated?
What is the format of the export file?
0
 
QMBBAuthor Commented:
File format is .txt, tab delimited.

Yes, that field's data is being truncated at 255.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
PatHartmanConnect With a Mentor Commented:
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
 
Kelvin SparksCommented:
Is the export being made from a table or via a query?

Kelvin
0
 
QMBBAuthor Commented:
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
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
 
QMBBAuthor Commented:
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
 
QMBBAuthor Commented:
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
 
QMBBAuthor Commented:
Both of the comments I selected were valid solutions to the end result.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
glad you got it ~
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.