Excel VBA更新或通过依赖关系插入到现有访问表中

问题描述

我想将Excel工作表(shReceiver)中的所有行上载到MS Access表(tblReceiver).

I want to upload all rows from Excel worksheet (shReceiver) to an MS Access table (tblReceiver).

在Excel以及访问表(tblReceiver)中都有一个主键-TNR(票号).因此，首先我检查tblReceiver中是否存在shReceiver中的TNR.如果是这样，那么我将删除tblReceiver中的数据.然后，我将所有数据从shReceiver附加到tblReeiver.

There is a single primary key - TNR (Ticket number) in both Excel as well as the Access table (tblReceiver). So first I check if TNR from shReceiver exists in tblReceiver. If so, then I delete the data in tblReceiver. Then I append all data from shReceiver to tblReeiver.

但是，Access DB中的许多查询都与tblReceiver相关联，这使得该过程非常缓慢(执行需要30分钟以上).更新tblReceiver时，是否有任何方法可以禁用其他Access DB查询或索引?

However, a lot of queries in Access DB are associated with tblReceiver, which makes this process extremely slow (30 min plus for execution). Is there any way to disable the other Access DB queries or indexes, when updating the tblReceiver?

请注意，我在tblReceiver中有74k列的大约70k +记录，在shReceiver中要上传的10k +数据.

Please note that I have around 70k+ records with 74 columns in tblReceiver and 10k+ data for upload in the shReceiver.

我的代码如下:

Public Sub ExportReceiver()
Dim lastRow, var_Range_Count As Long
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Receiver")
lastRow = sh.Cells(Rows.Count, 2).End(xlUp).Row

Call databaseCon
For var_Range_Count = 3 To lastRow
    cnn.Execute "DELETE * FROM tblReceiver WHERE tblReceiver.[TNR] =" & "'" 
    & sh.Range("A" & var_Range_Count).Value & "'", dbFailOnError
Next
Call CloseDB

Dim acc As New ACCESS.Application
acc.OpenCurrentDatabase ThisWorkbook.Path & "\TicketsDB.accdb"
acc.DoCmd.TransferSpreadsheet _
        TransferType:=acImport, _
        SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _
        TableName:="tblReceiver", _
        FileName:=Application.ActiveWorkbook.FullName, _
        HasFieldNames:=True, _
        Range:="Receiver$A2:BX" & lastRow

acc.CloseCurrentDatabase
acc.Quit
Set acc = Nothing
End Sub

推荐答案

不导入Excel数据，而是将它们链接为链接表.

Don't import the Excel data but link them as a linked table.

然后使用该表作为合并的更新/追加查询中的源，如下所述:

Then use that table as source in a combined update/append query as described here:

比较两个表并更新或插入数据

这将一口气运行.

这篇关于Excel VBA更新或通过依赖关系插入到现有访问表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！