CODEWITHSUNDEEP
excelms-accessvbscriptms-access-2007adodb
shresthaal
shresthaal

Reputation: 685

Microsoft.ACE.OLEDB.12.0 Current Recordset does not support updating error received when trying to update access

I have a ASP page where a user will upload a excel file. After the file is successfully uploaded I want to take the rows in the REGION tab and insert them into an MS Access 2007 table. Below is the code I used and I am getting this error. Can I use the recordset update with the Microsoft.ACE.OLEB.12.0 provider? Is there a better way to do this? ADODB.Recordset error '800a0cb3'

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

Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & strExcelFile & ";" & _
                    "Extended Properties=" &  Chr(34) & "Excel 12.0 Xml;HDR=Yes;IMEX=1" & Chr(34) & ";" 
Response.Write "Excel connection opened<BR>"

' Load ADO Recordset with Excel Data
Set rstExcel = Server.CreateObject("ADODB.Recordset")
rstExcel.Open "Select * from [REGION$]", cnnExcel, adOpenStatic 
Response.Write "Excel Recordset loaded<BR>"

' Open Access Connection
Set cnnAccess = Server.CreateObject("ADODB.Connection")
cnnAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strAccessFile & ";Persist Security Info=False;" 
Response.Write "Access connection opened<BR>"
Const adOpenStatic = 1
Const adLockOptimistic = 3
Const adCmdText = &H0001
' Load ADO Recordset with Access Data
Set rstAccess = Server.CreateObject("ADODB.Recordset")
rstAccess.Open "REGION", cnnAccess, adOpenStatic, adLockOptimistic, adCmdTable
Response.Write "Access Recordset loaded<BR>"

' Synchronize Recordsets and Batch Update
Do Until rstExcel.EOF

        ' .AddNew
        For each field in rstExcel.Fields
            If field.Name = "% Over/Under" Then
                rstAccess.AddNew field.Name,0
            Else
                rstAccess.AddNew field.Name,field.Value
            End If
        Next

    rstExcel.MoveNext
Loop
rstAccess.UpdateBatch

Upvotes: 2

Views: 7998

Answers (3)

shresthaal
shresthaal

Reputation: 685

Thank you Hans, Tim and Remou. I followed this example Using INSERT INTO to write data into access database

  ' Open Access Connection
Set cnnAccess = Server.CreateObject("ADODB.Connection")
cnnAccess.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strAccessFilePath & ";Persist Security Info=False;"   
cnnAccess.Execute "DELETE * FROM REGION"
'Open Excel Connection
Set cnnExcel = Server.CreateObject("ADODB.Connection")
cnnExcel.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                "Data Source=" & strExcelFilePath & ";" & _
                    "Extended Properties=" &  Chr(34) & "Excel 12.0 Xml;HDR=Yes;IMEX=1" & Chr(34) & ";" 
Response.Write "Excel connection opened<BR>"
cnnExcel.Execute "INSERT INTO REGION IN 'C:\inetpub\wwwroot\FolderName\App_Data\AccessFileName.accdb' " & _
                "Select col1,col2,col3,col4" & _
               "from [REGION$]"


' Clean up    
cnnExcel.Close
Set cnnExcel = Nothing
cnnAccess.Close
Set cnnAccess = Nothing

Upvotes: 3

HansUp
HansUp

Reputation: 97100

You said "... I want to take the rows in the REGION tab and insert them into an MS Access 2007 table".

Although I don't fully understand your sample code, you seem to be opening one recordset for the Access table and another for the Excel worksheet, then moving through the Excel recordset and inserting (something for) each row into the Access recordset.

If the Access recordset was editable (ie not read-only), you could insert something. However, even if you got it working, you would still be left with a RBAR (Row By Agonizing Row) approach. It would be better to use a set-based approach so you import all the worksheet rows as a single operation.

Here is an Access query which mimics what I think you're trying to do. My Region worksheet has 4 columns of data: % Over/Under; fld2; fld3; and fld4. The Access REGION table has those same 4 columns set up with compatible data types.

INSERT INTO REGION ( [% Over/Under], fld2, fld3, fld4 )
SELECT 0 AS [% Over/Under], fld2, fld3, fld4
FROM [Excel 12.0 Xml;HDR=Yes;IMEX=1;DATABASE=C:\share\Access\regions.xlsx].[Region$];

If you can get a similar query working in your Access database, change your ASP to open the ADO connection to your Access db, then use the connection to Execute your INSERT statement. Your ASP code would be much simpler than what you have now, and should operate considerably faster.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166241

You need a different cursor type - adOpenStatic doesn't support updates.

http://www.w3schools.com/ado/prop_rs_cursortype.asp

Upvotes: 1

Related Questions