sql - valores - Usando Excel VBA para exportar datos a la tabla de MS Access

modificar datos de una tabla mysql

Question

Actualmente estoy usando el siguiente código para exportar datos de la hoja de trabajo a la base de datos de MS Access, el código está en bucle a través de cada fila e insertar datos en la tabla de 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

El código anterior funciona bien para unos pocos cientos de líneas de registros, pero aparentemente habrá más datos para exportar. Como 25000 registros, es posible exportar sin hacer un bucle a través de todos los registros y solo una instrucción SQL INSERT para insertar de forma masiva todos los datos en Ms.Access Tabla de una sola vez?

Cualquier ayuda será muy apreciada.

EDITAR: NÚMERO RESUELTO

Solo para obtener información, si alguien busca esto, hice una gran cantidad de búsquedas y encontré que el siguiente código funciona bien para mí, y es muy rápido debido a SQL INSERT, (27648 registros en solo 3 segundos !!!! ):

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

Aún trabajando para agregar nombres de campos específicos en lugar de usar "Seleccionar *", probé varias formas de agregar nombres de campos, pero no puedo hacer que funcione por ahora.

Answer 1

¿Es posible exportar sin recorrer todos los registros?

Para un rango en Excel con un gran número de filas, puede ver algunas mejoras en el rendimiento si crea un objeto Access.Application en Excel y luego lo usa para importar los datos de Excel en Access. El siguiente código se encuentra en un módulo VBA en el mismo documento de Excel que contiene los siguientes datos de prueba

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

Answer 2

@Ahmed

A continuación se encuentra el código que especifica los campos de un rango con nombre para su inserción en MS Access. Lo bueno de este código es que puede asignar un nombre a sus campos en Excel como lo desee (si usa *, entonces los campos deben coincidir exactamente entre Excel y Access), como puede ver, he nombrado una columna de Excel "Haha" a pesar de que la columna de acceso se llama "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