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 |
|
|
|