Solved

DBase import via DAO, recognize deleted records

Posted on 1998-12-15
8
313 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

627 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