从EXCEL中读数据进入ACCESS表，为什么在ACCESS中比在EXCEL 中慢 ?

holywind

有EXCEL表中的(部分)数据要导入ACCESS TABLE中，在EXCEL中用VBA 读取用了2 分钟，几乎同样的程序放在ACCESS VBA中要用 13分钟，哪位大侠给瞧瞧。 多谢!!

ACCESS 中的：



Option Compare Database
Option Explicit
Public Const PATH = "D:\HFH Doc\programing\PPA\update\VBA\06_29_2009"
Public Const USAGE = "USAGEFTR.xls"
Public Const CRUNCHDB = "CRUNCHDB.mdb"

Function DAOFromExcelIntoAccessTable()
Dim conn As DAO.Connection
Dim rsSub As DAO.Recordset
Dim rsContract As DAO.Recordset
Dim rsDataSOC As DAO.Recordset
Dim rsHardware As DAO.Recordset
Dim rsBilling As DAO.Recordset
Dim rsRoam As DAO.Recordset
Dim rsRevenue As DAO.Recordset
Dim iErrorHandle As Integer

    iErrorHandle = ReadInUsageTable(PATH, USAGE)
End Function

Function ReadInUsageTable(UsageFilePath As String, UsageFileName As String) As Integer
Dim rsUsage As DAO.Recordset
Dim iUsageSht_StartRow As Long
Dim iUsageSht_StopRow As Long
Dim iLast_Row As Long
Dim I As Long
Dim ExcelApp As Excel.Application
Dim xlWrkBk As Excel.Workbook
Dim xlsht As Excel.Worksheet
Set ExcelApp = CreateObject("Excel.Application")
Set xlWrkBk = GetObject(UsageFilePath & "\" & UsageFileName)
Set xlsht = xlWrkBk.Worksheets(1)
With xlWrkBk
    If xlsht.Range("B1").Value > 0 Then
        iLast_Row = xlsht.Range("B1").End(xlDown).Row
        iUsageSht_StopRow = iLast_Row - 1 + iUsageSht_StartRow - 1
    Else
        MsgBox "There seems to be a problem with the SUB.xls report.  Please check and make sure that it is as required", vbOKOnly, "SUB.xls"
        End
    End If
End With
   
DoCmd.RunSQL "delete * from Source_Usage"
    Set rsUsage = CurrentDb.OpenRecordset("Source_Usage_Advance", dbOpenDynaset)
    With xlsht
    For I = 2 To iLast_Row
        rsUsage.AddNew
        rsUsage!BAN = .Cells(I, 1).Value
        rsUsage!SUBSCRIBER_NO = .Cells(I, 2).Value
        rsUsage!BILL_YEAR = .Cells(I, 4).Value
        rsUsage!BILL_MONTH = .Cells(I, 5).Value
        rsUsage!BILL_CYCLE = .Cells(I, 6).Value
        rsUsage!PRICE_PLAN_CODE = .Cells(I, 7).Value
        rsUsage!SPECIAL_NUM_TYPE = .Cells(I, 8).Value
        rsUsage!AIRTIME_FEATURE_CD = .Cells(I, 9).Value
        rsUsage!ACTION_DIRECTION_CD = .Cells(I, 10).Value
        rsUsage!CALL_TYPE = .Cells(I, 14).Value
        rsUsage!PERIOD_LEVEL_CODE = .Cells(I, 16).Value
        rsUsage!CTN_MINS_LOCAL = .Cells(I, 19).Value
        rsUsage!NUM_CALLS_LOCAL = .Cells(I, 20).Value
        rsUsage!CHRG_AMT_LOCAL = .Cells(I, 21).Value
        rsUsage!CTN_MINS_FM = .Cells(I, 35).Value
        rsUsage.Update
    Next
    End With
   
    Set rsUsage = Nothing
    xlWrkBk.Close False
End Function

Private Sub ReadIn_CMD_Click()
Open "D:\HFH Doc\programing\PPA\update\VBA\06_29_2009\timeused.txt" For Append As #1
Print #1, Time
DAOFromExcelIntoAccessTable
Print #1, Time
Close #1
CurrentDb.Close
End Sub


原EXCEL 数据有10M 左右，不方便上传。请自己做个例表TEST，看看程序结构上有什么不妥。

dragonszr

看不太懂,有说明没有啊!

dragonszr

可以用了,就是导入数据太慢了!

levy_rao

只接用获取外部数据-导入会不会快呢