VBA write data from Excel to Access

[JackDanIce]

Hi,

Due to large amounts of data, I'm working on a system with an Excel front-end and Access as data store.

For proof of concept/testing, I've successfully written code to read data from a given table with a specific field value, however, I'm struggling to write code (late biding) that pushes data to an Access DB table

When I run below, error message is "Syntax error in FROM clause", I suspect I'm not defining the data source correctly (see red below).

Struggling to find relevant answer online I can adapt, please can someone help correct? TIA, Jack

Sub Write_Data()

    Dim cn         As Object: Set cn = CreateObject("ADODB.Connection")
    Dim rs          As Object
    Dim strAdd   As String: strAdd = Data_String_Address
    Dim strda()  As Variant: strda = Database_Params
    
    With cn
        .Open strda(1) & ";Data Source=" & strda(2)
        .Execute Write_Query, , 1
        .Close
    End With
        
    Set cn = Nothing: Set rs = Nothing: Erase strda
        
End Sub


Private Function Database_Params() As Variant

    Dim a As Variant: ReDim a(1 To 2)
    
    a(1) = "Provider=Microsoft.ACE.OLEDB.12.0"
    a(2) = Range("Database_Path").Text
    
    Database_Params = a: Erase a
    
End Function


Private Function Write_Query()

    Dim msg As String: msg = "[Excel8.0;HRD=YES;DATABASE=" & ActiveWorkbook.FullName & "]." & Data_String_Address

    Write_Query = "INSERT INTO tbl_Data_ORIG SELECT * FROM " & msg
    
End Function


Private Function Data_String_Address() As String

    With Sheets("Data")
        Data_String_Address = .Name & "!" & .Cells(2, 1).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row - 1, 11).Address
    End With
    
End Function

 

[xenou]

Ty @welshgasman but both links rely on use of add-ins, namely:

Microsoft ActiveX Data Objects 6.1 Library
Microsoft Office 14.0 Access database engine objects

Which in turn means using early biding (instead of late binding, where variables are declared as objects) ; reasons given earlier for need to use late binding only.

Just FYI Those are not addins. They are the same libraries that are used with late binding. The only difference is whether the binding is early or late.

 

[xenou]

Mr Ice, when you say you are pushing data or writing data to Access like this, how much data are you writing? Is "Data Range" a cell, a few rows, or a few hundred rows, or tens of thousands of rows?

 

[JackDanIce]

Hi xenou - yes meant libraries, not add-ins. Same reason though, too many variable installs across User PCs and management want late binding.

Currently, the entire data set is ~12k rows

Set up as follows:

1 Client can have 1 or more jobs. [230 clients currently]
Each job has 1 or more transactions [max 12 x 16 = 192]

User interface has to be a cross-tab view on Excel with job details on veritcal (cols A:H) time across horizontal (12 months x 16 silo's)

I use various data controls/restrictions and macros with this to control user-interface, generate a normalised data table which is then written to a CSV file

Each silo has a manager and they read and aggregate from CSV files by silo to see the overal status for their team

With files on servers and need for historical tracking of data, initial ask was for pure Excel driven system, but server slowness has forced them to consider an Excel/Access system and I haven't built an Access database in years!!

Have figured out VBA to read and write data from Excel - Access, next is now understanding how to create tables in Access with correct relationships and how to do data change tracking as well as manager updates etc.

 

[xenou]

The point about addins vs libraries was just that whether you use late binding or early binding you still have dependencies, so avoiding dependencies isn't a reason to prefer one over the other (with late binding you can sometimes avoid dependencies on a specific version of a library but you can't avoid dependency on the library itself.

Still not sure how much data you are transferring but in any case be cautious. Personally I don't query open workbooks with ADO if I can avoid it - there used to be some weird bugs around that. But that was some years ago and maybe it's not a problem now. I would save the data to a separate file and then run the data transfer to Excel via that file. But if it was a small amount of data you could write a routine to move it to Access using an in memory copy of the data.

 

[Norie]

Jack

You mention an export to CSV, why not export the data in Excel you want to transfer to Access to a CSV and then import from there into Access?

 

[JackDanIce]

To xenou understand now about difference, ty

To both,

The current process is too slow for the managers to read and write data from ~230CSV files into an aggregate file (anywhere from 2mins+ when on a server), so doing the intermediary step of data to csv before Access would reintroduce the bottle neck.

The absolute restriction is the interface has to be in Excel, cross-tab view.

So trying to build an Excel-Access hybrid system to handle their revenue forecasting, the ADO code worked to read data (and believing avoiding need to have additional libraries) so continued with that option/connection to write data.

Now also need to include a historical trans-actional element to capture each read/write state to allow for data roll-back.