VBA write data from Excel to Access

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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$]?
 
Upvote 0
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?
 
Upvote 0
Have you tried including the header row?

PS If you are able to have you tried manually running the SQL in the Access database?
 
Upvote 0
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:
Upvote 0
How about trying with a (temporary?) named range?

PS Noticed a slight type, HRD=YES should be HDR=YES.
 
Upvote 0
Thanks Norie, spotted that after posting, corrected it but no joy :(

Will try with named range and reply back
 
Upvote 0
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:
Upvote 0
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top