Deleting records from access using ADO in through Excel

  • Thread starter Thread starter Chris Dunigan
  • Start date Start date
C

Chris Dunigan

Hi,

I would like to be able to delete multiple records in Access from a
procedure in Excel.

At the moment i have a macro set up in Excel that can look at my
database and tell me if there are any records in it that have "Month =
7". I'd like to have a pop-up box in Excel that then asks if the user
would like to delete all records from the database that match the
criteri "Month = 7".

My code looks like this:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=G:\test.mdb"
rs.Open "TestTable", cn, adOpenKeyset, adLockOptimistic,
adCmdTable
rs.Find ("Month = '7'")
If rs.AbsolutePosition > 0 Then
Test = MsgBox "TestMessage",vbYesNo, "Warning")
End If
If Test = vbYes Then
rs.Filter = "Month = '7'"
rs.Delete
rs.Close
End If

---------
At the moment this code does nearly all i want it to do, except when i
get to the rs.Delete line. It only deletes one record, and not all
the records that have been found using the rs.Filter line.

Can anyone tell me how i can get the macro to delete every record
brought back from the rs.Filter line.

Many thanks in advance

Chris
 
Hi Chris,

Instead of opening the data set and finding/deleting each record that
matches, you can execute one SQL statement against the Connection object.
This will be faster and easier for you.

Here's an (untested) example:

Dim sSQL As String
Dim lRecordsDeleted As Long

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=G:\test.mdb"

sSQL="DELETE FROM TestTable WHERE [Month]='7'"

cn.Execute sSQL, lRecordsDeleted, adCmdText

MsgBox CStr(lRecordsDeleted) & " records were deleted."

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
I don't know about other users but I have found that the filter command does
not work reliably in Excel using the Jet provider.

You could try using the adaffectgroup option in your delete statement

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdaenumac_5.asp

However, I'd suggest deleting the records one at a time. Unless you have a
huge amount of data it should still be quite quick.

Something like this (untested)

with rs
.movefirst
Do while not .eof
if .fields("Month").value = 7 then
.delete adaffectcurrent
else
.movenext
end if
loop
.updatebatch
end with

Robin Hammond
www.enhanceddatasystems.com
 
Jake or Robin,

I posted a similar question a while back. How can I delete all records in a
table?

Thanks,
Donnie
 
Neither the SQL DELETE keyword nor the recordset's Delete method is
supported for Excel. The nearest you can get is to SELECT..INTO a new
table/worksheet the rows/columns you want to keep and issue a DROP
TABLE for the original. DROP TABLE merely clears the sheet; the
worksheet is not removed from the workbook.

--
 
Maybe I made a mistake in terms of your system. However, I work a lot with
disconnected recordsets retrieved indirectly from a database via a web
server as an XML stream, and when using these the delete command works
perfectly well, with changes reflected in the db when the recordset is
passed back to the database via the web server with an updatebatch command.
I've had this working with both Access and SQL as the back end database
platform.

I would have thought that the fact there is a web server invovled is
irrelevant, but strangely I just tried to test it with a connected and a
disconnected recordset and Excel doesn't seem to like it. So, here's an
alternative solution. You effectively run an SQL command directly on the DB.
It's quite a neat trick, but be very careful to make sure you specify a
WHERE clause or you could rapidly delete all your data. Backup your db
before you try this please. Not perfect, but it should help. I think this
also answers the other question in Donnie's post.

Sub DeleteCommand()
'would take the same TSQL syntax as a normal SQL query
RunSQLCommand "DELETE FROM TestTable WHERE Month = '7'"
End Sub

Sub RunSQLCommand(strSQL As String)
Dim oConn As ADODB.Connection
Dim rsTemp As ADODB.Recordset

Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=E:\My Documents\Temp\db1.mdb;Persist Security Info=False"

Set rsTemp = New ADODB.Recordset
oConn.Open
rsTemp.Open strSQL, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText

Set rsTemp = Nothing
If oConn.State = adStateOpen Then oConn.Close
End Sub

Yours,

Robin Hammond
www.enhanceddatasystems.com
 
Thanks for all your help. All is becoming clearer!!
I am still having one problem that i wondered if you's help me with.

I have the following code:

-----
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=P:\test.msd"

If testcondition1=whatever then
rs.Open "Table1", cn, adOpenKeyset, adLockOptimistic, adCmdTable
sSQL = "DELETE FROM Table1 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If
rs.Close

If testcondition2=whatever then
rs.Open "Table2", cn, asOpenKeyset, adLockOptimistic, adCmdTable
sSQL = "DELETE FROM Table2 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If
rs.Close
-----

In the majority of cases i would expect both testcondition1 and
testcondition2 to be met, therefore there will be deletions from
Table1 an Table2. The code initially works, when testcondition1 is
met the data is deleted from Table1, however when the code gets down
to delete data from Table2 i get the following error:

----
Run-time error '-2147217913(80040e07)':
Data type mismatch in criteria expression
----

This error occurs when the line cn.Execute sSQL, lRecordset, adCmdText
is tried.

Do you have any ideas what is going wrong??

Many thanks in advance.
Chris
 
Hi Chris,

First of all, you don't need the Recordsets at all if you're using this
method to delete the records. The SQL statement is executed against the
Connection object, so opening the Recordset is unnecessary overhead. So you
can change your code to this:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=P:\test.msd"

If testcondition1=whatever then
sSQL = "DELETE FROM Table1 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If

If testcondition2=whatever then
sSQL = "DELETE FROM Table2 WHERE [Month]='7'"
cn.Execute sSQL, lRecordsDeleted, adCmdText
End If

'/ make sure you close your Connection object when you're done
cn.Close
Set cn = Nothing


The error message you're getting indicates that perhaps the Field "Month" in
Table2 is not a Character or Text field. Try it without the single quotes
to see if it works.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top