Microsoft.ACE.OLEDB.12.0当前Recordset不支持在尝试更新访问时更新错误 [英] Microsoft.ACE.OLEDB.12.0 Current Recordset does not support updating error received when trying to update access

问题描述

我有一个ASP页面，用户将上传一个excel文件。文件成功上传后，我想在REGION选项卡中取出这些行，并将它们插入到MS Access 2007表中。以下是我使用的代码，我收到这个错误。可以使用Microsoft.ACE.OLEB.12.0提供程序的记录集更新吗？有没有更好的方法来做到这一点？
ADODB.Recordset错误'800a0cb3'

当前Recordset不支持更新。这可能是提供者或所选锁类型的限制。

 设置cnnExcel = Server.CreateObject（ADODB.Connection）
 cnnExcel.OpenProvider = Microsoft.ACE .OLEDB.12.0; &安培; _ 
Data Source =& strExcelFile& ; &安培; _ 
扩展属性=& Chr（34）& Excel 12.0 Xml; HDR = Yes; IMEX = 1& Chr（34）& ; 
 Response.Write打开Excel连接&BR; 
 
'使用Excel数据加载ADO Recordset 
设置rstExcel = Server.CreateObject（ADODB.Recordset）
 rstExcel.Open从[REGION $]中选择*，cnnExcel ，adOpenStatic 
 Response.Write加载了Excel Recordset&BR; 
 
'打开访问连接
设置cnnAccess = Server.CreateObject（ADODB.Connection）
 cnnAccess.OpenProvider = Microsoft.ACE.OLEDB.12.0; Data Source = & strAccessFile& ; Persist Security Info = False; 
 Response.Write已打开访问连接&BR; 
 Const adOpenStatic = 1 
 Const adLockOptimistic = 3 
 Const adCmdText =& H0001 
'使用访问数据加载ADO记录
设置rstAccess = Server.CreateObject（ADODB .Recordset）
 rstAccess.OpenREGION，cnnAccess，adOpenStatic，adLockOptimistic，adCmdTable 
 Response.Write已加载访问记录集&BR 
 
'同步记录集和批次更新
做直到rstExcel.EOF 
 
'.AddNew 
对于rstExcel.Fields中的每个字段
如果field.Name =％Over / Under然后
 rstAccess.AddNew field.Name，0 
 Else 
 rstAccess.AddNew field.Name，field.Value 
 End If 
下一个
 
 rstExcel.MoveNext 
循环
 rstAccess.UpdateBatch 
  

解决方案

谢谢Hans，Tim和Remou。我遵循此示例使用INSERT INTO将数据写入访问数据库

 '打开访问连接
设置cnnAccess = Server.CreateObject（ADODB.Connection）
 cnnAccess.OpenProvider = Microsoft.ACE.OLEDB.12.0; Data Source =& strAccessFilePath& ; Persist Security Info = False; 
 cnnAccess.ExecuteDELETE * FROM REGION
'打开Excel连接
设置cnnExcel = Server.CreateObject（ADODB.Connection）
 cnnExcel.OpenProvider = Microsoft。 ACE.OLEDB.12.0; &安培; _ 
Data Source =& strExcelFilePath& ; &安培; _ 
扩展属性=& Chr（34）& Excel 12.0 Xml; HDR = Yes; IMEX = 1& Chr（34）& ; 
 Response.Write打开Excel连接&BR; 
 cnnExcel.ExecuteINSERT INTO REGION IN'C：\inetpub\wwwroot\FolderName\App_Data\AccessFileName.accdb'& _ 
选择col1，col2，col3，col4& _ 
from [REGION $]
 
 
'清理
 cnnExcel.Close 
设置cnnExcel = Nothing 
 cnnAccess.Close 
设置cnnAccess =没有
  

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

解决方案

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

这篇关于Microsoft.ACE.OLEDB.12.0当前Recordset不支持在尝试更新访问时更新错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！