How export an Access 2007 table to dbase and maintain record order?

Posted on 2009-02-17
Last Modified: 2013-11-29
I have a dBase IV file that I need to import into Access, modify, and then export to a dBase file.

I need the record order to be maintained (i.e. the first physical record in the original dbase file must be the first physical record in the new dbase file).  There is no field in the original dbase file that can be sorted in order to retain the original physical record order.

How can I use ACCESS 2007 to write a new dBase file that retains the origina dbase file record order?  I am finding that Access is reordering the records during export.

I need an answer to that specific question

With Regards-

Question by:SAbboushi
    LVL 92

    Expert Comment

    by:Patrick Matthews
    SAbboushi said:
    >>I need an answer to that specific question

    Sam, it cannot be done.  The only way to maintain the order is if some column or combination of columns
    can specify the order.  Otherwise, the physical ordering will be arbitrary and unpredictable.

    Author Comment

    Thanks for your post.  Any documentation you can refer me to support this?

    Accepted Solution

    OK - seems I have found a way to do this.  Here's what I have done:

    I have a dBase IV table 'TA' with about 250,000 records in it.
    In Access 2007, I create a link to the TA.dbf file
    I create a Table in Access, 'TA with ID'.  Same fieldnames as TA; add an AutoNumber ID Primary Key field at the end
    Run an Append query to append TA records to 'TA with ID' table.  

    I find that the TA link provides the records in the physical order they are stored..
    I find that the append also occurs in the TA physical record order
    As each record is appended, the ID field is creating the means for me to keep track of the original TA physical record order (in some cases I fiind the view re-sorts the records, but the ID field still contains the correct physical record order number)
    I then do what I want to update the table

    The Export to dBase seems to use the Primary Key field to order the records during export.  Note: Until I defined ID as a Primary Key, the dBase export changed around the physical record order somewhat.

    Several trials with 250,000 records - working perfectly.

    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

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    754 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

    24 Experts available now in Live!

    Get 1:1 Help Now