We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

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

Medium Priority
1,002 Views
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-
Sam



Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Commented:
Thanks for your post.  Any documentation you can refer me to support this?
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.