Solved

DBase import via DAO, recognize deleted records

Posted on 1998-12-15
8
297 Views
Last Modified: 2010-07-27
I am trying to import some legacy DBase stuff into a Jet database & notice they used absolute record numbers to reference records. So my problem is to import that stuff preserving those joins. I create a destination table with an additional ID field declared as autoincrement, set the Xbase Engeine's Deleted Seting to 0, link to the source table without specifying indexes, and run a insert into query. This *will* produce the correct ID values. However, I do not want the deleted records, so I need a way to segregate them. My approach to this is to import a second copy with Deleted=1 & then matching them up, but I run into the problem that Jet will not respect the new registry settings when I write them on-the-fly from my Importing utility. Opening multiple DBEngine objects seems a bit hard to do since it prevents me from using DoCmd.TransferDatabase.

- Can someone tell me how to recognize deleted DBase records with HKLM\Software\Microsoft\Jet\3.5\Engines\XBase\Deleted = REG_BINARY 00?
- Or can someone tell me how to force a reload of the Jet Engine Settings?
0
Comment
Question by:kuk010998
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 5

Expert Comment

by:chewhoung
ID: 1971024
I can write a Clipper program for you if you want. It will remove all deleted record from the dbf file. Let me know the dbf file name and your email address if you want it.
0
 
LVL 5

Expert Comment

by:chewhoung
ID: 1971025
Let me know also the path of the directory where the dbf file will be in.
0
 
LVL 1

Author Comment

by:kuk010998
ID: 1971026
Hi chewhoung - no thanks. Modifying the source data is not the approach I want - besides, if I just packed the dbf I'd get wrong Record Numbers afterwards...
I think I'll have to move to VB & force a Jet engine reload. There was a KB article on how to do that - points still available for a tip on how to read the deleted bit or how to reinitialize Jet from within Access...
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 7

Expert Comment

by:smilitaru
ID: 1971027
I don't knot sure, but I think you have no need to remove the deletede records.
Your problem seams to be how you can use a dbf with or without deleted records. I search for a method for reinitialisation for DBEngine object, but after you used first time a DAO object you'll not be able to change his seetings.
So, my advice is to open trough OLE another session if Access, after you change the seetings in registry, import the data in a temporar database and after that you close the new access session and import the data in your current database.
This will eat some time and resources, but will work (I supose).

0
 
LVL 1

Author Comment

by:kuk010998
ID: 1971028
smilitaru: Hmmmm - opening Access sessions thru OLE automation, and dropping & reopening those after changing the registry settings *would* work, but I am not sure I want to depend on Access being actually installed - honestly, I do not know yet whether this importing utility will ever be installed onto a client machine, but if, I don't want that dependency. If not, the process will be one-time and I can do it by hand... I'll reject for now & wait for better suggestions till monday.
0
 
LVL 7

Expert Comment

by:smilitaru
ID: 1971029
Ok. I knew it. It was not a good solution. It was just a solution, and all your observation are correct. I like develop end-user application which not depend so much of user machine, too.
0
 
LVL 1

Accepted Solution

by:
ronaldvr earned 220 total points
ID: 1971030
Open dbase, add a logical field to the database called DeletedRec for instance. Run the following statement:
replace all DeletedRec with deleted()
(deleted puts true (.T.) in the field if the record has deleted, false (.F.) if not)
Import your data (with the deleted setting set to 0 of course).

Then use in access the deleted rec field to run the queries you want.
0
 
LVL 1

Author Comment

by:kuk010998
ID: 1971031
Oh well, this still is not what I wanted (get the info using only MS components), but - I want to close this anyway, and your solution is so straightforward that I can call it clever. Not that I think it's worth the twohun points, but then again it's not worth insulting you with a lower rating either. have sun!
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question