Push data from Excel to Access database

问题:

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         |
                 ------------------

回答1:

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

回答2:

I have tried @Remou's suggestion but never managed to have it work with my configuration (Office 2010). Here's what I have stolen from Microsoft Dev Forums which works perfectly for me with every type of SQL query (select/insert/delete, etc.)

Public Sub updateAccess()

Dim con As New ADODB.Connection
Dim connectionString As String

Dim sql, newTable As String
Filename = "C:\test.accdb"
connectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" & Filename

con.Open connectionString

' Save current table ("BEFORE") to another table ("BEFORE_yyyymmdd_hh_mmss")
newTable = "BEFORE_" & Format(Date, "yyyymmdd") & "_" & Format(Now, "hhmmss")
sql = "SELECT CODE, STORE INTO " & newTable & " FROM BEFORE"
con.Execute sql

' Delete rows of current table ("BEFORE")
sql = "DELETE FROM BEFORE"
con.Execute sql

' Insert new rows into current table ("BEFORE") from my Excel Sheet
sql = "INSERT INTO BEFORE ([CODE], [STORE]) " & _
      "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[" & ThisWorkbook.Sheets("CODE_BY_STORE").Name & "$]"
con.Execute sql

con.Close
Set con = Nothing

End Sub

This works like a charm! Hope this helps...