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

 

[Norie]

Have you tried using the sheet name on it's own?

Have you tried enclosing the sheet name in [] and appending a $, e.g. [sheetname$]?

 

[JackDanIce]

I've tried but same error, making me guess it's syntax related. Currently the write query reads as:

INSERT INTO tbl_Data_ORIG SELECT * FROM [Excel8.0;HRD=YES;DATABASE=G:\Finance\forecast\Access\Data_Access_Read_Write_Test.xlsm].[Data$]$A$2:$K$6

The range I want to insert into tbl_Data_ORIG is Data!$A$2:$K$6, what change do I need to make to this query?

 

[Norie]

Have you tried including the header row?

PS If you are able to have you tried manually running the SQL in the Access database?

 

[JackDanIce]

Including the header row doesn't resolve (same error thrown up)

I'm not sure who I'd run this from Access, do you mean to test/resolve just the SQL query? If so, I'm not sure how to write:

"INSERT INTO tbl_Data_ORIG SELECT * FROM [source]<source>"

When I replace <source>[source] with any variation of below, it errors:

[Excel8.0;HRD=YES;DATABASE=G:\Finance\forecast\Access\Data_Access_Read_Write_Test.xlsm].[Data$]$A$2:$K$6
[Excel8.0;HRD=YES;DATABASE=G:\Finance\forecast\Access\Data_Access_Read_Write_Test.xlsm].[Data]$A$2:$K$6
[Excel8.0;HRD=YES;DATABASE=G:\Finance\forecast\Access\Data_Access_Read_Write_Test.xlsm].[Data$]$A$1:$K$6

 

[Norie]

How about trying with a (temporary?) named range?

PS Noticed a slight type, HRD=YES should be HDR=YES.

 

[JackDanIce]

Thanks Norie, spotted that after posting, corrected it but no joy

Will try with named range and reply back

 

[welshgasman]

Use a recordset?

https://www.exceltip.com/import-and...-access-ado-using-vba-in-microsoft-excel.html

Or with a query

https://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/


HTH

 

[JackDanIce]

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.

 

[JackDanIce]

Changed the query to this:

Write_Query = "INSERT INTO tbl_Data_ORIG SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].[Data$]" & Data_String_Address

Giving error "Syntax error in FROM clause"

If I name data range "Data_Rng" and use this as my query:

Write_Query = "INSERT INTO tbl_Data_ORIG SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & ThisWorkbook.FullName & "].Data_Rng"

It does update data into the correct table.

Need to test some more but appear to have found a solution, thanks for all help and suggestions above