By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,953 Members | 1,143 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,953 IT Pros & Developers. It's quick & easy.

Current Recordset does not support updating

P: 88
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?
Sep 3 '07 #1
Share this Question
Share on Google+
9 Replies


QVeen72
Expert 100+
P: 1,445
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
Sep 3 '07 #2

P: 88
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
Sep 3 '07 #3

QVeen72
Expert 100+
P: 1,445
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
Sep 3 '07 #4

QVeen72
Expert 100+
P: 1,445
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
Sep 3 '07 #5

P: 88
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.
Sep 3 '07 #6

P: 88
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.
Sep 4 '07 #7

QVeen72
Expert 100+
P: 1,445
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
Sep 4 '07 #8

P: 88
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?
Sep 4 '07 #9

P: 1
Download this instructions
hope it will help you
Download Link
Jun 19 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.