"Runtime error 3251" "Current Recordset does not support updating. This may be a limitation of the provider or of the selected locktype."

Posted on 2005-04-11
Medium Priority
Last Modified: 2008-01-09
My client called today to report the following run time error:

"Runtime error 3251" "Current Recordset does not support updating. This may be a limitation of the provider or of the selected locktype."

This error occurs in several different VB6 programs that have been working perfectly for several months.  The difference is that the client changed the SQL Server 2000 recovery model for the database from "Simple" to "Full" recovery so that transactions will be logged.

Nothing was changed in the VB code.

The programs all open SQL Server 2000 ADODB recordsets with adOpenDynamic and adLockOptimistic, so updating is supported.

Anyone have any idea why changing the SQL Server receovery model from "simple" to "full" might have triggered these errors?
Question by:portdata
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 13759337
hi try these settings
what is the cursorlocation and type u r using ?
      Dim objRec As New ADODB.Recordset

      objRec.CursorLocation = adUseClient
      objRec.CursorType = adOpenDynamic
      objRec.LockType = adLockPessimistic

hope this will help u


LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 13759349
if this doesn't seem to work, can u post the connection string u r trying

Author Comment

ID: 13761124

Thanks for the suggestion.

However, I am not interested in changing the code - it has been working perfectly for a year or more.

The problem is that it stopped working when the SQL Server 2000 database recovery model was changed from "simple" (no transaction logging) to "full: (full transaction logging) on the database.

What I need to know is "why" did this error start to appear after the change was made.

Art Misita
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 70

Expert Comment

by:Éric Moreau
ID: 13761205
I have never seen that changing the recovery model had effects on application! There is surely something else that changed! Is the database is marked as read-only?

Author Comment

ID: 13765460
I solved the problem myself.

It turns out that when the client imported the table that is triggering the error, they did not redefine the two indexes that wewre originally defined on that table.

I defined the indexes and the problem disappeared.

This is actually kind of strange since indexes are "optional" in SQL Server.

I am requesting that this question be withdrawn as "self solved'.

Accepted Solution

PashaMod earned 0 total points
ID: 13800063
Closed, 500 points refunded.
Community Support Moderator @ Experts Exchange

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question