Connecting Tech Pros Worldwide Help | Site Map

Current Recordset does not support updating

  #1  
Old September 3rd, 2007, 09:50 AM
Member
 
Join Date: Aug 2007
Posts: 89
Hi,

I get the following error on the last line:

Run-time error '3251':

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Expand|Select|Wrap|Line Numbers
  1.     ' Open database connection
  2.     Set adoConn = CreateObject("ADODB.Connection")
  3.     adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
  4.     adoConn.Open
  5.  
  6.     ' Open recordset
  7.     Set adoRecSet = New ADODB.Recordset
  8.     adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenKeyset, adLockOptimistic
  9.  
  10. adoRecSet.MoveLast
  11. adoRecSet.AddNew
I did some searches in this forum, but setting the locktype to adLockOptimistic apparently does not help... What do I do wrong with this one?
  #2  
Old September 3rd, 2007, 09:56 AM
QVeen72's Avatar
Moderator
 
Join Date: Oct 2006
Location: Bangalore
Posts: 1,384

re: Current Recordset does not support updating


Hi,

Change this :


Expand|Select|Wrap|Line Numbers
  1. adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
  2.  
Change KeySet to Dynamic..

REgards
Veena
  #3  
Old September 3rd, 2007, 09:58 AM
Member
 
Join Date: Aug 2007
Posts: 89

re: Current Recordset does not support updating


Quote:
Originally Posted by QVeen72
Hi,

Change this :


Expand|Select|Wrap|Line Numbers
  1. adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
  2.  
Change KeySet to Dynamic..

REgards
Veena
I did, but still get the same error... :S
  #4  
Old September 3rd, 2007, 10:15 AM
QVeen72's Avatar
Moderator
 
Join Date: Oct 2006
Location: Bangalore
Posts: 1,384

re: Current Recordset does not support updating


Quote:
Originally Posted by BlackMustard
Hi,

I get the following error on the last line:

Run-time error '3251':

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

Expand|Select|Wrap|Line Numbers
  1.     ' Open database connection
  2.     Set adoConn = CreateObject("ADODB.Connection")
  3.     adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
  4.     adoConn.Open
  5.  
  6.     ' Open recordset
  7.     Set adoRecSet = New ADODB.Recordset
  8.     adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenKeyset, adLockOptimistic
  9.  
  10. adoRecSet.MoveLast
  11. adoRecSet.AddNew
I did some searches in this forum, but setting the locktype to adLockOptimistic apparently does not help... What do I do wrong with this one?
Hi,

Why are u "Late Binding" the Object by creating...?

Directly Declare :
Dim adoConn As New ADODB.Connection
Dim adoRecSet As New ADODB.RecordSet

And Check "strArchivePath" Should have last Char ="\" some thing like :
C:\MyFolder\Data\

Regards
Veena
  #5  
Old September 3rd, 2007, 10:45 AM
QVeen72's Avatar
Moderator
 
Join Date: Oct 2006
Location: Bangalore
Posts: 1,384

re: Current Recordset does not support updating


Hi,

What Version of ADO Objects u have Included in ur proj..?

It Should be :
"Microsoft ActiveX Data Objects 2.0 Library"

or Higher..

REgards
Veena
  #6  
Old September 3rd, 2007, 11:49 AM
Member
 
Join Date: Aug 2007
Posts: 89

re: Current Recordset does not support updating


I have included a library called "Microsoft ActiveX Data Objects 2.7 Library", and I changed the declarations in (General) to the following:

Expand|Select|Wrap|Line Numbers
  1. Dim adoConn As New ADODB.Connection
  2. Dim adoRecSet As New ADODB.RecordSet
In my code, i then removed the "Set" commands, leaving the code at

Expand|Select|Wrap|Line Numbers
  1.     ' Open database connection
  2.     adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
  3.     adoConn.Open
  4.  
  5.     ' Open recordset
  6.     adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
  7.  
  8.     ' Add record
  9.     adoRecSet.MoveLast
  10.     adoRecSet.AddNew
I still get the same error...

EDIT:
strArchivePath is previously set directly in code, and does end with a "\". I use a variable just to make it easier to change if i want to use this sub somewhere else.
  #7  
Old September 4th, 2007, 09:34 AM
Member
 
Join Date: Aug 2007
Posts: 89

re: Current Recordset does not support updating


I found the error - I was using the same recordset in another function, so the adoRecSet variable didn't contain what i thought it did. Problem solved by using a separate recordset.
  #8  
Old September 4th, 2007, 09:37 AM
QVeen72's Avatar
Moderator
 
Join Date: Oct 2006
Location: Bangalore
Posts: 1,384

re: Current Recordset does not support updating


Hi,

Good u solved, if that was the Case, then u can overcome that problem, by Setting the Recset = Nothing before opening it..:

Set RS =Nothing
and then Open :
RS.Open strSQL, AConn

REgards
Veena
  #9  
Old September 4th, 2007, 03:10 PM
Member
 
Join Date: Aug 2007
Posts: 89

re: Current Recordset does not support updating


Quote:
Originally Posted by QVeen72
Hi,

Good u solved, if that was the Case, then u can overcome that problem, by Setting the Recset = Nothing before opening it..:

Set RS =Nothing
and then Open :
RS.Open strSQL, AConn

REgards
Veena
Well, now i've already emailed the macro out to the entire department =) But this is worth remembering for next time... Does it optimize the prestanda compared to using several different recordsets?
  #10  
Old June 19th, 2009, 04:17 AM
Newbie
 
Join Date: Jun 2009
Posts: 1

re: Current Recordset does not support updating


Download this instructions
hope it will help you
Download Link
Reply


Similar Threads
Thread Thread Starter Forum Replies Last Post
Current Recrdset does not support updating jamesnkk answers 8 December 26th, 2006 04:11 AM
Current Recordset does not support updating - HELP needed! Hexman answers 5 February 24th, 2006 09:15 AM
Current Recordset does not support updating. barret bonden answers 2 December 13th, 2005 06:05 AM
Problem filling an ADO recordset from SQL Server msprygada answers 5 November 13th, 2005 06:23 AM