Solved

DBase import via DAO, recognize deleted records

Posted on 1998-12-15
8
277 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

910 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

19 Experts available now in Live!

Get 1:1 Help Now