Linking To DBase File Trouble

[C_Monster221]

Hello All,
I have a link to a dbase file that I need to base my entire database on. This file is updated via a program we use in the office. I need to find a way to give this file a primary key and allow for frequent updates. The file I am trying to use sadly is outside the limitations for Access. So I am unable to add the fields we need in the office. I have tried making a select query to give me only the fields i need and then create a new table. My current problem is keeping the new table updated to the dbase file. I have tried real hard in trying to use an update query but it seems to work for me. Any help in solving this problem would be gratly appreciated. Thank you for your time in advance.

 

[neileg]

If you don't have a PK in the underlying data, you really are stuck. Is there a combination of two or more fields in your data that give a unique combination? I don't normally like composite PKs but you have to do what you can.

 

[Atomic Shrimp]

Depending on the dBase file version, you may be able to add an autonumber field (or the equivalent of it) in the underlying dBase table. However, this might break the application that natively uses the tabe, so if you can, try it on a copy of the system, not the live one.

Could you describe the data a little more? Is there any combination of fields that would denote unique records?

If not, then I would do it like this (and I'm assuming it's something like a sales history table, where records get appended, but never changed once they are there):

1. Link the table into an access DB,

2. Create a new, empty structure, identical to the linked table, except also with an autonumber field.

3. Create a find unmatched query to discover records that are in the linked version, but not in the local copy (based on date, perhaps - maybe all new records dated yesterday and before, to ensure you don't get half of today's)

4. Append the results of that find unmatched query into your copy table.

5. Repeat from step 4 on a daily/scheduled basis

There is another pitfall to be avoided when you're working with dBase files in Access - certain versions of dBase have a philosophical disagreement about dBase file header structure - the second byte in the actual file describes the year the file was last updated. Some versions of dBase expect this to roll around (so it would contain '09' now), some just expect it to contain the number of years since 1900 (so it would contain '109' now) - in some cases, a dBase file created or linked in Access will become unreadable to its native application.

I have a fix for this, which I can post later, if you think it might be a problem...

 

[C_Monster221]

Thank you for the fast reply to my problem. I will try these solutions and post back any results.

 

[C_Monster221]

I have been trying the fix my problem based off the response from Mike Gurman. I used "Make Table" from the design screen selecting a few of items in the dbf file. I then made one of the fields that was unique in every entry my pk.

At this point I made a unmatched query of the two files. I based the query off of a unique field. At this point i switched the query to append the table i just created from the dbf file. When I run this append query, I get an error stating a type conversion error. I am confused since all fields should be exactly as the dbf. What could be causing me this problem now.

This particular app I am working on is based off of data taken from a scheduling software. Where the data i am tracking is both the employee and consumer. The scheduling app saves its files in the dbf format. I just need to get the information about the employees and consumers out of it since this changes frequently. The files I need have way more fields than I need. Making it impossible to import. So I linked to them, only to take the files I need out. Sadly the dbf has no pk so i am forced to duplicate it to a new table where I can make a pk based off one of the unique fields. my entire app is based off these dbf files. I can get the information intially but it is the problem is updating of the info. Such as an employee/consumer going from active to inactive. The dbf file is continuous, so as it adds a new consumer/ employee it gives the new person a new record number. It is that file that I use as my pk.

 

[neileg]

If you have a unique field why not use that as the PK?

 

[C_Monster221]

Neileg, I would love to but I am unable to set a primary key off of the linked table. But I did manage to set up a pk on the copied table. It is at this point, I am unable to enuse a proper update of the data. My intial dbf file is displayed one way ( I am unable to even interpret its exact or order and can not reorganize.) and my copied table does not copy the exact order from the source. I need point out i have verified that the data is correct manually searching for parts. But I can not verify that my update queries are correct after that. As stated earlier the update either appears to not work or gives a conversion error.

 

[Atomic Shrimp]

If there is a native unique field that could be used as a PK, it's even easier.

Link the table
Make an empty copy of the structure
On launching the application each time, empty the copy table and append the whole linked table into it afresh

That should let you set the unique field (in the copy table) as PK.

 

[Atomic Shrimp]

For the record (and I hope you never have cause to use this), here is the code I mentioned to fix dbf headers when Access has rendered them unreadable to dBase:

Public Function FixDBF(PathName As String)
Dim mybyte As Byte
mybyte = Val(Format(date, "YY"))
Open PathName For Binary As #1 Len = 1
Put #1, 2, mybyte
Close 1
DoEvents
End Function

It just rewrites the second byte of the file to be the current year within the decade. I wrote the function when I was creating dBase III files in Access 97 and Access 2000, and they suddenly broke on the first day back to work in January 2001.

 

[C_Monster221]

I would like to thank you all for your help in helping me sort through my problem. I am happy to say I have at this point gotten this thing worked out. But i need to ask since i now have the update problem licked. I am trying to now work on setting up some sort of automatic data refresh which would include newly added files. Such a completely new consumer name added to the dbf file after and update.
To my limited knowledge the update will change only those files which have already been in place it will not add totally new content. New content is accomplished by using an append query. But I do not wish to add all the content just the new. How should I go about this.

 

[Atomic Shrimp]

One other thing I just remembered about working with linked dBase files (only applies if you're using Access to change their contents) - deleting records doesn't delete them for real - it just marks them for deletion - they won't be gone for good until you open them in dBase and 'pack' them.

That's actually the standard methodology for record deletion anyway in dBase, but Access must be doing it in some way subtly different, because some legacy applications that just wouldn't see records deleted (marked for deletion) in dBase, still see them as present if they were deleted in Access as a linked table. Applications compiled in Clipper (essentially compiled dBase) are susceptible to this).

If you need to be able to delete records in a dbase table, via Access, the only safe way to do it is to keep a clean, empty template dBase table, create a copy, link it and append the required recordset into it - never actually deleting records, just appending the ones you want to keep into a virgin copy of the empty table each time.