Solved

Access 2003, modify export spec

Posted on 2016-10-11
11
35 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 36

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 36

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
Independent Software Vendors: 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!

 
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 20
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 20

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 20
ID: 41839166
glad you got it ~
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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