Utilizzando Excel VBA per esportare i dati nella tabella MS Access

Attualmente sto usando il seguente codice per esportare i dati dal foglio di lavoro al database di MS Access, il codice è in loop su ogni riga e inserire i dati nella tabella di MS Access.

Public Sub TransData() Application.ScreenUpdating = False Application.EnableAnimations = False Application.EnableEvents = False Application.DisplayAlerts = False ActiveWorkbook.Worksheets("Folio_Data_original").Activate Call MakeConnection("fdMasterTemp") For i = 1 To rcount - 1 rs.AddNew rs.Fields("fdName") = Cells(i + 1, 1).Value rs.Fields("fdDate") = Cells(i + 1, 2).Value rs.Update Next i Call CloseConnection Application.ScreenUpdating = True Application.EnableAnimations = True Application.EnableEvents = True Application.DisplayAlerts = True End Sub 

 Public Function MakeConnection(TableName As String) As Boolean '*********Routine to establish connection with database Dim DBFullName As String Dim cs As String DBFullName = Application.ActiveWorkbook.Path & "\FDData.mdb" cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";" Set cn = CreateObject("ADODB.Connection") If Not (cn.State = adStateOpen) Then cn.Open cs End If Set rs = CreateObject("ADODB.Recordset") If Not (rs.State = adStateOpen) Then rs.Open TableName, cn, adOpenKeyset, adLockOptimistic End If End Function 

 Public Function CloseConnection() As Boolean '*********Routine to close connection with database On Error Resume Next If Not rs Is Nothing Then rs.Close End If If Not cn Is Nothing Then cn.Close End If CloseConnection = True Exit Function End Function 

Sopra il codice funziona bene per poche centinaia di righe di record, ma a quanto pare saranno più dati da esportare, come 25000 record, è ansible esportare senza eseguire il looping di tutti i record e solo una istruzione SQL INSERT per inserire in blocco tutti i dati su Ms.Access Tavolo in una volta?

Qualsiasi aiuto sarà molto apprezzato.

MODIFICA: ISSUE RISOLTO

Solo per informazioni se qualcuno cerca questo, ho fatto un sacco di ricerche e ho trovato il seguente codice per funzionare bene per me, ed è molto veloce grazie a SQL INSERT, (27648 record in soli 3 secondi !!!! ):

 Public Sub DoTrans() Set cn = CreateObject("ADODB.Connection") dbPath = Application.ActiveWorkbook.Path & "\FDData.mdb" dbWb = Application.ActiveWorkbook.FullName dbWs = Application.ActiveSheet.Name scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath dsh = "[" & Application.ActiveSheet.Name & "$]" cn.Open scn ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) " ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh cn.Execute ssql End Sub 

Sto ancora lavorando per aggiungere il nome di campi specifici invece di usare “Seleziona *”, ho provato vari modi per aggiungere nomi di campi ma non posso farlo funzionare per ora.

è ansible esportare senza eseguire il looping di tutti i record

Per un intervallo in Excel con un numero elevato di righe, potresti notare un miglioramento delle prestazioni se crei un object Access.Application in Excel e utilizzalo per importare i dati di Excel in Access. Il codice riportato di seguito si trova in un modulo VBA nello stesso documento Excel che contiene i seguenti dati di test

SampleData.png

 Option Explicit Sub AccImport() Dim acc As New Access.Application acc.OpenCurrentDatabase "C:\Users\Public\Database1.accdb" acc.DoCmd.TransferSpreadsheet _ TransferType:=acImport, _ SpreadSheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:="tblExcelImport", _ Filename:=Application.ActiveWorkbook.FullName, _ HasFieldNames:=True, _ Range:="Folio_Data_original$A1:B10" acc.CloseCurrentDatabase acc.Quit Set acc = Nothing End Sub 

@Ahmed

Di seguito è riportato il codice che specifica i campi da un intervallo denominato per l’inserimento in MS Access. La cosa bella di questo codice è che puoi nominare i tuoi campi in Excel qualunque sia il tuo inferno (se usi * allora i campi devono corrispondere esattamente tra Excel e Access) come puoi vedere ho chiamato una colonna di Excel “Haha” anche se la colonna Access è chiamata “dte”.

 Sub test() dbWb = Application.ActiveWorkbook.FullName dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2" 'Data2 is a named range sdbpath = "C:\Users\myname\Desktop\Database2.mdb" sCommand = "INSERT INTO [main] ([dte], [test1], [values], [values2]) SELECT [haha],[test1],[values],[values2] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh Dim dbCon As New ADODB.Connection Dim dbCommand As New ADODB.Command dbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbpath & "; Jet OLEDB:Database Password=;" dbCommand.ActiveConnection = dbCon dbCommand.CommandText = sCommand dbCommand.Execute dbCon.Close End Sub