Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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] Chris Dunigan wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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/de...daenumac_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 "Chris Dunigan" wrote in message om... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
Jake or Robin,
I posted a similar question a while back. How can I delete all records in a table? Thanks, Donnie "Robin Hammond" wrote in message ... 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/de...daenumac_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 "Chris Dunigan" wrote in message om... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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. -- "Donnie Stone" wrote in message ... Jake or Robin, I posted a similar question a while back. How can I delete all records in a table? Thanks, Donnie "Robin Hammond" wrote in message ... 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/de...daenumac_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 "Chris Dunigan" wrote in message om... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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 "onedaywhen" wrote in message m... 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. -- "Donnie Stone" wrote in message ... Jake or Robin, I posted a similar question a while back. How can I delete all records in a table? Thanks, Donnie "Robin Hammond" wrote in message ... 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/de...daenumac_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 "Chris Dunigan" wrote in message om... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting records from access using ADO in through Excel
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] Chris Dunigan wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting NEARLY multiple records in excel | Excel Discussion (Misc queries) | |||
Import Access records into Excel | Excel Discussion (Misc queries) | |||
How do I display duplicate records in Excel without deleting? | Excel Worksheet Functions | |||
Linking Access Records to Excel | Excel Worksheet Functions | |||
Access records updating from Excel. | Excel Programming |