Solved

Access 2003, modify export spec

Posted on 2016-10-11
11
19 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 34

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 34

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18
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 18

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now