run time error 3251 - object or provider not capable of performing...

[scroce]

i've seen several posts on this issue, but often they are more complex that what I'm trying to achieve. many of them are calls from asp pages, but I am working right within my own access db (2002) environment.

The goal is to understand ADO in Jet environment, as I have plans to eventually work with connecting to larger databases. Thus I know I can workaround with DAO, but I don't want to

anyway, here is my code, which just takes info from textboxes on form and updates a table.

Public Sub addNewAuto()

   
Dim strSQL As String
Dim strPolID As String
Dim datEffDate As Date
Dim datExDate As Date
Dim intAutoNum As Integer
Dim strVehDescrip As String
Dim strVin As String

Dim cnADO As ADODB.Connection
Dim rsADO As ADODB.Recordset

Set cnADO = CurrentProject.Connection

strPolID = Me.txtPolID.Value
datEffDate = Nz(Me.txtEffDate.Value, Date)
datExDate = Nz(Me.txtExpDate.Value, Date)
intAutoNum = Me.txtVehicleNum.Value
strVehDescrip = Nz(Me.txtDescrip.Value, "")
strVin = Nz(Me.txtVin.Value, "")

strSQL = "SELECT tblAutoSchedule.ID, tblAutoSchedule.PolicyID, tblAutoSchedule.EffDate, "

strSQL = strSQL & "tblAutoSchedule.ExDate, tblAutoSchedule.Number, tblAutoSchedule.Year, "

strSQL = strSQL & "tblAutoSchedule.VehDescrip, tblAutoSchedule.Vin "

strSQL = strSQL & "FROM tblAutoSchedule;"

Set rsADO = cnADO.Execute(strSQL)

rsADO.AddNew
rsADO("PolicyID") = strPolID
rsADO("EffDate") = datEffDate
rsADO("ExDate") = datExDate
rsADO("Number") = intAutoNum
rsADO("VehDescrip") = strVehDescrip
rsADO("Vin") = strVin


rsADO.Update

End Sub

the culprit is addNew - can't get it to work! - it also could be that cnADO.Execute statement - I tried cnADO.Open -but it didn't work Can somebody help me with my syntax?

much obliged!



I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[KarMac21]

Try this:
Public Sub addNewAuto()

Dim strSQL As String
Dim strPolID As String
Dim datEffDate As Date
Dim datExDate As Date
Dim intAutoNum As Integer
Dim strVehDescrip As String
Dim strVin As String

Dim cnADO As ADODB.Connection
Dim rsADO As ADODB.Recordset

strPolID = Me.txtPolID.Value
datEffDate = Nz(Me.txtEffDate.Value, Date)
datExDate = Nz(Me.txtExpDate.Value, Date)
intAutoNum = Me.txtVehicleNum.Value
strVehDescrip = Nz(Me.txtDescrip.Value, "")
strVin = Nz(Me.txtVin.Value, "")

[red]rsADO.open "tblAutoSchedule", CurrentProject.connection, adOpenKeyset, adLockOptimistic, adCmdTable[/red]

rsADO.AddNew
  rsADO("PolicyID") = strPolID
  rsADO("EffDate") = datEffDate
  rsADO("ExDate") = datExDate
  rsADO("Number") = intAutoNum
  rsADO("VehDescrip") = strVehDescrip
  rsADO("Vin") = strVin
rsADO.Update

rsADO.close

End Sub

Hope this helps!

 

[RoyVidar]

Using .execute that way, gives you a readonly forwardonly recordset. If recordsetapproach is wanted, try:

[tt]set rs=new adodb.recordset
rs.open strsql,cn,adopenkeyset,adlockoptimistic,adcmdtext[/tt]

But in stead of recordsetapproaches, why not execute an append query?

Roy-Vidar

 

[scroce]

KarMac, and Roy

A few things

1. I came up with something that works - it may be overcomplicated. It is posted below
2. Since it may be overcomplicated, KarMac, I want to try your code modification to see if it worked, b/c it looks simple
3. Roy - yes, I could have done that, but I'm trying to understand ADO better than I do today

That all being said, here's my new code:

Public Sub addNewAuto()

   
Dim strSQL As String
Dim strPolID As String
Dim datEffDate As Date
Dim datExDate As Date
Dim intAutoNum As Integer
Dim strVehDescrip As String
Dim strVin As String
Dim cnString As String

Dim cnADO As New ADODB.Connection
Dim rsADO As New ADODB.Recordset

'Test Code
'Set cnADO = CurrentProject.Connection

'The connection string:
cnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=C:\myPath\myDatabase.mdb;"

'Open the string:
cnADO.Open (cnString)

'Set variables:
strPolID = Me.txtPolID.Value
datEffDate = Nz(Me.txtEffDate.Value, Date)
datExDate = Nz(Me.txtExpDate.Value, Date)
intAutoNum = Me.txtVehicleNum.Value
strVehDescrip = Nz(Me.txtDescrip.Value, "")
strVin = Nz(Me.txtVin.Value, "")

'pull the fields from the table we want to update:
strSQL = "SELECT tblAutoSchedule.ID, tblAutoSchedule.PolicyID, tblAutoSchedule.EffDate, "

strSQL = strSQL & "tblAutoSchedule.ExDate, tblAutoSchedule.Number, tblAutoSchedule.Year, "

strSQL = strSQL & "tblAutoSchedule.VehDescrip, tblAutoSchedule.Vin "

strSQL = strSQL & "FROM tblAutoSchedule;"

'Test Code
'Set rsADO = cnADO.Execute(strSQL)

rsADO.Open strSQL, cnADO, adOpenDynamic, adLockOptimistic

'Add the new record
rsADO.AddNew
rsADO("PolicyID") = strPolID
rsADO("EffDate") = datEffDate
rsADO("ExDate") = datExDate
rsADO("Number") = intAutoNum
rsADO("VehDescrip") = strVehDescrip
rsADO("Vin") = strVin

'update the recordset
rsADO.Update

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[KarMac21]

Hi Scroce,

There are definitely easier ways to do this, such as an append query or a Docmd.RunSQL/Docmd.Execute statement with an "INSERT INTO" query. I only use ADO connections when Updating/Inserting/Gathering information from other data sources, such as SQL Server, DB2 Databases or other ODBC sources.

I do use ADO recordsets within my databases alot, but that's just because that's what I'm comfortable working with. Good luck with your ADO exploration.

Shane

 

[scroce]

Yes, the easier way would probably be something like,

strSQL = "APPEND blah blah blah blah blah"

docmd.runSQL(strSQL)

I am a nobody, and nobody is perfect; therefore, I am perfect.

 

[RoyVidar]

[tt]strsql="insert into tblAutoSchedule " & _
"(PolicyID,EffDate,ExDate,Number,VehDescrip,Vin) " & _
"values (" & Me.txtPolID.Value & ", #" & _
Nz(Me.txtEffDate.Value, Date) & "#, #" & _
Nz(Me.txtExpDate.Value, Date) & "#, " & _
Me.txtVehicleNum.Value & ", '" & _
Nz(Me.txtDescrip.Value, "") & "'" & _
Nz(Me.txtVin.Value, "") & "')"
rsADO.execute strsql[/tt]

- though I'm not sure how much it likes the empty string, I'd probably

[tt] ...Nz(Me.txtVin.Value, "Null")...[/tt]

Roy-Vidar

 

[scroce]

guys,

You may be interested in reading a new thread I've started which is relevant to this thread, but asks a slightly different question. It has to do with ADO and requerying a listbox. It uses the same code as above.

Again, your comments would be highly valued if you would be kind enough to spare them.

thread705-1021287

thanks,

Steve

I am a nobody, and nobody is perfect; therefore, I am perfect.