U geen gegevens wijzigen, toevoegen of verwijderen in tabellen die zijn gekoppeld aan een Excel-werkmap in Access

Notitie

Office 365 ProPlus wordt hernoemd naar Microsoft 365 Apps voor ondernemingen. Raadpleeg deze blogpost voor meer informatie over deze wijziging.

Origineel KB-nummer:   904953

Symptomen

In Microsoft Office Access 2007 of Microsoft Office Access 2003 of Microsoft Access 2002 u geen gegevens wijzigen, toevoegen of verwijderen in tabellen die zijn gekoppeld aan een Microsoft Excel-werkmap.

Bovendien u dit gedrag ervaren wanneer een van de volgende voorwaarden waar is:

  • U maakt een query om gegevens op te halen uit tabellen die zijn gekoppeld aan een Excel-werkmap.
  • U maakt een formulier dat toegang krijgt tot gegevens uit tabellen die zijn gekoppeld aan een Excel-werkmap.
  • U gebruikt DAO of ADO om tabellen programmatisch bij te werken die zijn gekoppeld aan een Excel-werkmap.

U ontvangt het volgende bericht wanneer u een query uitvoert om records in een gekoppelde Excel-werkmap bij te werken:

De bewerking moet een updatebare query gebruiken

U ontvangt het volgende bericht wanneer u DAO gebruikt om tabellen die zijn gekoppeld aan een Excel-werkmap programmatisch bij te werken:

Runtime Error '3027' kan niet worden bijgewerkt. Database of object is alleen-lezen.

Wanneer u de gekoppelde gegevens in ADO probeert bij te werken, is het bericht hetzelfde, maar het foutnummer kan vergelijkbaar zijn met het volgende:

-2147217911 (80040e09)

Wanneer u een query uitvoert om records in een Excel-werkmap in te voegen, ontvangt u het volgende foutbericht, zelfs als de Excel-werkmap niet is gekoppeld aan een Access-database:

De bewerking moet een updatebare query gebruiken

Oorzaak

Dit verwachte gedrag treedt op wanneer een van de volgende voorwaarden waar is:

  • U gebruikt Office Access 2007.
  • U hebt Microsoft Office 2003 Service Pack 2 (SP2) of een later servicepack of access 2003-updates die na Office 2003 SP2 zijn uitgebracht, geïnstalleerd.
  • U hebt de update voor Access 2002 (KB904018) die dateert van 18 oktober 2005 geïnstalleerd.
  • U hebt een Access runtime-toepassing geïnstalleerd die Microsoft Office 2003 Service Pack 2 (SP2) of een later servicepack bevat, alle Access 2003-updates die zijn uitgebracht na Office 2003 SP2 of de update voor Access 2002 (KB904018) die is gedateerd op 18 oktober 2005 of hoger.

Tijdelijke oplossing

Gebruik een van de volgende methoden om dit verwachte gedrag te omzeilen.

Methode 1: Microsoft Excel gebruiken

Open de gekoppelde Excel-werkmap in Microsoft Excel en breng vervolgens de werkmap aan. Wanneer u de wijzigingen hebt voltooid, slaat u de wijzigingen op en sluit u de werkmap.

Methode 2: Office Access 2007, Access 2003 of Access 2002 gebruiken

Importeer de gekoppelde Excel-werkmap in Access en breng de gegevens aan. Wanneer u de wijzigingen hebt voltooid, exporteert u de gegevens als een Excel .xls-bestand.

Voer de volgende code uit in Access als u de tabel wilt exporteren van Access naar Excel.

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

Notitie

Vervang in dit codevoorbeeld de volgende tijdelijke aanduidingen:

  • <AccessPath->

  • <ExcelPath>

  • <MyQuery>

    <MyQuery> is tijdelijke aanduiding voor de query die u uitvoert tegen de tabellen in de Access-database. Het resultaat van de query wordt geëxporteerd naar de Excel-werkmap.

  • <Werkbladen>

    <Werkbladen> is een tijdelijke aanduiding voor het werkblad in Excel waarnaar u het resultaat wilt exporteren. Als u dit codevoorbeeld wilt uitvoeren, drukt u op Ctrl+G om het venster Direct te openen, typt u Tijdelijke oplossingen en drukt u op ENTER.

Meer informatie

Vanwege juridische problemen heeft Microsoft de functionaliteit in Access 2003 en in Access 2002 uitgeschakeld waarmee gebruikers de gegevens in gekoppelde tabellen kunnen wijzigen die wijzen op een bereik in een Excel-werkmap. Wanneer u echter rechtstreeks wijzigingen aanbrengt in de Excel-werkmap, worden de wijzigingen weergegeven in de gekoppelde tabel in Access.