Deleting a recordset in Access database

  • Thread starter Thread starter iris
  • Start date Start date
I

iris

I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
 
I don't know how to translate it - but its error 3251

Danny J. Lesandrini said:
What is the error message?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


iris said:
I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
 
On Sun, 15 Feb 2009 11:17:01 -0800, iris

You are opening a Shapshot-type recordset, and those are ALWAYS
readonly. Rather open a Dynaset-type using dbOpenDynaset.

You can write a lot less code if you invoke a Delete query instead.

Another problem with your code is that you load ALL records from your
table whereas you only want to delete a single one (or possibly a
few). You should really add a WHERE clause to your sql statement
limiting the recordset to the interesting rows.

I'm puzzled by your Do Loop. If I read it correctly you are looping
over all records, checking each one to see if your combobox value is
the same as the "heb" field, and then ask if user wants to delete that
one. If yes, delete, loop around, and possibly find another one.
Since the user can't know which row we are on (you are not displaying
that information, and you don't have an ORDER BY clause) how would she
know which row to delete?

You are also exiting the loop after the first No. Presumably if user
says Yes we go find the next one, but if one No is issued that's it.
Very curious. Are you sure a delete query removing all heb's would not
be better?

-Tom.
Microsoft Access MVP
 
I want to delete only the recordset that is = to combobox2.text....



Danny J. Lesandrini said:
What is the error message?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


iris said:
I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
 
The error message might map to this ...

Object or provider is not capable of
performing requested operation

Here's my guess. You have an Access XP database, which sets the ADO library
by default. You added the DAO library afterwards, but the ADO library shows up
higher in the references list, so this declaration ...

Dim rs As Recordset

is being interpreted as
Dim rs As ADODB.Recordset

Try doing an explicit declaration, or removing the ADO library reference.
Change the recordset declaration to this and try again ...

Dim rs As DAO.Recordset
 
Identifying the row isn't the problem. Your code will work, though there's a better way.

Dim sSQL As String

sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

If the field [heb] is a text field, you need to delimit the combo box text with single quotes.
If it's a date, delimit it with Pound Signs (#).

This removes the need to loop through records and simply deletes the desired row,
provided the user has OKd it.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


iris said:
I want to delete only the recordset that is = to combobox2.text....



Danny J. Lesandrini said:
What is the error message?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


iris said:
I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
Click to expand...
 
On Sun, 15 Feb 2009 12:38:14 -0700, "Danny J. Lesandrini"

Not in this case, because OpenDatabase is only valid for DAO.
-Tom.
 
All of you are right about the looping problem...

actually, their can be only one correct value in "heb" - so there is no need
to loop through all the database...

I will try your suggestion with the
sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

and update U.

Thank You!

Tom van Stiphout said:
On Sun, 15 Feb 2009 11:17:01 -0800, iris

You are opening a Shapshot-type recordset, and those are ALWAYS
readonly. Rather open a Dynaset-type using dbOpenDynaset.

You can write a lot less code if you invoke a Delete query instead.

Another problem with your code is that you load ALL records from your
table whereas you only want to delete a single one (or possibly a
few). You should really add a WHERE clause to your sql statement
limiting the recordset to the interesting rows.

I'm puzzled by your Do Loop. If I read it correctly you are looping
over all records, checking each one to see if your combobox value is
the same as the "heb" field, and then ask if user wants to delete that
one. If yes, delete, loop around, and possibly find another one.
Since the user can't know which row we are on (you are not displaying
that information, and you don't have an ORDER BY clause) how would she
know which row to delete?

You are also exiting the loop after the first No. Presumably if user
says Yes we go find the next one, but if one No is issued that's it.
Very curious. Are you sure a delete query removing all heb's would not
be better?

-Tom.
Microsoft Access MVP


I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
 
when I write this code:

Set dbDatabase = OpenDatabase("C:\nihul.mdb")
Set rsp = dbDatabase.OpenRecordset("SELECT * FROM expressions WHERE heb Like
'" & ComboBox2.Text & "';", dbOpenSnapshot)

Dim eStr As String
Dim answer As String
d = False
e = 0
Dim sSQL As String

sSQL = DELETE from expressions WHERE heb = '" & ComboBox2.Text & "'
dbDatabase.Execute sSQL


I get an error:

compile error
expected: end of statement

I must be doing this wrong!

Danny J. Lesandrini said:
Identifying the row isn't the problem. Your code will work, though there's a better way.

Dim sSQL As String

sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

If the field [heb] is a text field, you need to delimit the combo box text with single quotes.
If it's a date, delimit it with Pound Signs (#).

This removes the need to loop through records and simply deletes the desired row,
provided the user has OKd it.

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


iris said:
I want to delete only the recordset that is = to combobox2.text....



Danny J. Lesandrini said:
What is the error message?

--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
Click to expand...
 
sSQL = DELETE from expressions WHERE heb = '" & ComboBox2.Text & "'
dbDatabase.Execute sSQL


I get an error:

compile error
expected: end of statement
Click to expand...

You need a double quote at the beginning of the SQL string:

sSQL = "DELETE from expressions WHERE heb='" & ComboBox2.Text & "'
 
On Sun, 15 Feb 2009 12:24:00 -0800, iris

Good for you.
Not to pile it on, but if "heb" is unique, be sure to have a Unique
Index on that field.

-Tom.
Microsoft Access MVP

All of you are right about the looping problem...

actually, their can be only one correct value in "heb" - so there is no need
to loop through all the database...

I will try your suggestion with the
sSQL = DELETE FROM expressions WHERE heb = " & ComboBox2.Text
dbDatabase.Execute sSQL

and update U.

Thank You!

Tom van Stiphout said:
On Sun, 15 Feb 2009 11:17:01 -0800, iris

You are opening a Shapshot-type recordset, and those are ALWAYS
readonly. Rather open a Dynaset-type using dbOpenDynaset.

You can write a lot less code if you invoke a Delete query instead.

Another problem with your code is that you load ALL records from your
table whereas you only want to delete a single one (or possibly a
few). You should really add a WHERE clause to your sql statement
limiting the recordset to the interesting rows.

I'm puzzled by your Do Loop. If I read it correctly you are looping
over all records, checking each one to see if your combobox value is
the same as the "heb" field, and then ask if user wants to delete that
one. If yes, delete, loop around, and possibly find another one.
Since the user can't know which row we are on (you are not displaying
that information, and you don't have an ORDER BY clause) how would she
know which row to delete?

You are also exiting the loop after the first No. Presumably if user
says Yes we go find the next one, but if one No is issued that's it.
Very curious. Are you sure a delete query removing all heb's would not
be better?

-Tom.
Microsoft Access MVP


I am trying to delete a record from an access database.

This code is written in a word userform...

Dim dbDatabase As Database
Dim rs As Recordset
Dim e As Integer
Dim d As Boolean

Set dbDatabase = OpenDatabase("C:\mydata1.mdb")
Set rs = dbDatabase.OpenRecordset("SELECT * FROM expressions ORDER by heb;",
dbOpenSnapshot)
Dim eStr As String
Dim answer As String
d = False
e = 0

With rs
Do Until .EOF
If ComboBox2.Text = ![heb] Then
answer = MsgBox("Do you want to delete this record: " &
ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
answer = MsgBox("Are you shure you want to delete
this record: " & ComboBox2.Text, vbQuestion + vbYesNo)
If answer = vbNo Then
Exit Sub
Else
rs.Delete
MsgBox "the record " & ![heb] & " has been
deleted"
End If
End If
End If
.MoveNext
e = e + 1
Loop
End With
rs.Close
dbDatabase.Close



I get an error the rs.delete....

Can someone tell me what I am doing wrong please?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top