How to delete a Record from DBF files?

[elbimbo]

Hi,

I try using the ADO to delete a record from a .DBF Files using dBase 5.0 driver but it wont permit on win98,ME but it allows on winXP. what would be the problem?

So... i try using DAO. but with no success. can anybody show me some codes on how to delete a record from a .DBF table?

thanks in advance.

[Franky]

It seems to be a Driver-problem. What is the Format of the DBase files itself? DBase5 files never existed (it was just a new version number for the same file structure and header), only II or III and IV. The version III is the most used version. If you use VBasic and DBase files you should try to use an standard connection with ADO and DBase III.

Here is the connection code for ADO

Dim strProvider As String 'name the database provider
Dim strDataSource As String 'let the system know what data source to search in for the database
Dim strDataBaseName As String 'name of the database
Dim strPassword As String 'password, if the DB is protected
Dim strConnect As String

Set DBF = New ADODB.Connection
'Unfortunatly it needs the correct position, but with AppPath is this to shortcut
DBF.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & "DriverID=277;" & "Dbq=c:\projects2002\vbasic\DbfFiles;"

Set rsDBFdata = New adodb.Recordset
rsDBFdata.Open "Select * From MyCostomers.dbf", DBF, adOpenDynamic, adLockPessimistic, adCmdText

Now you have an recordset in ADO with the name rsDBFdata
Now just use the Standard-ADO commands .Delete .AddNew

This here is the Standard-DBF-driver from Microsoft. I never had any DBase file that didn't work with this driver.

They work with ADO in WindowsMe, W98 WinXP, Win2000. I tried it.


nice greetings
Franky

[elbimbo]

Thank you very much Frank,

I tried running your code and it gives me this error.

Run-time error ‘-2147467259 (80004005)’:
[Microsoft][ODBC dBase Driver] Cannot update. Database or object is read-only.

i am very new with this technology may be i can give you the DBF files i want to delete a record. By the way, when i use the intrinsic data control i can delete the record from there, but i want to do it with codes. pls help me on this... i just need the delete procedure. I also dont know how to use this variables:

Dim strProvider As String 'name the database provider
Dim strDataSource As String 'let the system know what data source to search in for the database
Dim strDataBaseName As String 'name of the database
Dim strPassword As String 'password, if the DB is protected
Dim strConnect As String

can you please show me a complete coding for a delete procedure? Pls. use the attached files.

Hope to hear from u soon. thank you again!...

[Franky]

There is no attachment. Put the attachment here with some records. I will try to solve the problem.

[elbimbo]

Hi,

I cant attached the DBF table coz the allowed size is to small. It is already in a ZIP format and has a 260 KB size.

may be i can email it to you. can i know ur email address.

thanks man!...

[elbimbo]

Hi Franky,

I have resolved the issue. I stop using the dBase driver instead i used the Microsoft Visual Foxpro driver. And it works fine.

Finally i can delete a record from any DBF Table. (foxpro, dBase, clipper). Thanks a lot for helping out.

GOD SPPED!

[Franky]

Good that you solved the problem.

As I said, I was sure that it is a Driver-problem. You should still try to avoid to use bound records. And go away from DBF-Files, sooner or later you will get the next problems. The Index-files are not very stable, specially when the prog jumps out. And you are limited to 15 Indexes in Clipper for each Table. The next problem is that if the people use it in a network DBF-Files are not a good solution.

Ok, have fun
I will be in Manila in the next weeks

nice greetings
Frank

[elbimbo]

Thanks for the tip. so you doin business in manila? good luck man! actually im from cebu. if you need help (vb, sql, ms access, asp) just buzz me in. until then...

[elbimbo]

Hi Franky,

anyone hav an idea on how to completely delete a record from a dbf file/table. in clipper we use the pack command to totaly delete records that are mark for deletion. how can i do this using VB. my database is clipper(DBF) files.

thanks in advance.

[Franky]

I used in Clipper never the Pack because it means that the Database have to be cleaned regulary.

A better way is to clear the fields from the record that you want to delete, then delete the record. If you need then later a new record you check first for deleted records and then just use an empty, deleted record. Only if no deleted record exists then you add a new record. It is a way to handle the records that needs normaly no support.

Nice greetings
Franky