Solved

DBase import via DAO, recognize deleted records

Posted on 1998-12-15
8
269 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now