Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

DBase import via DAO, recognize deleted records

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
kuk010998
Asked:
kuk010998
  • 3
  • 2
  • 2
  • +1
1 Solution
 
chewhoungCommented:
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
 
chewhoungCommented:
Let me know also the path of the directory where the dbf file will be in.
0
 
kuk010998Author Commented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
smilitaruCommented:
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
 
kuk010998Author Commented:
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
 
smilitaruCommented:
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
 
ronaldvrCommented:
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
 
kuk010998Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now