VBA write data from Excel to Access

JackDanIce

Well-known Member
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
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
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
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
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
How about trying with a (temporary?) named range?

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

JackDanIce

Well-known Member
Thanks Norie, spotted that after posting, corrected it but no joy :(

Will try with named range and reply back
 

JackDanIce

Well-known Member
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
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 :)
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top