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



SAbboushiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.