Writing to an Access Database RUNTIME ERROR 3251

[PSUIVERSON]

I am trying to write a variable to an ACCESS database. I setup the ODBC Connection without problem and part of the app already pulls in data from the database etc. However when I run the app and attempt to write to an ACCESS Table from the VB application I get a RUNTIME ERROR '3251'

"The operation requested by the application is not supported by the provider"

Here is my code:

...[Prior does other functions and captures the variable]...

Dim adoConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Dim connectString As String

' Create a new connection
Set adoConnection = New ADODB.Connection

' Creat a new Recordset
Set rst = New ADODB.Recordset

' Build connection string
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FFL\EY2003.mdb"

adoConnection.Open connectString

' Open Table and write the player to the team

rst.Open "Master_Teams", adoConnection

rst.MoveFirst
Do While Not rst.EOF
If IsNull(rst!Team1) Then
rst!Team1 = playerSelected ERROR HERE!
GoTo closeRecords:
End If
rst.MoveNext
Loop

closeRecords:

rst.Close
Set rst = Nothing

getout:

End Sub

 

[TomKane]

I don't think you can work it like that. I think you're either goinf to vave to do something like:

rst.Edit
rst!Team1 = PlayerSelected
rst.Update

Or you could issue a sql update command

 

[PSUIVERSON]

It does not allow me to choose the .EDIT method. The only method I am allowed is .EditMode and that doesn't work. That's how I thought it should work as well and it did not.

I'm baffled as to why .EDIT method is not there as well.

Thanks...

 

[TomKane]

You could always try the sql update. As to why it's not working? I don't do the .edit thing myself in ado but perhaps it's the connection string you can specify the locking method and things like that - perhaps you have the table open in read only mode?

 

[PSUIVERSON]

Thanks. Yeah - I usually write VBA in access and have no issues writing to the tables in ActiveDB. EditMode property maybe telling me something. Perhaps it's read-only and I need to set that property.

I am not adept using the SQL Update in code. A quick tip or link to a tutorial would be appreciated.

 

[PSUIVERSON]

Resolved it. I wasn't setting the SORTYPE, LOCKTYPE and OPTIONS settings (SEE BOLDED) , obviously not allowing me to write. Makes sense. Thanks for your help.

' Once player selected has been aproved - Add to the ACCESS Database to keep track

Dim adoConnection As ADODB.Connection
Dim rst As ADODB.Recordset
Dim connectString As String
Dim fld As ADODB.Field

' Create a new connection
Set adoConnection = New ADODB.Connection

' Creat a new Recordset
Set rst = New ADODB.Recordset

' Build connection string
connectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=EY2003FFL"

adoConnection.Open connectString

' Open Table and write the player to the team

rst.Open "Master_Teams", adoConnection, adOpenDynamic, adLockOptimistic, adCmdTable

rst.MoveFirst
Do While Not rst.EOF
If IsNull(rst!Team1) Then
rst!Team1 = playerSelected
rst.Update
GoTo closeRecords:
End If
rst.MoveNext
Loop

closeRecords:

rst.Close
Set rst = Nothing

getout:

 

[CCLINT]

Be advised that a adOpenDynamic cursor uses alot more resources and creates extra net traffic.
If the db is local, then no problem.
But, unless you really really really need a dynamic cursor, use a adOpenKeyset cursor instead...

 

[PSUIVERSON]

Thanks for the heads up. It's a local database on my C:
But I am advised and appreciate the head's up. I will read up on the different cursor types for future reference.