Import dbase records marked for deletion (VB6)

Topic

New Reply

When I import dBase III records into an Access table in VB6 using SQL’s INSERT INTO command, I only get the undeleted records. Records marked for deletion in dBase III aren’t imported. My Access table has an additional boolean field to indicate if a record is for deletion.

How would I import all the dBase III records (including those marked for deletion) into my Access table and enter TRUE in my Access boolean field for records marked for deletion?

Reply | Quote

Replies

I am not aware of a setting in Access that will let you import DBase records marked for deletion.

You could add a boolean field to the DBase table in DBase or FoxPro, set it to True for records marked for deletion, then remove the marks. Then import the result into Access.

Reply | Quote

My solutions to my clients are these (both not satisfactory):
(1) Undelete the dBase III records marked for deletion in the original DOS program (Clipper 5.3) and then import them into Access (VB6 program). Then delete them again in the VB6 program by changing the boolean field to TRUE..
(2) Re-enter the records marked for deletion in VB6 and then mark them for deletion by setting the boolean field.

Your suggestion is good but my clients may not know dBase well enough to do it. I am looking for some parameter settings in SQL or some identifiers to detect dBase records marked for deletion when importing from dBase to Access so that my imprt codes can do it automatically without users’ intervention. Look like I am out of luck this time.

Thanks for your help, Hans. You had helped me out of my cul-de-sac a few times previously.

Reply | Quote

Questions similar to yours have been asked in the newsgroups several times, and as far as I can tell nobody has come up with a method to import records that have been marked for deletion directly.

Reply | Quote

Perhaps I see some light, Hans. You said we couldn’t do it “directly”. Can you suggest method(s) that I can do it indirectly or in a round-about way?

Reply | Quote

Sorry to be a wet blanket, the method I described in my first reply was the indirect one I meant.

Reply | Quote

Excuse my naivety. I’m just thinking along this line : a record marked for deletion in dBase begins with a ‘*’. Can we parse the records for the ‘*’ and just omit it?

Reply | Quote

How would you parse the record? I’m sorry, I have no idea about the binary structure of a dBase file.

Reply | Quote

The records in dBase that are marked for deletion aren’t visible to programs attaching to it, so how could you parse records you can’t see?

Reply | Quote

I’m not proficient with dBase, using it only when converting databases to Access, but if there is an export facility or some sort of reporting facility that can be used within dBase to create a text file that is printed, you could try using that approach to create a text file, and then import the text file into Access with a boolean field that indicates they are deleted. However on a ongoing basis, that sort of thing is very difficult to maintain with an Access MDB, as changes can still be made at the table level. SQL Server has triggers that can be used to prevent record deletion, and views that can be used to hide logically deleted records.

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

Thanks HansV, Charlotte & Wendel for your valuable responses. Since it is confirmed that there is no quick fix for my problem (I was hoping there was one), I looked into the structure of a dBase file and I managed to solve my problem though in a convoluted way. I put it down in case there is someone who may need this info in future. Here is my solution:

(1) Get a description of dBase file structure. One can get it, for example, from here for dBase III+ to 5. dBase 7 has a different strucuture.
(2) Make a copy of the dBase file and work on the copy.
(3) Get record number from bytes 4 to 7.
(4) Get record length from bytes 10 to 11.
(5) Open the dBase file with a hex editor to find out where the first record begins. The first byte of the first record varies with available record fields. My database is all text and of fixed length.
(6) Loop through the records to check if the first byte of each record is a * . If it is, then it is a record marked for deletion and replace it with a space. I attach a * to the end of of the value of an available field to signify it is a record marked for deletion. I can’t manipulate the dBase file in VB6 by adding a new boolean field to indicate deleted records without losing the info on records marked for deletion.
(7) Save the newly modified copy of dBase file.
(8) Use SQL to insert the modified dBase file into the Access database. Loop through the records again to look for * at the end of the field in (6). Delete the * and register the boolean field in the Access database correspondingly. One may lose a byte of information if the field in (6) is filled up to the last byte. Choose the most dispensable field with spaces.
(9) Finally delete the modified dBase file as well as detach any linked tables as needed.

All the procedures are done in VB6 and the codes are actually very short and fast too.

Reply | Quote

Thanks for posting back with the solution.

Reply | Quote