将数据从Excel推送到Access数据库 [英] Push data from Excel to Access database

问题描述

我一直在寻找一种将数据从Excel推送到Access数据库的方法.我在下面找到了代码，但是我不知道如何改进它.

I was looking for a way to push the data from Excel to an Access database. I found the code below, but I have no idea how to improve it.

Dim wsQS As Worksheet
Dim sConnect As String
Dim sCommand As String
Dim adoCn As ADODB.Connection

Set wsQS = Worksheets("QueryStrings")
Set adoCn = New ADODB.Connection
sConnect = wsQS.Range("rngConnect").Value
sCommand = wsQS.Range("rngCommand").Value

' Get ADO connection to the workbook
adoCn.Open sConnect
' Append data from Excel worksheet
adoCn.Execute sCommand

' Close the connection to the workbook
adoCn.Close
Set adoCn = Nothing
Worksheets("CopyToDB").Range("DataToExport").Offset(1, 0).ClearContents
Worksheets("Proj DB").Activate

Set wsQS = Nothing

建筑

Architecture

                 ------------------
                |       web        |
                |       page       |
                 ------------------
                          |
                          |
                          |
            Python and BS4(Data Extraction)
                          |
                          |
                          |
                 ------------------                               
                |       Excel      |
                |       data       |
                 ------------------
                          |
                          |
                          |
             Python to Push Data(Oracle/Access)
                          |
                          |
                          |
                 ------------------
                |       Any        |
                |       DB         |
                 ------------------

推荐答案

如何在几乎任何地方(VBScript，VBA)运行并在Access from Excel中创建表的东西呢?您几乎可以运行任何有效的SQL.

How about something that will run from nearly anywhere (VBScript, VBA) and create a table in Access from Excel? You can run pretty nearly any valid SQL.

Set cn=CreateObject("ADODB.Connection")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\docs\test.accdb"

sSQL = "SELECT * INTO FromExcel " _
     & "FROM [Excel 8.0;HDR=YES;DATABASE=Z:\Docs\Test.xls].[Sheet1$]"

cn.Execute sSQL, recs

MsgBox "Records: " & recs

Jet SQL

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000

这篇关于将数据从Excel推送到Access数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！