Recording Excel ranges from several tabs into Access DB

[aribadabar]

Hello, I am new to VBA and would like to ask for your insight.

I have an Excel workbook with several tabs (named SP ,Q and IW) which contain tables(ranges) that are updated periodically via API.

I would like to record the contents of these tables (varying size of the ranges) into an Access DB every 5 min as they are getting updated without overwriting the existing records - just appending the latest ones to the DB.
Both the XLSX and the ACCDB file are in the same folder.

How to achieve this?

Many thanks for your assistance!


Below is what I managed to come up with which is not working at all.

Sub InsertRecord()

Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim DBPath As String
    Dim stSQL As String
    
DBPath = DBPath = ActiveWorkbook.Path & "\OptionsDB.accdb"

Set cn = CreateObject("ADODB.Connection")
    
        stSQL = "INSERT INTO OptionsSPY SELECT * FROM [Excel 12.0;HDR=Yes;IMEX=1;Database=" & C:\Users\konst\OneDrive\Desktop\Synapse-last.xlsx & "].[SP$D5:T63]"
        stSQL2 = "INSERT INTO OptionsSPY SELECT * FROM [Excel 12.0;HDR=Yes;IMEX=1;Database=" & C:\Users\konst\OneDrive\Desktop\Synapse-last.xlsx & "].[Q$D5:T60]"
        stSQL3 = "INSERT INTO OptionsSPY SELECT * FROM [Excel 12.0;HDR=Yes;IMEX=1;Database=" & C:\Users\konst\OneDrive\Desktop\Synapse-last.xlsx & "].[IW$D5:T50]"

    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & ";Jet OLEDB:RIDatabase"
    Set rs = cn.Execute(stSQL)

    Set rs = Nothing
    cn.Close
    Set cn = Nothing
Sub my_Procedure()
    Call loop
End Sub

Sub loop()
   Application.OnTime Now + TimeValue("00:05:00"), "my_Procedure"
End Sub

 

[alansidman]

Instead of VBA, why not just link the Worksheets to Access. In this manner, any updates to the worksheets are automatically updated to the Access Tables.