ADODB.recordset delete method does not work

[fenris]

I am have problems with ado and the delete method. This is the error that I am getting:
runtime error 3251
This operation is not supported by the provider

I can add records to the access 2000 DB but I can't delete them. Everything worked fine with DAO but I wanted to try out ADO and I can't seem to get the delete to work.

The first sub is used to create open a Database and the second function is used to remove a record. Note: both of these subs are encapsulated into a class So DB is a class variable.
Any ideas would be appreciated.


I have references to "Microsoft DAO 3.6 object library" and " Microsoft Activex Data Objects 2.0 library"


'==================
Public Sub openDB(Optional provider As String)
'make sure there is a path to the database
Dim fso As FileSystemObject
If DBName = "" Then
MsgBox "Please enter a path to the database before trying to open it!"
Exit Sub
End If

'check to make sure the database file exists
Set fso = New FileSystemObject
If Not fso.FileExists(DBName) Then
MsgBox "The Database does not exist!", vbCritical, "File Not Found"
Exit Sub
End If


Dim sProvider As String
sProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"

With DB
.Mode = adModeReadWrite
.ConnectionTimeout = 10
.CommandTimeout = 5
.CursorLocation = adUseClient
.Open sProvider & "Data Source=" & DBName & ";"
End With

End Sub

'=================
Public Sub rmNumber(ddate As Date)
Dim sql As String
Dim rs As ADODB.Recordset

sql = "SELECT * from LottoNumbers WHERE (Date = #" & ddate & "#)"

Set rs = New ADODB.Recordset
rs.Open sql, DB, , adCmdText
rs.Delete adAffectCurrent
rs.Update

'===================

Troy Williams B.Eng.
fenris@hotmail.com

 

[jeba]

I'm having a lot of pain going from DAO to ADO also.

You need to reference the following to use ADO. From what I got from the MS knowledge base.

Microsoft ActiveX data objects 2.5 Library
Microsoft Jet and Replication objects 2.5 Library
and
Microsoft ADO ext 2.5 for DDL and security

Here's a sample connect string.

AccessConnect = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"Extended Properties=" & """" & _
"DSN=MS Access Database;" & _
"DBQ=" & DbFileName & ";" & _
"DefaultDir=" & ";" & _
"UID=admin;PWD=;" & _
"DriverId=25;FIL=MS Access;" & """"

I have no problems deleting, but I can't get the error trapping to work. When errors occur the on error resume next doesn't "resume next" it dies. I'll let you know when I figure that one out.

You can also use the jet 4.0 but it has OTHER problems. I think your main problem is the reference to DAO 3.60

Jim

 

[fenris]

Thank you for your response...



Troy Williams B.Eng.
fenris@hotmail.com

 

[jeba]

The connect string I gave you uses ODBC. If you want to use only OLEDB then change the connect string to:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=pathtoyourDB"
as you already had.

The main item is to point to the activex data objects 2.1 or higher in the "project references"

Sorry for the confusion.