Current Recordset does not support updating

[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.

    ' Open database connection
    Set adoConn = CreateObject("ADODB.Connection")
    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
    adoConn.Open
 
    ' Open recordset
    Set adoRecSet = New ADODB.Recordset
    adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenKeyset, adLockOptimistic
 
adoRecSet.MoveLast
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?

[QVeen72]

Hi,

Change this :

adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
 

Change KeySet to Dynamic..

REgards
Veena

[BlackMustard]

Hi,

Change this :

adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
 

Change KeySet to Dynamic..

REgards
Veena

I did, but still get the same error... :S

[QVeen72]

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.

    ' Open database connection
    Set adoConn = CreateObject("ADODB.Connection")
    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
    adoConn.Open
 
    ' Open recordset
    Set adoRecSet = New ADODB.Recordset
    adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenKeyset, adLockOptimistic
 
adoRecSet.MoveLast
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

[QVeen72]

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

[BlackMustard]

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

Dim adoConn As New ADODB.Connection
Dim adoRecSet As New ADODB.RecordSet

In my code, i then removed the "Set" commands, leaving the code at

    ' Open database connection
    adoConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & strArchivePath & "mailarchive.mdb" & ";User ID=Admin;Password=;"
    adoConn.Open
 
    ' Open recordset
    adoRecSet.Open "SELECT * FROM tblEmail ORDER BY ISS ASC", adoConn, adOpenDynamic, adLockOptimistic
 
    ' Add record
    adoRecSet.MoveLast
    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.

[BlackMustard]

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.

[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

[BlackMustard]

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?

[lenard2500]

Download this instructions
hope it will help you
Download Link