Excel VBA를 사용하여 MS Access 테이블로 데이터 내보내기

Excel VBA를 사용하여 MS Access 테이블로 데이터 내보내기

현재 다음 코드를 사용하여 워크시트에서 MS Access 데이터베이스로 데이터를 내보내고 있습니다.코드는 각 행을 루프하여 MS Access Table에 데이터를 삽입합니다.

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

위의 코드는 수백 줄의 레코드에서 정상적으로 동작하지만 25000개의 레코드와 같이 내보낼 데이터가 많아질 것입니다.모든 레코드를 루프하지 않고 SQL INSERT 문을 하나만 사용하여 모든 데이터를 MS에 일괄 삽입할 수 있습니다.한 번에 테이블에 액세스하시겠습니까?

어떤 도움이라도 주시면 대단히 감사하겠습니다.

편집: 문제 해결 완료

참고로 찾고 있는 사람이 있다면 아래의 코드를 많이 검색해 보았습니다만, SQL INSERT로 매우 빠릅니다(3초만에 레코드가 27648개).!!):

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

"Select *"를 사용하는 대신 특정 필드 이름을 추가하는 작업을 계속하고 있으며, 필드 이름을 추가하는 다양한 방법을 시도했지만 현재 사용할 수 없습니다.

모든 레코드를 루프하지 않고 내보낼 수 있습니까?

Excel에서 행 수가 많은 범위의 경우, 다음을 생성하면 성능이 향상될 수 있습니다.Access.ApplicationExcel의 객체를 사용하여 Excel 데이터를 Access로 Import합니다.다음 코드는 다음 테스트 데이터가 포함된 동일한 Excel 문서의 VBA 모듈에 있습니다.

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

@아메드

다음 코드는 MS Access에 삽입하기 위해 지정된 범위의 필드를 지정하는 코드입니다.이 코드의 장점은 Excel에서 원하는 필드에 이름을 붙일 수 있다는 것입니다(*을 사용하는 경우 Excel과 Access 사이에 정확히 일치해야 합니다). Access 컬럼은 dte라고 불리지만 Excel 컬럼은 Haha라고 이름 붙여져 있습니다.

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

언급URL : https://stackoverflow.com/questions/16161865/using-excel-vba-to-export-data-to-ms-access-table