VBA write data from Excel to Access

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
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

Rich (BB code):
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
I've tried but same error, making me guess it's syntax related. Currently the write query reads as:

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
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:
Rich (BB code):
"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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,392
Office Version
365
Platform
Windows
How about trying with a (temporary?) named range?

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
Thanks Norie, spotted that after posting, corrected it but no joy :(

Will try with named range and reply back
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
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.
 
Last edited:

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,400
Changed the query to this:
Code:
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:
Code:
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 :)
 

Forum statistics

Threads
1,084,776
Messages
5,379,821
Members
401,629
Latest member
LEMANOIS

Some videos you may like

This Week's Hot Topics

Top