Problem with dBase IV ODBC access via ADO
| Author |
Message |
|
J. Hohl
#1 / 5
|
 Problem with dBase IV ODBC access via ADO Hi! I am accessing a DBase IV "database" ;) from a VB DLL via ADO.
Now i found out that while updating or adding records to a table (.DBF) DOES
update the corresponding index file (.MDX), the index file is NOT updated
when i delete a record. Thus the record still is retrieved from another
remote application. After deleting from the RecordSet, i perform an Update
call. I also tried UpdateBatch, as proposed in the Online help, but got the
same result: no change to the Index file.
Can anyone help?
Thanx in advance, Jo
By the way: is it true that DBase access via ODBC only works with ODBC
drivers older than V3.5? With newer drivers, i cannot get any write access!
|
| Mon, 24 Feb 2003 16:59:38 GMT |
|
 |
|
Tim Snyde
#2 / 5
|
Problem with dBase IV ODBC access via ADO Jo, I'm trying to get the complete story on this myself. Maybe someone here will
give us the definitive answer. It looks like you can read and write to dBase
and FoxPro tables using the lateset ODBC and ADO. As you (and we)
discovered, the indexes are not updated when writing. I think the indexes
aren't even opened. You can't set an Index property and you can't use the
Seek method.
MDAC 2.1 and later overwrites ODBC files with the latest version and you
lose the functionality you once had.
I've read that if you buy the Borland Database Engine you can connect to
dBase .DBF/.MDX with the latest ODBC driver and use ADO. Haven't tried this.
You can use DAO 3.51 to read/write to xBase tables and indexes.
Apparently there is a Visual Foxpro ODBC. I haven't found it or tried it.
Experts, jump in here and straighten us out, please.
Tim
Quote: > Hi! > I am accessing a DBase IV "database" ;) from a VB DLL via ADO.
> Now i found out that while updating or adding records to a table (.DBF)
DOES
> update the corresponding index file (.MDX), the index file is NOT updated
> when i delete a record. Thus the record still is retrieved from another
> remote application. After deleting from the RecordSet, i perform an Update
> call. I also tried UpdateBatch, as proposed in the Online help, but got
the
> same result: no change to the Index file.
> Can anyone help?
> Thanx in advance, Jo
> By the way: is it true that DBase access via ODBC only works with ODBC
> drivers older than V3.5? With newer drivers, i cannot get any write
access!
|
| Fri, 28 Feb 2003 05:00:04 GMT |
|
|
|
Jason Ratk
#3 / 5
|
Problem with dBase IV ODBC access via ADO Does anyone know how to pack a dBase database in code? I am using DAO 3.51
to access the database (dBase IV), and the possibility exists in my program
that the database may be purged. I need to pack the database after this, and
have yet to find anything on MSDN to do this... I know this isn't exactly
realted to this thread, but I would appreciate any help I can get...
Thanks,
Jason
Quote: > Jo, > I'm trying to get the complete story on this myself. Maybe someone here
will
> give us the definitive answer. It looks like you can read and write to
dBase
> and FoxPro tables using the lateset ODBC and ADO. As you (and we)
> discovered, the indexes are not updated when writing. I think the indexes
> aren't even opened. You can't set an Index property and you can't use the
> Seek method.
> MDAC 2.1 and later overwrites ODBC files with the latest version and you
> lose the functionality you once had.
> I've read that if you buy the Borland Database Engine you can connect to
> dBase .DBF/.MDX with the latest ODBC driver and use ADO. Haven't tried
this.
> You can use DAO 3.51 to read/write to xBase tables and indexes.
> Apparently there is a Visual Foxpro ODBC. I haven't found it or tried it.
> Experts, jump in here and straighten us out, please.
> Tim
> > Hi!
> > I am accessing a DBase IV "database" ;) from a VB DLL via ADO.
> > Now i found out that while updating or adding records to a table (.DBF)
> DOES
> > update the corresponding index file (.MDX), the index file is NOT
updated
> > when i delete a record. Thus the record still is retrieved from another
> > remote application. After deleting from the RecordSet, i perform an
Update
> > call. I also tried UpdateBatch, as proposed in the Online help, but got
> the
> > same result: no change to the Index file.
> > Can anyone help?
> > Thanx in advance, Jo
> > By the way: is it true that DBase access via ODBC only works with ODBC
> > drivers older than V3.5? With newer drivers, i cannot get any write
> access!
|
| Fri, 28 Feb 2003 23:12:02 GMT |
|
|
|
Tim Snyde
#4 / 5
|
Problem with dBase IV ODBC access via ADO This is just a guess: You would copy to a temp table, delete the original table and index and re-name the temps. I don't know if the indexes will be ok or not. Here's what we've switched to in our xBase apps. It's an ancient technique
that will also work in other databases. It requires more work, but it has
its advantages.
Instead of deleteing records, we remove them by blanking them out and
putting a high order character in an indexed field (not the primary, since
it can't have duplicates) We use char(251), (?). When we want a new record,
we look for any marked with the check mark and use it rather than adding a
new one. If there are no reuseable records, we add a new one. Thus we never
have to pack, but we have to suppress records with the check mark from
appearing on the screen or in reports.
Advantages: No packing.
Can be used in SQL-style databases.
If you move to a SQL-style database, deleted records are gonzo. You can't
get them back and they create "dead wood" in the database (at least in
Access)
You don;t fill up Access tables with dead wood nearly so quickly.
Disadvantages : More coding to contend with unwanted records
Quote: > Does anyone know how to pack a dBase database in code? I am using DAO 3.51
> to access the database (dBase IV), and the possibility exists in my
program
> that the database may be purged. I need to pack the database after this,
and
> have yet to find anything on MSDN to do this... I know this isn't exactly
> realted to this thread, but I would appreciate any help I can get...
> Thanks,
> Jason
> > Jo,
> > I'm trying to get the complete story on this myself. Maybe someone here
> will
> > give us the definitive answer. It looks like you can read and write to
> dBase
> > and FoxPro tables using the lateset ODBC and ADO. As you (and we)
> > discovered, the indexes are not updated when writing. I think the
indexes
> > aren't even opened. You can't set an Index property and you can't use
the
> > Seek method.
> > MDAC 2.1 and later overwrites ODBC files with the latest version and you
> > lose the functionality you once had.
> > I've read that if you buy the Borland Database Engine you can connect to
> > dBase .DBF/.MDX with the latest ODBC driver and use ADO. Haven't tried
> this.
> > You can use DAO 3.51 to read/write to xBase tables and indexes.
> > Apparently there is a Visual Foxpro ODBC. I haven't found it or tried
it.
> > Experts, jump in here and straighten us out, please.
> > Tim
> > > Hi!
> > > I am accessing a DBase IV "database" ;) from a VB DLL via ADO.
> > > Now i found out that while updating or adding records to a table
(.DBF)
> > DOES
> > > update the corresponding index file (.MDX), the index file is NOT
> updated
> > > when i delete a record. Thus the record still is retrieved from
another
> > > remote application. After deleting from the RecordSet, i perform an
> Update
> > > call. I also tried UpdateBatch, as proposed in the Online help, but
got
> > the
> > > same result: no change to the Index file.
> > > Can anyone help?
> > > Thanx in advance, Jo
> > > By the way: is it true that DBase access via ODBC only works with ODBC
> > > drivers older than V3.5? With newer drivers, i cannot get any write
> > access!
|
| Mon, 03 Mar 2003 03:00:00 GMT |
|
|
|
Jason Ratk
#5 / 5
|
Problem with dBase IV ODBC access via ADO Thanks, that's a good idea to not actually delete the records. As it turns
out, your first suggestion is what I ended up doing. I was given a reference
to an MSDN article with a code sample to do this (Q119116), and I am now
happily on my way to more dead-ends ;)
Thanks for the help, and I will definitely remember that little trick. I am
of the opinion that a little extra coding never hurt anyone as long as it
helps the overall performance...
Jason
Quote: > This is just a guess: You would copy to a temp table, delete the original > table and index and re-name the temps. I don't know if the indexes will be > ok or not. > Here's what we've switched to in our xBase apps. It's an ancient technique
> that will also work in other databases. It requires more work, but it has
> its advantages.
> Instead of deleteing records, we remove them by blanking them out and
> putting a high order character in an indexed field (not the primary, since
> it can't have duplicates) We use char(251), (?). When we want a new
record,
> we look for any marked with the check mark and use it rather than adding a
> new one. If there are no reuseable records, we add a new one. Thus we
never
> have to pack, but we have to suppress records with the check mark from
> appearing on the screen or in reports.
> Advantages: No packing.
> Can be used in SQL-style databases.
> If you move to a SQL-style database, deleted records are gonzo. You can't
> get them back and they create "dead wood" in the database (at least in
> Access)
> You don;t fill up Access tables with dead wood nearly so quickly.
> Disadvantages : More coding to contend with unwanted records
> > Does anyone know how to pack a dBase database in code? I am using DAO
3.51
> > to access the database (dBase IV), and the possibility exists in my
> program
> > that the database may be purged. I need to pack the database after this,
> and
> > have yet to find anything on MSDN to do this... I know this isn't
exactly
> > realted to this thread, but I would appreciate any help I can get...
> > Thanks,
> > Jason
> > > Jo,
> > > I'm trying to get the complete story on this myself. Maybe someone
here
> > will
> > > give us the definitive answer. It looks like you can read and write to
> > dBase
> > > and FoxPro tables using the lateset ODBC and ADO. As you (and we)
> > > discovered, the indexes are not updated when writing. I think the
> indexes
> > > aren't even opened. You can't set an Index property and you can't use
> the
> > > Seek method.
> > > MDAC 2.1 and later overwrites ODBC files with the latest version and
you
> > > lose the functionality you once had.
> > > I've read that if you buy the Borland Database Engine you can connect
to
> > > dBase .DBF/.MDX with the latest ODBC driver and use ADO. Haven't tried
> > this.
> > > You can use DAO 3.51 to read/write to xBase tables and indexes.
> > > Apparently there is a Visual Foxpro ODBC. I haven't found it or tried
> it.
> > > Experts, jump in here and straighten us out, please.
> > > Tim
> > > > Hi!
> > > > I am accessing a DBase IV "database" ;) from a VB DLL via ADO.
> > > > Now i found out that while updating or adding records to a table
> (.DBF)
> > > DOES
> > > > update the corresponding index file (.MDX), the index file is NOT
> > updated
> > > > when i delete a record. Thus the record still is retrieved from
> another
> > > > remote application. After deleting from the RecordSet, i perform an
> > Update
> > > > call. I also tried UpdateBatch, as proposed in the Online help, but
> got
> > > the
> > > > same result: no change to the Index file.
> > > > Can anyone help?
> > > > Thanx in advance, Jo
> > > > By the way: is it true that DBase access via ODBC only works with
ODBC
> > > > drivers older than V3.5? With newer drivers, i cannot get any write
> > > access!
|
| Mon, 03 Mar 2003 03:00:00 GMT |
|
| |
|
