runtime error 3251 "object or provider is not capable of performing requested.."

  • Thread starter Stephen Croce via AccessMonster.com
  • Start date
S

Stephen Croce via AccessMonster.com

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!
 
D

Dave Patrick

All air code but this should get you started.

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 rsADO = New 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 ID, PolicyID, EffDate , ExDate, Number, Year ,VehDescrip,
Vin FROM tblAutoSchedule; "

rsADO.Open , cnADO, adOpenDynamic, adLockOptimistic

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

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| 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!
|
| --
|
 
T

Tim Ferguson

Set rsADO = cnADO.Execute(strSQL)
Click to expand...

This is a legal method to open a recordset, but in your case it's not a
very good one since it looks as though it's returning a snapshot. You
need an updateable dynaset. Try looking up help for the Recordset.Open
method, and pay attention to all the arguments.

Secondly, I don't understand why you want to pull in all the records from
the table just in order to add one new one. If you have some attachment
to using .AddNew and .Update, then at least you can leave the old records
where they are with something like

SELECT ID, PolicyID, EffDate, Number, Year, VehDescrip, Vin
FROM tblAutoSchedule
WHERE FALSE

which won't lock the entire table against other users. It would be even
better to use a straightforward INSERT statement in the first place:

strSQL = "INSERT INTO tblAutoSchedule " & _
"(PolicyID, EffDate, ExDate, Number, Year, VehDescrip, Vin) " & _
"VALUES (" & _
strPolID & ", " & _
Format(datEffDate, c_strCorrectFormatForDatabase) & ", " & _
Format(datExDate, c_strCorrectFormatForDatabase) & ", " & _
intAutoNum & ", " & _
"""" & strVehDescrip & """, " & _
"""" & strVIN 7 """) "


Note that the date format is different for Jet and SQL Server (and
probably for other db engines too). If you want to remain friends with
your local db admin and network admin, then this is the sort of
interaction you need to get used to.

HTH


Tim F
 
Top