Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: VBA write data from Excel to Access
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default VBA write data from Excel to Access

    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

    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 by JackDanIce; Oct 16th, 2019 at 10:31 AM.


  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,189
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA write data from Excel to Access

    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$]?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA write data from Excel to Access

    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?


  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,189
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA write data from Excel to Access

    Have you tried including the header row?

    PS If you are able to have you tried manually running the SQL in the Access database?
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA write data from Excel to Access

    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:
    Code:
    "INSERT INTO tbl_Data_ORIG SELECT * FROM [source]"
    When I replace [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 by JackDanIce; Oct 16th, 2019 at 11:14 AM.


  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,189
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA write data from Excel to Access

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

    PS Noticed a slight type, HRD=YES should be HDR=YES.
    If posting code please use code tags.

  7. #7
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA write data from Excel to Access

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

    Will try with named range and reply back


  8. #8
    Board Regular
    Join Date
    May 2013
    Posts
    666
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA write data from Excel to Access

    Office 2007
    Access novice. Sometimes trying to give something back

  9. #9
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA write data from Excel to Access

    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 by JackDanIce; Oct 16th, 2019 at 01:06 PM.


  10. #10
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    9,306
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA write data from Excel to Access

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •