Importing Dbase files in unexpected record order.

[Peterout]

Hi gang . . .

I use Access 2000 for importing Dbase files created from another program. These Dbase files can have different fields and have records anywhere from 500 to 150,000. The program that creates the Dbase file sorts the records in a very specific order.

I then import the Dbase file into Access for some additional work. However, Access will at times mess up the order of the records of the imported Dbase file. It does not matter if it was a small or large Dbase file. And it happens with about every 10 imports or so. Just a note: I use a new Access Database for each clients order.

I have nothing set in the Options > Tables/Queries > AutoIndex.

Because this happens I need to review each file imported which can be a lot of work!

When I do spot the problem, I delete the table I just imported and re-import the same Dbase file. And usually on the second try the file is in proper order.

I have checked the Dbase files prior to importing into Access with a generic Dbase file viewer and all the records show sorted fine.

Any ideas as to why Access would randomly scatter the sort on an imported Dbase file?

Also, just to let you know, I have no other option but to work with Dbase files.

Thanks for any advice.

Peter

 

[PHV]

In a relational database the usual way to guarantee a properly sorted resultset is a query with an ORDER BY clause, dot.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[alvechurchdata]

Is there an index file with the dbf or are you utterly dependent on the physical order of the records in the file?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Peterout]

Thanks PH . . . but I don't have a field that I can rely on for a sort. The program that created the DBF gives it a very specific order which does not allow for any specific fields to be used for sorting in Access.


Thanks Geoff . . . there is no index file in the DBF file, so yes, I am dependant on the physical order of the records. Right now I am using other programs like Parserat and DBF Viewer 2000 to handle the DBF files instead of Access. Access is just giving me too many random sorts. The other programs I am using are a temp measure but they have never messed up the sort of the DBF file.

Frustrating to say the least :-(

Peter

 

[alvechurchdata]

yes, I am dependant on the physical order of the records

That's bad - but you know that<g>.

I was thinking that perhaps the viewer was respecting the index but that the Access import was ignoring it.

Can you ask the supplier to add a new field holding RECNO() so as you can then sort your import on record number?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Peterout]

Thanks Geoff . . . not much chance of the software supplier adding a new field for me. But I will ask. The software specializes in sortation of addresses by delivery mode. It is quite complex and the order of the records output conforms with Postal regulations. No extra fields are supplied such as a Sequence Field. The only way I am checking the sort visually is by looking at what is called tray and bundle breaks. They need to be in order but there is clustered info in the fields, so I can't even sort with those . . .

<sigh> . . . why can't Access just do a "dumb" import like the simple little dbf viewing programs I have?

Peter

 

[alvechurchdata]

<sigh> . . . why can't Access just do a "dumb" import

I'd guess that Access is being truly relational and treating the data set as a pure mathematical set.

Hey ho. Off you go to eBay and look for a cheap copy of dBase II so as you can hack the dbf.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Peterout]

Nah, not buying a copy of dbase II . . . lol

I still like Access for all the other things it does . . . just frustrated with the way it sometimes likes Dbase and sometimes not . . . Then again it may be just my ignorance on understanding dbase and/or Access when it comes to importing.

Thanks for your help Geoff, appreciate it . . .

Peter

 

[pbrodsky]

Can you link to the dbase files instead of importing them? That should leave the original files alone and the order unchanged.

I don't know what will happen when you try to use the files in Access.

Are you planning on running queries or some sort of ADO/DAO processing?

 

[Peterout]

Hi PB . . .

Yes, I do run queries on the files once imported. I also add some additional fields and populate them with other information. So linking to the dbase files will not help me.

I have used dbase converting programs to change the dbase to text or tab delimited files > then import the file as a txt file. Problem is that the process is too time consuming for me. On average I am handling 20 different dbase files created from various clients each day. I need a quick way to bring in the dbase file in Access, quickly run some queries, then output again to a dbase file to a large printing system. ---- without the worry that the dbase file I initially imported into Access has not been somehow corrupted in the original sort order.

Access is the only program I have that has a problem with leaving the sort order of the dbase alone. All other programs that touch the dbase file have no problem in keeping records straight, including Excel.

Thanks . . .

Peter

 

[pbrodsky]

Once again - it's not fair to characterize it as a "problem". That is the way relational databases are supposed to work. Any databases the guarantee the existing order are providing a 'extra' feature.

 

[Peterout]

Once again - it's not fair to characterize it as a "problem". "

? - I can take the exact same .dbf file and import it into Access. That exact file will at times import in the original order and at other times import with the order completely messed up. So . . . how is that not a problem with such inconsistent import results of the exact same dbase file?

 

[PHV]

Just because physical record order is IRRELEVANT in any relational database.
Again, to guarantee a properly sorted resultset the only way is a query with a proper ORDER BY clause, DOT.

 

[Peterout]

Thanks for all the responses . . .

 

[alvechurchdata]

Access is the only program I have that has a problem with leaving the sort order of the dbase alone. All other programs that touch the dbase file have no problem in keeping records straight, including Excel.

Sounds as though you need something with less database intelligence than Access - but I can't think what.

You could use Excel for the shorter tables but Excel won't take 150,000 rows.

I'd use Fox (because I've got Fox) but you couldn't justify buying it just to retrofit a sequence number to an existing dbf.

Lotus Approach or Alpha 5 can read dbfs and write the data out again but I don't know enough about either to guarantee that they'd respect the record order.

Crystal Reports or MS Query could read the data and add the extra column but the only useful output would be to Excel and you're stuck with the limited number of rows.

I fear it's time to give up for the night. Perhaps inspiration will strike over the weekend.

Geoff Franklin

http://www.alvechurchdata.co.uk