Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

How To Delete Records In Foxpro?

I have figured out how to add new records to foxpro tables , i am using adodc1 to connect to a foxpro table and i want to delete records with a delet button what EASY string can i use to do this
my record name is adodc1.

Edit: Moved from General

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Delete Marked Records In Foxpro Database.
I am using VB to connect to a Foxpro database. When I delete a record it still in my database but marked for deletion. I already use the pack command in my code but I get an error like this "File must be opened exclusively."
This is my connection function.

'Connect to Foxpro through ODBC (Microsoft Visual Foxpro Driver)
Function ConnectToFox(ByRef Cn As ADODB.Connection, ByVal strDSN As String) As Boolean
On Error GoTo ErrorHandle
Cn.ConnectionTimeout = 100
Cn.ConnectionString = "DSN=TestFoxpro;UID=;PWD=;
ConnectToFox = True
Exit Function
Call recLog(Now, "ConnectToFox : " & Err.Description)
ConnectToFox = False
End Function

And here is my delete command:
call ConnectToFox(foxCon, "TestFoxpro")
Call Pub_ExecuteSql(foxCon, "delete from table1 where idEM='1'")
Call Pub_ExecuteSql(foxCon, "PACK")

Do I have something wrong?
Thanks in advanced.

Deleting Records From Visual Foxpro
Does Anybody know how to Delete Records permanently from a Visual Foxpro Database, By Using Vb.

you can Do it in VFP by using the Pack Command..but how in Vb?

Selecting Records From Foxpro Table
I am trying to extract some records from a table (exam.dbf) and copy the recordset to another dbf file.

I use the following connection string:

    CNSTR = "Provider=MSDASQL.1;" & _
        "Persist Security Info=False;" & _
        "Driver={Microsoft Visual FoxPro Driver};" & _
        "SourceDB= " & SRC_DBF_FILE & ";" & _
        "SourceType=DBF;Exclusive=No;" & _
    cn1.Open CNSTR

And i have the following statements:

    STR1 = "select * from exam  where date>={^" &
Format(FROMDT, "yyyy/mm/dd") & "}  and date<={^" &
Format(TODT, "yyyy/mm/dd") & "} INTO TABLE " &
    cn1.Execute STR1

When i run the statements i dont get the new DBF file EXAMNEW.DBF in the Tdest_folder.

I am not sure where i get the error. If i run the same query in visual foxpro i get the new table. I am not geting the new table while running the query in VB.

Please help. Its urgent.



Remove Deleted Records Out Of Foxpro Database.
Pls show me the code to delete completely a record out of the Foxpro table.
Thank you very much.

Can We Mark Records For Deletion Like Foxpro?/ Urgent
Hi everybody,

    I am using SQL server and i monitor that table through vb. I know that we can delete the records which is in a particular table.Here the problem. I don't want to really delete that record. I only want to mark that record to refer that it is an deleted one. B.coz sometime later i may restore that record.I don't want to use a seperate table for the deleted records. I want to keep that deleted record in the same table itself. Can we do like that?

Pls.Help me. This is for un urgent work.

Thanx in Advance.

How To Pack Table After Delete Command In FoxPro
I know that has been asked before (I checked this forum back and forth), but can anyone tell me how I can pack a table after deleting records in FoxPro in my case? This is my code:

Private Sub Command2_Click()

' Declare FoxPro variables
Dim CIM_qtyonh As String
Dim CIM_IM_Part As String
Dim CIM_QM_Part As String

' Load the FoxPro database
While Not DataEnvironment1.rsIM_PTN.EOF
CIM_qtyonh = UCase(Trim$(DataEnvironment1.rsIM_PTN.Fields("cim_qtyonh")))
If CIM_qtyonh = 0 Then
'MsgBox "Deleted " & CIM_IM_Part
End If

End Sub
I'm using DataEnvironment to connect to the table.


Delete Records In ADO
I am working in a point of sale system. Some times the customer changes his her mind and need to delete a item from the purchase. The list of item purchased are in a datagrid stored in a Access temporary database. When I try to delete a record I get this message " Insufficient or incorrect key column informaction. Too many rows affected by update"
to delete the record I am using

There is another part where I need to delete all the records to begin from cero the purchase.... rs.delete as parameters to delete allthe records in the group (filtered) but it says it does not work in this context.

Thanks in advance for your help

Delete All Records
I have a table that has to be totaly recreated via code, how do I delete all current records in a table via SQL?

Delete Records
is there an instruction to partialy delete in a table ?
for example i got a recordset with 3 records in it, then i want to delete those and add some more after.
i've tried .delete but i'm probably doing something wrong.

I'll be among the best soon, very soon!!!

Delete Dbf Records

I am trying to delete all the records in a dbf database using vb 6.0.

I am issuing the sql command "delete * from tmpdbf"

The records are marked for deletion but they aren't pemenantly deleted. is there any way I can permenantly delete the dbf records using vb 6.0

Thanks in advance for your input.

Delete Records (DAO)
Ok, down to the last straw with my label creator app. When my Word doc has saved and printed, I would like to clear the database records...

How do I do this with DAO???

Thanks in advance....

Delete All Records

I'm using ADODB.Connection
How do I delete all the records in the table?


Delete Records From S.f.
How to delete records from a sequential file?

How Do I Delete All Records In A Db?
I have an Access database connected to a data control using DAO and I want to delete all records. I tried doing an SQL of:

Data1.RecordSource = "DELETE * in Table1"

But got an Invalid Operation. Is there a different want to use DELETE, INSERT INTO, etc with the Datab control other than the recordsource property?


How To Delete Records From .mdb
Hello Guys and Gals I am kinda new so be nice.
My question is: I have figured out how to get info into my db using the INSERT INTO statement, but can some one give me and example of how to delete a record? Can I use a similar statement as the INSERT INTO, like DELETE FROM? if so please give sample code. This project has 3 textbox entries; FirstName, LastName and Note. I am not having any luck finding help. Each example I have tried gives errors. I need code that works.

Thanks in advance


Delete All Records From A Table?
I want to delete all the records in a table in Access. How would I do this? Would I open the recordset then delete them?

This is what I have thought of:

Dim db As DAO.Database
Dim tblApp As DAO.Recordset

Set db = CurrentDb

Set tblApp = db.OpenRecordset("Appointments")

If tblApp.EOF And tblApp.BOF Then

With tblApp
'This is where I need the code to every record in every field
End With
End If
End Sub

What code would I use?

Delete Records From Datagrid

Why I cannot delete the records which refers to one customer? I have got 3 tables with information about a customer. (Customer, AHNP and BLNP).
If I run the command the next error message will appear:
Run-time error “-2147217873 (80040e2f)’:
Delete statement conflicted with COLUMN REFERENCE constraint ‘FK_BLNP_Customer’. The conflict occurred indatabase ‘BISY’, table ‘BLNP’, column ‘PersoonsID’.

Thanks in advance!


(Code in VB

Private Sub cmdDEL_Click
Dim mResult As Integer
Dim Rst As New Recordset
Dim SQL As String

mResult = MsgBox("Do you want to delete:" & pt2.Text & "en zijn/haar relaties verwijderen?",
_ vbCritical + vbYesNo, "Delete records")
If mResult = vbNo Then
End If

If mResult = vbYes Then
SQL = "SELECT * FROM Customer,AHNP,BLNP WHERE BLNP.CustomersID = " &
Text2.Text & " AND AHNP.CustomersID = " & Text2.Text & " AND
Customers.CustomersID = " & Text2.Text & ""

Rst.Open SQL, Adodc1.ConnectionString, adOpenKeyset, adLockOptimistic, adCmdText
Rst.Delete adAffectCurrent
Set Rst = Nothing
End If
End Sub

Add, Edit, Delete Records
Hi, i am using VB6 and MS Access 2002. Basically im creating a simple program to access a database to view, edit, add and delete records.

At present i have succeeded in viewing a database using a data control. As a result there is not even any VB code which is a good thing for me. However the manual i have been using does not say how to edit, add and delete records. Im looking to keep this as simple as possible.

Can anyone help out please?

Delete All Records In A Table
How can I handle this ?.
Today I am using sql-query against my access-database for
getting data and update data in my tables.
But how can I simply delete all records I have in a specific table ?.

I can do it simply from access, but I need code for running this from my
VB application.

MSHFlexGrid (Delete Records)

I have a question. Let's say I have an MSHFlexGrid and populated with records from a database. My question is, what is the best way to delete a record? Can I delet a highlighted row? If I can, how?


Access Delete Records &gt; 10
hi all,

i've not found how to do it in Tutor's corner nor searching the site. What i need is to delete all records from an Access table having more than 10 records.
( That's only the first 10 records should remain in the table )

Probably the code may look like this :

dbAcc.Execute "delete from sduut where ... > 10 "

please any idea ..?
thanks !

Delete Multiple Records
i now how i have to delete 1 record

With conLev
If .EOF Then .MoveLast
End With

but now I want to delete all the records that I selected with

conVer.Open "select * from verbinding where [Leveranciers ID] = " & leveranciers_ID$ & " Order by [Leveranciers ID];", db, adOpenStatic, adLockOptimistic

Delete Records Problem
can any of you guys work out why this wont work?

Private Sub cmdDeleteAll_Click()
Dim delete As Variant
Dim DeleteallSQL As String
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

If admin = True Then 'this is a module
delete = MsgBox("By clicking yes you will loose all information within this table.", vbQuestion + vbYesNo, "Delete") = vbYes
If delete = vbYes Then
conn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + App.Path + "mytable.mdb;Persist Security Info=False")
DeleteallSQL = "DELETE FROM mytable"
conn.Execute DeleteallSQL

End If
MsgBox ("You do not have the correct rights to perform this action")
End If
End Sub

basically if the user has correct admin rights he [or she] can empty the history table where deleted records are kept.

when i run this module - the prompt "By clicking yes you will loose all information within this table." does come up but when i click yes - the records still show up in the table.

any ideas?

ive been staring at this for 30 minutes trying to figure it out!

Error When There Are No Records To Delete
Private Sub cmdDelete_Click()
Dim Response As Integer

Response = MsgBox("Are you sure you want to delete the selected record?", vbYesNoCancel + vbCritical + vbDefaultButton2, "Delete Record")
If Response = vbYes Then

End If

End Sub

Simple but gives an error when theres no records to delete, somthing about EOF or BOF value = true

can anyone modify this code to trap this error?


How To Delete Top 500 Records In Query?
I am using sql server 2000 and I want a query to delete top 500 records in a table.

Can anyone help? Thanks.

How To Delete All Records In A Recordset
I have a recordset that I need to delete all of the records from so I can reload it. Can I do this without using a loop?

Delete Records In Two Tables
Delete records in to tables
I have two tables one is the master and one the details for example the order and the orders details.

Now I want to delete and order in the master table consecluently I want to delete de order details, too. This means when I delete an order in the master file I also need to delete the records in the details table that belong to these orders.

My question is how can I delete these records in boot tables at once?.

I tied to use JOIN SQL Clause but I do not know understant how can I do that. I did something like this example but it does not work.
Dim DatePur, Match, strDate As String

Dim dbs As Database, rst As Recordset

' Get user input Date to delete
strDate = _
Trim(InputBox("Enter Month to Delete eg. mm "))
If strDate = 0 Then Exit Sub

Set dbs = OpenDatabase("C:CleanServCleaning.mdb")

' Create a join between the Purchase Order and Orders Details
' tables.
Set rst = dbs.OpenRecordset("SELECT DISTINCTROW " _
& "DatePur " _
& "Purchase.RecordID AS PurRec " _
& "PurchDet.RecordID AS DetRec " _
& "FROM Purchase INNER JOIN PurchDet " _
& "ON PurchDet.RecordID = " _
& "Purchase.RecordID " _
& "GROUP BY DatePur;")

MsgBox "Deleting " & strDate
dbs.Execute "DELETE * FROM " _
& "Purchase WHERE DatePur =" & strDate

dbs.Execute "DELETE * FROM " _
& "PurchDet WHERE RecordID =" & OdrRecord

I will apreciate your valuable help

Thank you

Delete Table Records?
I would like to delete every record in one table I have on my program. Can I have the code to do this?

Im using a data control

Delete All The Records In A Table.
I know how to step through and delete each record but is there a faster way? Maybe wipe the entire table in a few commands or just one?

Delete Records From A Dbf File.
How could I delete from a dbf?

If I open the table and try to delete records with delete and update methods, virtually the records are deleted. When I refresh the table or I exit my app, the records are still there.

Any ideas?

Delete Multiple Records
I have a small program that uses a .mdb database and have them connected with a data1 object. I have a command button on the form that is = to data1.recordset.addnew. If you click the add new button and add a new record then click it again to add another one. then click the data1.recordset.delete command button it will delete the current record and the original record you started on. I think I need some more code in the add command . I hope you can help me and that I am clear enough.
Thanks Alot,

Let Me Try Again Delete And Update Records
I am trying to update and delete records, using Visual Basic, from a file I have called "items.csv"

I displayed the current records in a listbox and I was wondering how you use VB and delete records from the file I called "items.csv"

example data in "items.csv"
item# A B C D
0142 123 32 12 YES
1111 111 222 0 NO
1234 123 123 0 NO
and so on

How do you go about deleting item# 1234 in the file item.csv

can anyone help????

Vb6 How To Delete Fox Pro Table Records
By vb6 When I delete fox pro records, They are being selected but remain as selected records in the table
How could i completey delete them?

How To Delete Records In Database(.MDB)?
how do u delete a group of records in the database?......

for example in my database theres some info that i want to delete..... like i want to delete all female in the database and leaves all the male....

i know how to delete by 1, but cant figure out how to delete by group....... i did a code hoping it would work, but it did not....

heres the code i did:

Code:dim fstr as string

fstr = ""

fstr = fstr & "Gender='" & txtGender & "'"
adoPeople.Recordset.Find fstr
adoPeople.Recordset.Filter = fstr
adoPeople.Recordset.Delete fstr

i thought with this code, ones i type the gender in the textbox it will delete all that matches what i typed in the textbox in my database....
this code is giving me errors...

please help me.......

Edited by - asianpinoy25 on 6/16/2004 1:30:41 PM

How To Delete Records In A Table
I'm using ADO for accessing Access DB.
I've tried to delete the records in the table using SQL command.
TRUNCATE TABLE <table name>
But it is not accepting. Is there any possibilities?

[help Me] Delete Some Records From DBF File
I'm using VB 6.0 and DBF files.
And I want to delete some records from the DBF file, with some condition.
Here is the code :

Dim conFox As New ADODB.Connection
    conFox.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;" & _
"Dbq=" & App.Path & "data" & ";"
      conFox.Execute "DELETE FROM subKrite WHERE kode = '" & trim(txtKode.text) & "'"
      If conFox.State = adStateOpen Then conFox.Close
      Set conFox = Nothing

But when I run it, there was an error.
And here is the error message :
"[Microsoft] [ODBC dBase Driver] Too few parameters. Expected 1."
And if I didn't give the condition (see the bold code above):
conFox.Execute "DELETE FROM subKrite" -> no error

How can I delete some records from DBF file with some conditions ?

thank you so much

Edited by - hackey on 1/15/2005 9:47:36 PM

Delete Matching Records
How do I delete records from a Table that are present in another table ? Can I use the delete query ? Both tables do not have unique ids or keys

a b c d
a d c c
a b c d

a b c d
a r t e
a t c r


How Do I Add/Edit/Delete Records Using SQL?
I am having troubles using SQL to handle Add/Edit/Delete of records from a Access DataBase. Actually I got Add to work using the code below. How do I Edit and Delete using SQL?

Code:Private Sub cmdSaveNew_Click()
' Save a new record
Dim rs As ADODB.Recordset
Dim AddRecordYesNoCancel

    If (txtBldNameAdd.Text = "" Or txtWoNumAdd.Text = "") Then
        MsgBox "Nothing to Save!", vbInformation, "Oops"
        'connect to the database and see if record already exists
        Set cn = CreateObject("ADODB.Connection")
        cn.Open ("provider=Microsoft.Jet.OLEDB.4.0;data source=" & App.Path & "WrkOrders.mdb")
        Set rs = cn.Execute("SELECT * from WOrders WHERE BldName = '" & txtBldNameAdd.Text & "' and WoNum = '" & txtWoNumAdd.Text & "' and Issued = '" & txtIssuedAdd.Text & "' and Contact = '" & txtContactAdd.Text & "' and Phone = '" & txtPhoneAdd.Text & "' and Completed = '" & txtCompletedAdd.Text & "' and HRs = '" & txtHRsAdd.Text & "' and WrkDone = '" & txtWrkDoneAdd.Text & "'")
        AddRecordYesNoCancel = MsgBox("Add this Record?" & Chr(13) & "Are you Sure?", vbYesNoCancel, "Add Record")
        If AddRecordYesNoCancel = vbYes Then
        '******************************** New Code From Net Try 2 ********************
        ' Build SQL String
        strSql = "INSERT INTO [WOrders] " & _
            " ( [BldName], [WoNum], [Contact], [Phone], [Issued], [Completed], [HRs], [WrkDone] )" & _
            "VALUES " & _
            " ('" & txtBldNameAdd.Text & "' ," & _
            " '" & txtWoNumAdd.Text & "' ," & _
            " '" & txtContactAdd.Text & "' ," & _
            " '" & txtPhoneAdd.Text & "' ," & _
            " '" & txtIssuedAdd.Text & "' ," & _
            " '" & txtCompletedAdd.Text & "' ," & _
            " '" & txtHRsAdd.Text & "' ," & _
            " '" & txtWrkDoneAdd.Text & "' )"
        ' Connect To Database and Update
        Set cn = CreateObject("ADODB.Connection")
        With cn
            .ConnectionString = App.Path & "WrkOrders.mdb"
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Execute (strSql)
        End With
        Set cn = Nothing

Any help would be Great!


Delete Records After A Certain Recordcount
I want to limit records in my database to 10000. Any records added after that, the earliest record should be deleted.
How can I do that?


In VB 6.0 How To Delete Records Using ADO(OLEDB)
Hello There,
This is my first ever post to this website. My question is:

I am using ADO(OLEDB) Class library to mainupulate my Access 2002 Database. I want to delete records by using query within the VB code my query isSuppose I have initialized Connection and Recordset variables successfuly.

dim Rs as new recordset
dim Con as new Connection
Rs=Select * from S_Erratics from S_Erratics where S_Erratics.userID='" & CustomerID & "'"

if not(Rs.Eof) then
end if
in the said table S_Erratics userID may duplicate. So, there may exist more than one records.
My problem is that when I want to delete as you may see above, Only first two records are deleted. Rest of records does'nt. What is the problem. Please Help.

Trying To Delete All Records With A Null Number
I'm trying to use a SQL statement in VBA to remove all records with a null ID from a table. These are the command strings I've tried, and they all fail:

DELETE FROM PropWriterXLookup WHERE [ID] = ''

On all versions it gives me an error saying, "No value given on one or more required parameters." What's the deal? BTW, the ID field is a number and is not autogenerated.

Edit Add Delete Listbox Records
Im looking for an example of something along this line.

On my userform1 I have a listbox, an edit button, a new button, and a
delete button.

I've gotten a list box to display A18 which has 4 columns.

But I dont know were to even start as far as the edit button.
I would like it so that when I select the row in the listbox and press
edit, a new userform pops up, with 4 textbox displaying the row. Then
you can edit it, and click 'save changes' and it updates that row in
the list.

If anyone could post an example that could help me. I've spent hours seaching for something like this but came up with nothing.

I also dont want to use the Excel DataForm of Jwalk advanced form but am looking for the code tom ake this work.


Use .xls As Reference To Delete Access Records
i want to delete records from an access table if one of the records matches another in .xls

im thinking about opening the .xls as i normally would, then store the values in an array and then create a doWhile loop to test the values in the array against the values in the table.

i just have no idea how to do it.


ASP: Delete Multiple Records Using Checkboxes
Need help here.

I am stucked.

On page1.asp, i have form dynamically generated with checkboxes on it.

Upon submitting of this page1 to maybe processPage1.asp, processPage1.asp will delete records from db where it was previously checked (done in page1.asp).

how do i go about doing that? please advise. Furthermore, the checkboxes are generated dynamically. WHat should i put in id and name attribute?

Best Way To Delete Records From A Text File?
What would be the best way to go about deleting individual records from a text file - the records are input to the file using a udt?

Delete Specific Records From A Table.
I need to delete specific records from a table using ADODB
code. I have tied the following
Option Explicit

Private WithEvents Cn As ADODB.Connection
Private WithEvents rsRecordSet As ADODB.Recordset

Set Cn = New ADODB.Connection
Cn.CursorLocation = adUseClient
Cn.Open dataBase

Set rsRecordSet = New ADODB.Recordset
rsRecordSet.Open "Select * From QuoteDets WHERE QuoteNo = " & cboQuoteNo.Text, Cn, adOpenStatic, adLockOptimistic
End Sub

'I want to say "Delete * From QuoteDets WHERE QuoteNo = " & cboQuoteNo.Text
'But .Execut isn't an option for rsRecordSet
End Sub

Delete Specific Records From A Table.
How do I delete specific records from a table using an SQL statement?
e.g. Delete * From QuoteDets WHERE QuoteNo = " & cboQuoteNo.Text

I am using the adodc component to link to the database.

Delete Muliple Records In Flexgrid
I am having a problem with deleting multiple records in a flexgrid. Here is the code snippet:

Dim varRowCount As Integer
Dim ii As Integer
delinvno = TextInvno.Text
varRowCount = Cwayflex.Rows - 1

For ii = 0 To varRowCount

If Cwayflex.TextMatrix(ii + 1, COL_DELETEEXTRACWAY) = "1" Then
Cwayflex.RemoveItem Cwayflex.Row
varRowCount = Cwayflex.Rows
End If

Next ii

Basically I have an empty first column if the user select the column. A little check appears that tells them that the row(s) is selected. There is
an extra column that is marked "1" when the check appears. So I scan the rows for the "1" value. It deletes the first value find but the rowcount is not reset. I tried to reset the rowcount right after a row is deleted but it still increments past the number of rows and gives the "out of script error" Any suggestions would be greatly appreciated.

Quick Way To Delete Records From Many Tables
Just wanted to know:
Is there a quick way to delete all records in all tables that have a field equal to a certain value?

Copyright © 2005-08, All rights reserved