在访问中链接Excel表会产生只读错误

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.

我有一些Excel 2010文件，它们被映射并链接到一个Access 2010数据库。我需要添加另一个文件，以便映射3而不是2。我从外部数据库的Import和Link选项卡链接excel文件。看起来还行。但是当我运行重新映射excel文件的代码时，它会给我一个运行时错误3027:只读取数据库或对象。所有文件或数据库都不是只读的。

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?

这是重新映射文件的代码，从一个新的位置(例如从X-drive到邮件W-drive)，它还将添加新的excel文件。是否应该添加一些东西让我添加新文件?

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

1 个解决方案

#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.

您不需要删除并重新创建到Excel文件的链接。只需关闭链接表(如果打开)并使用新副本替换Excel文件。

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

当您打开链接的表时，它将从新文件中读取。

#1