Using Excel VBA to export data to MS Access table

by

is it possible to export without looping through all records

For a range in Excel with a large number of rows you may see some performance improvement if you create an Access.Application object in Excel and then use it to import the Excel data into Access. The code below is in a VBA module in the same Excel document that contains the following test data

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

More Related Contents:

  1. SQL Query output in VBA is different than in SQL Oracle
  2. How do I use parameters in VBA in the different contexts in Microsoft Access?
  3. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  4. Performing SQL queries on an Excel Table within a Workbook with VBA Macro
  5. Accessing SQL Database in Excel-VBA
  6. Excel function to make SQL-like queries on worksheet data?
  7. Using Excel VBA to run SQL query
  8. How to use system username directly in MS Access query?
  9. Access – compare two tables and update or insert data in first table
  10. Query for getting value from another record in same table and filter by difference greater than a gap threshold
  11. How do I call an Oracle stored procedure from an Excel VBA script?
  12. How to save the result of a SQL query into a variable in VBA?
  13. Escaping ‘ in Access SQL
  14. sql in ms-access [closed]
  15. Managing and debugging SQL queries in MS Access
  16. How do I write a full outer join query in access
  17. Table-less UNION query in MS Access (Jet/ACE)
  18. How to run a SQL query on an Excel table?
  19. Non-web SQL Injection
  20. Ms Access Query: Concatenating Rows through a query
  21. Error: “Could Not Find Installable ISAM”
  22. Access-SQL: Inner Join with multiple tables
  23. Address standardization within a database
  24. Rounding in MS Access
  25. Generate sql insert script from excel worksheet
  26. Get length of array?
  27. Achieving ROW_NUMBER / PARTITION BY in MS Access
  28. Convert String to Date in MS Access Query
  29. HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]
  30. MSAccess – query to return result set of earliest rows with a unique combination of 2 columns

Leave a Comment