By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

delete records in adodb record set

P: 86
Hi all, i am using ms access 2003,

i have a form,
i connect to the mysql database using adodb connection.
this works fine.

but when i delete a record, it is deleted but still the record set shows that record.
when i close the application and again run it, then the record set dosnt show the delted record.

what could be the problem

my code goes like this
rsInfotable is a adodb record set

i opened the record set with the code
Expand|Select|Wrap|Line Numbers
  1.  str = "select * from infotable;"
  2. rsInfotable.Open str, objConn, adOpenKeyset, adLockOptimistic

Expand|Select|Wrap|Line Numbers
  2. If txtAutonumber.Visible Then
  6.             rsInfotable.Delete
  9.             MsgBox ("Record has been successfully Deleted")
  10.             rsInfotable.MoveNext
  13.         If rsInfotable.EOF And rsInfotable.BOF Then
  14.             MsgBox ("No more records")
  15.             Call DiableButtons
  16.             Exit Sub
  18.         ElseIf rsInfotable.EOF Then
  19.             rsInfotable.MoveLast
  20.              Call DisplayData
  23.         End If
  24.     End If

though i do it with rsInfotable.delete it still shows the record in the record set
Feb 25 '09 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 634

I am not sure how this deleted record shows its self in the procedure (ie. what is the problem?), but just perhaps you could change this

If rsInfotable.EOF And rsInfotable.BOF Then

to this

If rsInfotable.RecordCount = 0 Then


Feb 25 '09 #2

P: 86
my application target is very simple that
1. connect to the database
2. add records,
3. edit records,
4. navigate through records
5. and delete records.

i am doing all these fine except that deleting record.

when i delete with my code, the records are deleted in the database, but the record set still contains that record.

suppose say, i have 20 records, and now i am at say 8th record, now i pressed delete button and i perform a movenext action. The record is deleted in database, but when i press previous button or like that, i am still able to see that record in my recordset. I.e i am able to navigate through the deleted record which is wrong.

do i need to requery the record set?
if i requery the recordset, then the current position will be lost which is not a correct approach.

what is happening with my code?

thank you
Feb 25 '09 #3

Expert 100+
P: 634
Hi again

Well, I cannot fine any think wrong with your code (except for to suggestion in my last post as an error occures when there are not records left!). Other than that I cannot see where this is a problem for you (ie where does this error/probelm occure?). Perhaps I am missing something simple.

Just one thought, deleted records do remain in a FORM until it is Requeried?

Feb 25 '09 #4

P: 86
Hi, i attached my application.

indeed there is no error coming.But functionality is wrong in my code.

have a look at the attachment.

Open the database,
1.Run the form
2. Click open connection button
3. navigate to some record, or go to final record
4. Now press delete. there comes confirmation message that record has been deleted.
5. now click previous button, or some how navigate through the records.
the deleted record still appears.

what should i do to delete from the record set

if you want to add records, click create record, enter the fields and click save button

Note:Please do not enter last field(long) while creating record. it wil not be saved, and gives error. there is some problem.

thank you
Attached Files
File Type: zip VBAAptitudeTest_JIVA (51.4 KB, 103 views)
Feb 25 '09 #5

Expert 100+
P: 634

Unfortunately IT policy does not permit me to down load files, so I cannot look at this, but hopefully someone else will. Paticularly as I cannot simulate your problem, the deleted record just disapear when the recordset is moved.

Sorry cannot be of more help.

Feb 25 '09 #6

Expert Mod 2.5K+
P: 2,545
Hi ravindarjobs. The form's code module in the example database does not compile - there is a type mismatch in the line

RecordNavigationControl6.ControlSource = rsInfotable

of your OpenConnection_Click sub. The controlsource cannot be a recordset type - it should be a tablename, query name or SQL statement of some kind. Anyway, it has nothing to do with the error being investigated.

Commenting that line out leads to another error - as we have no access to the data source that is still specified for your connection object. For test purposes you need some local data source that we can access too - such as a table within the current database.

You should make sure that any database you attach is free from errors before you attach it - encountering avoidable mistakes like this is not helpful to anyone who is spending time looking at your application...

Like MTB, I can see no obvious error in your delete routine. I wonder if you have the relevant permissions on the underlying table, however. Don't be fooled by the message saying the record is deleted - at least if what you are referring to is your own messagebox call saying the record is deleted - does not mean the system did delete the record.

I would suggest that you set up a test table within your application itself, open that table in the ADO recordset instead of the remote target, and verify as MTB has done that the code deletes a target record. If so, it is clearly a permissions issue that is preventing you from deleting the record in the remotely-connected data source.

Feb 25 '09 #7

Expert Mod 15k+
P: 31,419
I know MTB mentioned it earlier, but I'm wondering if you caught it, as I've seen no further mention of it. After deleting a record, the recordset keeps the record holder until it is requeried.

Either requery the recordset when necessary, or recode to handle this and skip past such records where found.
Feb 25 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.