How to import external data in access?

2 years ago
Olivia Parker
2 minutes

There are several methods you can use to import external data into an access database.

  1. Use the “Import” feature in Access: Click on the “External Data” tab, choose the file format such as “Excel,” “Text File,” or others, and then follow the prompts of the import wizard.
  2. Utilize the “Link Tables” feature in Access: under the “External Data” tab, select “Link Tables,” then choose the external data file you want to link (such as an Excel file), and follow the wizard prompts to complete the linking process.
  3. Utilizing the programming interface of Access (such as VBA), you are able to write VBA code to read external data files and insert the data into an Access database. For instance, you can connect to an external data source using ADO objects and use SQL statements to insert data into an Access table.

Here is an example of using VBA code to import an Excel file into an Access database.

Sub ImportDataFromExcel()
    Dim conn As Object
    Dim rs As Object
    Dim strSQL As String
    Dim strFilePath As String
    
    ' 设置Excel文件路径
    strFilePath = "C:\Path\To\Your\Excel\File.xlsx"
    
    ' 创建ADO连接对象
    Set conn = CreateObject("ADODB.Connection")
    
    ' 打开数据库连接
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Your\Access\Database.accdb;"
    
    ' 创建ADO记录集对象
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 构建插入数据的SQL语句
    strSQL = "INSERT INTO YourTableName (Field1, Field2, Field3) SELECT [Column1], [Column2], [Column3] FROM [Sheet1$] IN '" & strFilePath & "' [Excel 12.0 Xml;HDR=YES;IMEX=2];"
    
    ' 执行SQL语句
    conn.Execute strSQL
    
    ' 关闭记录集和连接对象
    rs.Close
    conn.Close
    
    ' 释放对象
    Set rs = Nothing
    Set conn = Nothing
    
    MsgBox "数据导入成功！"
End Sub

Please note that the code in the examples above only applies to importing data from one worksheet in an Excel file. If your Excel file contains multiple worksheets, you will need to adjust the code accordingly to select the correct worksheet.

Furthermore, you can also utilize other methods, such as using third-party tools or libraries to import external data into an Access database. The specific method chosen will depend on your requirements and technical proficiency.

bannerAds