Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Deleting Records 6

Status
Not open for further replies.
bdavis96

bdavis96

Programmer
Feb 11, 2002
97
US
Is there a limited to the amount of records VB can delete? I keep getting an error:

[Microsoft][ODBC Microsoft Access Driver]Query is too complex

This is my delete function:

Public Sub Delete_Records()
If Not (frmADO.PC.Recordset.EOF) Then
frmADO.PC.Recordset.MoveFirst
Do While Not (frmADO.PC.Recordset.EOF)
frmADO.PC.Recordset.Delete
frmADO.PC.Recordset.MoveNext
Loop
End If
End Sub

The error is always pointing to frmADO.PC.Recordset.Delete

Basically what I am doing with my code is setting the ADO control (PC) to the specified Query and stepping through all the records and deleting them.

Is there an easier/better way to delete all the records from a Access file?

Any help would be much appreciated.
 
Sort by date Sort by votes
All the records?

Try executing "truncate table tblWhatever"

If not, you could also use "Delete from tblWhatever".

Both of these would delete the entire contents of the table. "Delete From" however would keep the sequence of any autonumber fields you have, where as Truncate would reset them.

mmilan
 
Upvote 0 Downvote
Is there an update line after the delete line?

I am trying:

frmADO.PC.RecordSource = "Delete From ASCNTL"

But it doesn't seem to be deleting anything from the table. I have tried:

frmADO.PC.Recordset.Update

but still nothing is working. Any more help would be appreciated.

Brian

 
Upvote 0 Downvote
Is there a limit to what VB can delete from Access? The table I am having problems with has 158 columns and 138 records. I have tried Microsoft Jet and Microsoft Access Driver Connection Strings. It keeps saying "Query is too complex" with either one. Is there a way to work around/correct this? I want to delete all records in a table so I can replace with new records. Any help would be much appreciated.
 
Upvote 0 Downvote
Try the following.



Option Explicit
Public DBConn As ADODB.Connection
Dim adoInsertComm As ADODB.Command
Public dbconnstring As String

Private Sub Form_Load()
Set DBConn = New ADODB.Connection
Set adoInsertComm = New ADODB.Command

DBConn.CursorLocation = adUseServer
dbconnstring = "dsn=LocalServer;uid=frederico;pwd=frede1;database=factucli;"
DBConn.Open dbconnstring

'sInsertSql =
adoInsertComm.CommandType = adCmdStoredProc
adoInsertComm.CommandText = "sp_add_codigo_postal"

adoInsertComm.ActiveConnection = DBConn
adoInsertComm.Parameters("@codigo_postal").Value = "123"
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
I think fredericofonseca's post was meant for thread222-906206.

I'm not clear on what frmADO.PC is. Is that some sort of data-bound control?
I think what mmilan was suggesting is to use the Execute method of your connection object to run simple SQL statements (which would probably be vastly more efficient than looping through the recordset deleting records one by one). I'm not familiar enough with data-bound controls to know how a connection object might relate to them.. or if there's even a connection object for you to use.
If you can get you hands on a connection object we could go from there.
 
Upvote 0 Downvote
Yes your are right. I had a problem with posting a response (server error), and then I pasted the incorrect code.

correct code.

With Adodc1
.CursorType = adOpenDynamic
.LockType = adLockBatchOptimistic
.CommandType = adCmdText
.ConnectionString = dbconnstring
.RecordSource = "delete from mytbl"
End With

Regards

Frederico Fonseca
SysSoft Integrated Ltd
 
Upvote 0 Downvote
See and study the code below that I use to delete every record "BAR ONE" in my app'. The one left over needs to stay. you can replace the data in that one record and update it or simply empty it's contents and then update it.

BUT!, And I mean BUT! If you delete the last record you will get a whole heep of problems even when your application starts up. You get a DB Error and whatever else comes after that.

So, it made good sence to me to delete all other records but ONE.

If you need the code to compact the database after deleting, let me know. As you are probably well aware that deleting and adding records will disfigure the database somewhat, and the size of the mdb file will simply grow unless you compact it.

Here's the code what works for me:
Code: 
Option Explicit
Dim CurrCust As Integer
Dim TotCust As Integer

Sub ClearAllRecords()
    datCust.Recordset.MoveLast
    TotCust = datCust.Recordset.RecordCount
    CurrCust = TotCust
    datCust.Recordset.MoveFirst
    datCust.Recordset.MoveNext
    While CurrCust > 1
        datCust.Recordset.Delete
        CurrCust = CurrCust - 1
        If CurrCust > 1 Then
            datCust.Recordset.MoveNext
        End If
    Wend
    datCust.Recordset.MoveFirst
    CurrCust = 1
    datCust.Recordset.MoveLast
    TotCust = datCust.Recordset.RecordCount
End Sub

I hope this helps!

Andrew.
 
Upvote 0 Downvote
I am using Access XP, but it is saving the DB as Access 2000. I converted the DB to Access 2002, but I am still getting the same problem. How do I found out what version of ADO and ODBC Driver I am using?

I have tried the following connectionstrings, but both give me the same error:

frmADO.PC.ConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myDB.mdb"

frmADO.PC.ConnectionString =
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=C:\myDB.mdb;"

(frmADO is the form that I have the physical ADO control on and PC is the name of it)

Then to connect the tables, I use:

frmADO.PC.RecordSource = "Select * From myTable"

I am guessing that delete is having a problem with the amount of Fields. My problem table has 160 Fields.
 
Upvote 0 Downvote
Andrew,

Will you post the code for the compacting of the database. That would be very helpful.

Brian
 
Upvote 0 Downvote
I don't do connections like you but, if you can have
frmADO.PC.ConnectionString
then you should be able to delete records by
frmADO.PC.execute "DELETE * FROM tblWhatever"

Never delete all records (or do any other function) in a loop unless there is no way you can do it otherwise, which is hardly ever.

The other method would be to drop the table and then recreate it, and the indices, but this is often not convenient.

MrVB50au. If you can't start up with an empty table, I would suggest you've coded something a bit wrong.

 
Upvote 0 Downvote
Okay, it looks like where I was going wrong was using an actual ADO control (frmADO.PC) on a form and doing everything with it. When I was trying to test .Execute with it, it was telling me Method or Data Member not found. I decided to remove my actual ADO control and go with code (DBConn As ADODB.Connection). When I did that, the Execute command worked perfectly and fast (compared to a loop function). It also fixed my problem of deleting records that have 160 Fields as well. If I can get code to keep my Access DB conpact, then my problems seem to have been fixed. Thank you everyone for your help.
 
Upvote 0 Downvote
I use this. You need to add a reference to Jet and Replication Objects library, and make sure the mdb is not open whilst compacting. I also have a pile of code to copy the mdb before and after and an On Error of course.

Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine

sSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cInPath
sDestDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cOutPath
JRO.CompactDatabase sSourceDB, sDestDB & ";Jet OLEDB:Engine Type=4"


 
Upvote 0 Downvote
bdavis96,

Use the database "Close" command in your code before compacting.

petermeachem You seem to know more about it so I'll step aside. You were right, it's fixed now. Thanks

 
Upvote 0 Downvote
This is kinda new territory for me since I first learned to use a ADODC control and do everything with that. I have about 20 tables in one Access file that I step through to get data. Let me know if this theory looks okay:

Dim PC As ADODB.Connection
Dim PC_RS AS ADODB.Recordset
Set PC As New ADODB.Connection
PC.Open = "Driver={Microsoft Access Driver (*.mdb)};" & "Dbq=C:\myDB.mdb;"
(I only need to do Set PC once while I am stepping through everything, correct?)
Set PC_RS As New ADODB.Recordset
PC_RS.Open "Select * From myTable1"
Do Stuff with myTable1
PC_RS.Close
PC_RS.Open "Select * From myTable2"
Do Stuff with myTable2
PC_RS.Close
(I can repeat that process through all the tables I have, correct?)
PC.Close

When I want to close my program, do I need to Set PC or PC_RS to Nothing or Null or whatever? Or will VB do it automatically for me?
 
Upvote 0 Downvote
In general, just open the connection once when the programme starts and close it when it stops.

After PC_RS.Open
You should do something like
if not ( PC_RS.eof and PC_RS.bof ) then
do something
endif

unless the do something is an addnew.

Aside from that you are spot on.

I have only ever used the ADODC control once. Couldn't fathom out how to make it work sensibly.

You are supposed to set PC and PC_RS to nothing when you have closed them. I don't bother, and no harm has come of it. For that matter you aren't supposed to close a programme by using end for some reason I don't follow. I always have used End.



 
Upvote 0 Downvote
Thank you for all your help. It is amazing the speed difference between ADODC with delete loops and ADODB with .execute "Delete From myTable". With an ADODC and loops, it was usually taking around 30-40 seconds to go through 23 tables, delete all the data, and repopulate the table with new data. At 20+ seconds, I start to say "Ummm....Is it working?" With ADODB, it takes around 10 seconds to do everything.
 
Upvote 0 Downvote
bdavis96,

I guess you're planning of using the database app for your own personal use.
If not, you need to look at some issues in your code above.

petermeachem, I'm suprised to see you've missed them.

good luck bdavis96.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
UPDATE DAO RECORDSET 1
Replies
11
Views
240
strongm
strongm
sal21
  • Locked
  • Question
fill array from recordset
Replies
6
Views
282
2009luca
2009luca
Franka71
  • Locked
  • Question
Connecting to a Disconnected Recordset...
Replies
1
Views
106
Andrzejek
Andrzejek
kskinne
  • Locked
  • Question
help with code used to update multiple records from datagrid 1
2
Replies
33
Views
457
Andrzejek
Andrzejek
vladk
  • Locked
  • Question
How to convert SqlDataReader to a Recordset in VB.NET
Replies
2
Views
134
vladk
vladk

Part and Inventory Search

Sponsor

Back
Top