Não pode alterar, adicionar ou eliminar dados em tabelas ligadas a um livro do Excel no Access

Número original da BDC: 904953

Sintomas

No Microsoft Office Access 2007 ou no Microsoft Office Access 2003 ou no Microsoft Access 2002, não pode alterar, adicionar ou eliminar dados em tabelas ligadas a um livro do Microsoft Excel.

Além disso, poderá deparar-se com este comportamento quando qualquer uma das seguintes condições for verdadeira:

• Pode criar uma consulta para obter dados de tabelas ligadas a um livro do Excel.
• Cria um formulário que acede a dados de tabelas ligadas a um livro do Excel.
• Pode utilizar o DAO ou o ADO para atualizar tabelas através de programação ligadas a um livro do Excel.

Recebe a seguinte mensagem quando efetua uma consulta para atualizar registos num livro do Excel ligado:

A operação tem de utilizar uma consulta atualizável

Recebe a seguinte mensagem quando utiliza o DAO para atualizar programaticamente tabelas ligadas a um livro do Excel:

Erro de runtime "3027" Não é possível atualizar. A base de dados ou objeto é só de leitura.

Quando tenta atualizar os dados ligados no ADO, a mensagem é a mesma, mas o número de erro pode ser semelhante ao seguinte:

-2147217911 (80040e09)

Quando executa uma consulta para inserir registos num livro do Excel, recebe a seguinte mensagem de erro, mesmo que o livro do Excel não esteja ligado a uma base de dados do Access:

A operação tem de utilizar uma consulta atualizável

Causa

Este comportamento esperado ocorre quando uma das seguintes condições é verdadeira:

• Está a utilizar o Office Access 2007.
• Instalou o Microsoft Office 2003 Service Pack 2 (SP2) ou um service pack posterior ou quaisquer atualizações do Access 2003 lançadas após o Office 2003 SP2.
• Instalou a atualização para o Access 2002 (KB904018) datada de 18 de outubro de 2005.
• Instalou uma aplicação de runtime do Access que inclui o Microsoft Office 2003 Service Pack 2 (SP2) ou um service pack posterior, quaisquer atualizações do Access 2003 lançadas após o Office 2003 SP2 ou a atualização para o Access 2002 (KB904018) datado de 18 de outubro de 2005 ou posterior.

Solução

Para contornar este comportamento esperado, utilize um dos seguintes métodos.

Método 1: Utilizar o Microsoft Excel

Abra o livro do Excel ligado no Microsoft Excel e, em seguida, faça as suas alterações ao livro. Quando tiver concluído as alterações, guarde as alterações e, em seguida, feche o livro.

Método 2: Utilizar o Office Access 2007, Access 2003 ou Access 2002

Importe o livro do Excel ligado para o Access e, em seguida, faça as alterações aos dados. Quando tiver concluído as alterações, exporte os dados como um ficheiro de .xls do Excel.

Para exportar a tabela do Access para o Excel, execute o seguinte código no Access.

Public Sub WorkArounds()
On Error GoTo Leave

Dim strSQL, SQL As String
    Dim Db As ADODB.Connection
    Set Db = New ADODB.Connection
    Db.CursorLocation = adUseClient
    Db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=<AccessPath>"
    'Note: In Office Access 2007, use the following line of code:
    'Db.Open "PROVIDER=Microsoft.ACE.OLEDB.12.0;Data Source=<AccessPath>"
    SQL = "<MyQuery>"
    CopyRecordSetToXL SQL, Db
    Db.Close
    MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
    Exit Sub
Leave:
        MsgBox Err.Description, vbCritical, "Error"
        Exit Sub
End Sub

Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
    Dim rs As New ADODB.Recordset
    Dim x
    Dim i As Integer, y As Integer
    Dim xlApp As Excel.Application
    Dim xlwbBook As Excel.Workbook, xlwbAddin As Excel.Workbook
    Dim xlwsSheet As Excel.Worksheet
    Dim rnData As Excel.Range
    Dim stFile As String, stAddin As String
    Dim rng As Range
    stFile = "<ExcelPath>"
    'Instantiate a new session with the COM-Object Excel.exe.
    Set xlApp = New Excel.Application
    Set xlwbBook = xlApp.Workbooks.Open(stFile)
    Set xlwsSheet = xlwbBook.Worksheets("<WorkSheets>")
    xlwsSheet.Activate
    'Getting the first cell to input the data.
    xlwsSheet.Cells.SpecialCells(xlCellTypeLastCell).Select
    y = xlApp.ActiveCell.Column - 1
    xlApp.ActiveCell.Offset(1, -y).Select
    x = xlwsSheet.Application.ActiveCell.Cells.Address
    'Opening the recordset based on the SQL query and saving the data in the Excel worksheet.
    rs.CursorLocation = adUseClient
    If rs.State = adStateOpen Then
        rs.Close
    End If
    rs.Open SQL, con
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        x = Replace(x, "$", "")
        y = Mid(x, 2)
        Set rng = xlwsSheet.Range(x)
        xlwsSheet.Range(x).CopyFromRecordset rs
    End If
    xlwbBook.Close True
    xlApp.Quit
    Set xlwsSheet = Nothing
    Set xlwbBook = Nothing
    Set xlApp = Nothing

End Sub

• <AccessPath>

• <ExcelPath>

• <MyQuery>

  <MyQuery é o marcador de> posição da consulta que executa nas tabelas na base de dados do Access. O resultado da consulta é exportado para o livro do Excel.

• <Folhas de Cálculo>

  <Folhas> de Cálculo é um marcador de posição para a folha de cálculo no Excel para a qual pretende exportar o resultado. Para executar este exemplo de código, prima CTRL+G para abrir a janela Imediata , escreva Soluções e, em seguida, prima ENTER.

Mais Informações

Devido a problemas legais, a Microsoft desativou a funcionalidade no Access 2003 e no Access 2002 que permite aos utilizadores alterar os dados em tabelas ligadas que apontam para um intervalo num livro do Excel. No entanto, quando efetua alterações diretamente no livro do Excel, as alterações são apresentadas na tabela ligada no Access.