Description of the update for Access 2002: October 18, 2005
You have installed an Access runtime application that includes Microsoft Office 2003 Service Pack 2 (SP2) or a later service pack, any Access 2003 updates that were released after Office 2003 SP2, or the update for Access 2002 (KB904018) that is dated October 18, 2005 or later.
For more information about how to include Office update files with an Access runtime application, click the following article number to view the article in the Microsoft Knowledge Base:
To work around this expected behavior, use one of the following methods.
Method 1: Use Microsoft Excel
Open the linked Excel workbook in Microsoft Excel, and then make your changes to the workbook. When you have completed the changes, save the changes and then close the workbook.
Method 2: Use Office Access 2007, Access 2003, or Access 2002
Import the linked Excel workbook into Access, and then make your changes to the data. When you have completed the changes, export the data as an Excel .xls file.
To export the table from Access to Excel, run the following code in 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
MsgBox "Access has successfully exported the data to excel file.", vbInformation, "Export Successful."
MsgBox Err.Description, vbCritical, "Error"
Private Sub CopyRecordSetToXL(SQL As String, con As ADODB.Connection)
Dim rs As New ADODB.Recordset
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>")
'Getting the first cell to input the data.
y = xlApp.ActiveCell.Column - 1
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.Open SQL, con
If rs.RecordCount > 0 Then
x = Replace(x, "$", "")
y = Mid(x, 2)
Set rng = xlwsSheet.Range(x)
Set xlwsSheet = Nothing
Set xlwbBook = Nothing
Set xlApp = Nothing
Note In this code example, replace the following placeholders:
<MyQuery> <MyQuery> is placeholder for the query that you run against the tables in the Access database. The result of the query is exported to the Excel workbook.
<WorkSheets> <WorkSheets> is a placeholder for the worksheet in Excel to which you want to export the result.
To run this code example, press CTRL+G to open the Immediate window, type WorkArounds, and then press ENTER.
Because of legal issues, Microsoft has disabled the functionality in Access 2003 and in Access 2002 that let users change the data in linked tables that point to a range in an Excel workbook. However, when you make changes directly in the Excel workbook, the changes appear in the linked table in Access.