Linking Excel Tables in Access Gives Read-Only Error

Question

I have a couple of Excel 2010 files that are mapped and linked to an Access 2010 database. I need to add another file so that three are mapped instead of two. I linked the excel file from the Import and Link tab under External Database. That seems to be okay. But when I run the code that re-maps the excel file, it gives me a runtime error 3027: Database or Object is read only. None of the files or database are read-only.

This is the code to re-map the files from a new location (ex. from the X-drive to the mail W-drive), which would also add the new excel file. Should something be added here to let me add new files?

Private Sub cmdAcceptPath_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strPath As String
Dim strFileName As String
Dim strSourceDB As String
Dim strTableName As String
Dim sList As String
Dim gMsgBoxTitle As String
On Error GoTo Error_Handler:

DoCmd.SetWarnings False
strSourceDB = Me.tExcelPath.Value
Set db = CurrentDb
strSQL = "update tblBackendFiles set setting=" & setData(strSourceDB) & " where code='SourceExcel'"
DoCmd.RunSQL strSQL

'-- Verify linked tables by refreshing
strSQL = "select setting, ExcelPath, ExcelRange from tblBackendFiles where code='SourceExcelWB'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'Open remapprogress
DoCmd.OpenForm "frmReMapProgress"

sList = ""
rs.MoveFirst
While Not rs.EOF
    strTableName = rs!Setting
    sList = sList & vbNewLine & "Deleting Table: " & strTableName
    Forms!frmReMapProgress.tbProgress = sList
    If TableExists(strTableName) Then
        DoCmd.DeleteObject acTable, strTableName
    End If
    rs.MoveNext
Wend

sList = ""
'-- Relink inventory database
rs.MoveFirst
While Not rs.EOF
    sList = sList & vbNewLine & "Linking Table: " & strTableName
    Forms!frmReMapProgress.tbProgress = sList
    Forms!frmReMapProgress.Refresh
    strTableName = rs!Setting
    strPath = strSourceDB & "\" & rs!ExcelPath
    Debug.Print strPath
    DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel9, strTableName, strPath, True, rs!ExcelRange
    rs.MoveNext
Wend
rs.Close
MsgBox "Re-Mapping Excel Links is Complete!"

GoTo exit_sub:

'If error occurs
Error_Handler:
    MsgBox Err.number & ": " & Err.Description, vbInformation + vbOKOnly, gMsgBoxTitle

exit_sub:
Set db = Nothing
Set rs = Nothing
DoCmd.SetWarnings True

DoCmd.Close acForm, "frmReMapProgress"
DoCmd.Close acForm, "frmReMapExcel"


End Sub

Answer 1

You don't need to delete and recreate the link to the Excel file. Just close the linked table (if open) and replace the Excel file with the new copy.

When you open the linked table, it will read from the new file.