Du kan ikke endre, legge til eller slette data i tabeller som er koblet til en Excel-arbeidsbok i Access

Obs!

Office 365 ProPlus får nytt navn og heter nå Microsoft 365-apper for enterprise. Hvis du vil ha mer informasjon om denne endringen, kan du lese dette blogginnlegget.

Opprinnelig KB-nummer:   904953

Symptomer

I Microsoft Office Access 2007 eller Microsoft Office Access 2003 eller Microsoft Access 2002 kan du ikke endre, legge til eller slette data i tabeller som er koblet til en Microsoft Excel-arbeidsbok.

I tillegg kan du oppleve denne virkemåten når en av følgende betingelser er oppfylt:

  • Du kan bygge en spørring for å hente data fra tabeller som er koblet til en Excel-arbeidsbok.
  • Du kan bygge et skjema som har tilgang til data fra tabeller som er koblet til en Excel-arbeidsbok.
  • Du kan bruke DAO eller ADO til å oppdatere tabeller programmatisk som er koblet til en Excel-arbeidsbok.

Du får følgende melding når du utfører en spørring for å oppdatere poster i en koblet Excel-arbeidsbok:

Operasjonen må bruke en oppdaterbar spørring

Du får følgende melding når du bruker DAO til å oppdatere tabeller som er koblet til en Excel-arbeidsbok programmatisk:

Kjøretidsfeil '3027' kan ikke oppdatere. Databasen eller objektet er skrivebeskyttet.

Når du prøver å oppdatere de koblede dataene i ADO, er meldingen den samme, men feilnummeret kan være lik følgende:

-2147217911 (80040e09)

Når du kjører en spørring for å sette inn poster i en Excel-arbeidsbok, får du følgende feilmelding selv om Excel-arbeidsboken ikke er koblet til en Access-database:

Operasjonen må bruke en oppdaterbar spørring

Årsak

Denne forventede virkemåten oppstår når en av følgende betingelser er oppfylt:

  • Du bruker Office Access 2007.
  • Du har installert Microsoft Office 2003 Service Pack 2 (SP2) eller en senere oppdateringspakke eller eventuelle Access 2003-oppdateringer som ble utgitt etter Office 2003 SP2.
  • Du har installert oppdateringen for Access 2002 (KB904018) som er datert 18 oktober 2005.
  • Du har installert et Access-kjøretidsprogram som inkluderer Microsoft Office 2003 Service Pack 2 (SP2) eller en senere oppdateringspakke, alle Access 2003-oppdateringer som ble utgitt etter Office 2003 SP2 eller oppdateringen for Access 2002 (KB904018) som er datert 18.

Løsning

Hvis du vil omgå denne forventede virkemåten, kan du bruke én av følgende metoder.

Metode 1: Bruke Microsoft Excel

Åpne den koblede Excel-arbeidsboken i Microsoft Excel, og gjør deretter endringene i arbeidsboken. Når du har fullført endringene, lagrer du endringene og lukker arbeidsboken.

Metode 2: Bruke Office Access 2007, Access 2003 eller Access 2002

Importer den koblede Excel-arbeidsboken til Access, og gjør deretter endringene i dataene. Når du har fullført endringene, eksporterer du dataene som en Excel XLS-fil.

Hvis du vil eksportere tabellen fra Access til Excel, kjører du følgende kode i 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

Obs!

I dette kodeeksemplet erstatter du følgende plassholdere:

  • <AccessPath>

  • <ExcelPath>

  • <MyQuery>

    <MyQuery> er plassholder for spørringen du kjører mot tabellene i Access-databasen. Resultatet av spørringen eksporteres til Excel-arbeidsboken.

  • <Arbeidsark>

    <WorkSheets> er en plassholder for regnearket i Excel som du vil eksportere resultatet til. Hvis du vil kjøre dette kodeeksemplet, trykker du CTRL+G for å åpne Øyeblikk-vinduet, skriver inn Midlertidige løsninger, og deretter trykker du ENTER.

Mer informasjon

På grunn av juridiske problemer har Microsoft deaktivert funksjonaliteten i Access 2003 og Access 2002 som lar brukere endre dataene i koblede tabeller som peker til et område i en Excel-arbeidsbok. Når du gjør endringer direkte i Excel-arbeidsboken, vises imidlertid endringene i den koblede tabellen i Access.