Re: Index and Deleted

From: Stephen Howe (stephenPOINThoweATtns-globalPOINTcom)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 14:47:12 -0000

> I fail to see the logic in that ADO will get slower because of deleted
> records. Doesn't it use the indexes to get the records?

I am sure it does. IIRC, back in the days of dbase II (and yes I go that far
back, even pre- MSDOS), a record carried a hidden byte which if it contained
'*' it indicated it was deleted. I am sure the meta-info on the table
indicates that there are deleted records present.
But I think that FIXNR is indexed but not the column where '*' resides.
But this is guesswork

But where does this get you?
I have done some poking around and these are my thoughts

(i) The Jet 4.0 Provider is remarkably undocumented when it comes to DBase
files (and others). If you want to use it, I would be dumping the Connection
Properties, before and after a successful connection, comparing and seeing
what has changed and whether there is a dynamic property that can be set
that makes the difference. Also see
http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovjet_overview.asp

Also this comment on Indexs for Jet 4.0 Provider might explain what is going
on:
>>
Although the 4.0 provider supports the Seek and SetRange interfaces, it does
not support these methods on file formats other than the 4.0 .mdb file
format. To accommodate the OLE DB specification, significant changes and
additions had to be made and it was not possible to make these changes to
the other Installable ISAMs that Jet uses to access other storage formats,
including the IISAMs that access previous formats of .mdb files (which
includes those used in Microsoft Access 97).
>>

But I think that even if significant energy was expended on this, my
educated guess would be that you would hit a brick wall. In short, using the
Jet 4.0 provider is a dead end due to its limitations.

(ii) There was a Microsoft native dbase Provider, but hey, it could not be
used to update the database (by design). That is laughable.

(iii) You best bet is to switch to the FoxPro provider. Since FoxPro is the
native provider for dBASE files you are likely to get better results.

The FoxPro Provider can be downloaded here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=0F43EB58-7A94-4AE1-A59E-965869CB3BC9&displaylang=en

Connection Strings here:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForVisualFoxPro

See also
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp/html/foorivisualfoxprodataaccessusingoledbprovider.asp

Stephen Howe

Relevant Pages

  • Re: HD-DVD story on BBC News 24
    ... >>> Paramount will be offering it's content on both formats. ... content provider to do so, ...
    (uk.tech.digital-tv)
  • Re: ADO.net Code contest
    ... > Ah, actually, accessing an Access database is done through JET. ... > generic interface like OLE DB or ODBC from any data access interface ... ODBC, OLE DB are ... These provider are widely available. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: OleDbException on Access DB Query
    ... not supported by Jet provider. ... > "Val Mazur " wrote: ... >> does not know what is inside of the stored query. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: How to determine if an ADO Recordset column is required
    ... > OLEDB (Jet), I was finally able to locate a Properties that is almost ... I do not understand your obsession about using the obsolete ODBC provider. ... your application has to go through two software layers: the OLE DB ...
    (microsoft.public.inetserver.asp.db)
  • Re: test connection failed
    ... .udl contains connection string and path to database (for Microsoft Jet ... Check also that you have Jet provider installed or install Jet SP from ...
    (borland.public.delphi.database.ado)