[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 915
  • Last Modified:

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

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-

  • 2
1 Solution
Patrick MatthewsCommented:
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.
SAbboushiAuthor Commented:
Thanks for your post.  Any documentation you can refer me to support this?
SAbboushiAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now