Linked table problem?

[geralf]

I have a linked table (dBase iV format) which I can't alter the structure of. I need to define a primary key, and enforce reference integrity (delete and update options checked) What would be the best way to do this, if it's even possible?

TIA

 

[Calvin]

dbase files are Flat-Files and require the software to manage what your looking for, since Access uses Relational structure the primary keys with enforced referential integrity from Access is not going to be possible on a dbase file. You will need to import the file into an access data table to to what you want.

You can use excel to edit dbase files and save them in dbase format, but other than that, I don't know what to tell you.

 

[geralf]

Thanks for your reply. Just what I suspected. So how do I build a good 'routine' to make an Access table from the dBase file? It must prevent duplicate records/to be added to the Access table?

TIA

 

[Calvin]

A good routine, hmm... It depends on your exact need... Do you need to keep and maintain the dbase file? Do you need to export Access data to the dbase file one time, multiple times? Do you only need to routinely import data from the dbase file? Do you just need to do a one time import of the dbase file and go forward maintaining it in Access?
Since you mentioned preventing duplicates, I'm assuming that you need to maintain an access table and routinely import the dbase file periodically but do not want to duplicate existing records. If this is the case you need to create a primary table in access and use a link to the dbase file and then write a series of queries that will import/append new records from the dbase file into the access table, the append query can be configured to exclude duplicate recoreds so only new data is imported. If there is activity in the access table you can export it to a dbase file as well using the TransferDatabase action/method, look it up in the help file or search this site.

 

[geralf]

Thanks Calvin, Pat is right. I've already tried it without any luck.

Pat, this thread is really a continuation of my 'Access to Oracle' thread burried under a ton of threads now.

The dBase file has data from a spectograph (to analyse concentrations of different metal alloys). The software delivered eith the spectograph has different output formats, but I think dBase was the best one if I remember correctly. I'll check this.

I can't import to Access, make structure changes/indexes and export back afterwards, because this corrupts the data on new analysis.

I might have found a solution. I import the dBase table structure to an Access table, made the field I want to be a primary key. I run an 'add records query' which adds the new analysis data to the Access table. The 'key field' in the dBase table is a text string. I convert this with Trim and Clng in the query. This is how far I've gotten today. I now only hope I can trap the error when a duplicate 'primary key' occur? I'll use VBA since I feel this gives me more control. Please comment if this will/will not work.

Thanks for the help

I can't retire the dBase app (but I would love to) since this is built into the analysis software which I don't control of course.

 

[Calvin]

Sounds like your on the right track, if there's a will there's usually a way or at least a workaround.
I understand about the PK and not being able to export it and of course you can't without jacking up your working dbase file format, but what you should be able to do is after your working table with the pk is ready to be exported again you can use/create a query that only displays the fields that fit your original dbase format and within it you can convert your long integers back to a fixed-length text field and export the query results out to a dbase file format. It will take some effort but not impossible.
I have a process that imports WK1 files into Access and then cleans them up and mixes them with data from other tables and then exports them back to WK1's so the legacy DOS app can still utilize them.

Make the tools... use the tools... don't be the tool.

 

[geralf]

Yes, this seems to be working. Haven't moved it to VBA yet, but it looks good.
Thank you both for your help and interest. Much appreciated.