+ Reply to Thread
Results 1 to 2 of 2

Update Access database from excel worksheet data through SQL in VBA Macro

  1. #1
    Registered User
    Join Date
    11-21-2015
    Location
    India
    MS-Off Ver
    2007
    Posts
    13

    Update Access database from excel worksheet data through SQL in VBA Macro

    I am trying to update Access database from excel worksheet data through SQL Update statement in VBA macro. But I am getting syntax erro in Update statement. The access table name is "Receiving". The code is as follows :

    Private Sub Receiving_Update()
    Set cn = CreateObject("ADODB.Connection")
    DBPath = "C:\Anand\Others\Excel\Access to Excel" & "\Payment_Management2.accdb"
    dbWb = "C:\Anand\Others\Excel\Access to Excel\Payment Management.xlsm" ' file name to be changed
    dbWs = "Data Entry"
    scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath
    dsh = "[" & "Data Entry" & "$]"
    cn.Open scn

    Dim sSQL As String

    sSQL = "Update Receiving "
    sSQL = sSQL & "set (b.[GSK_Rec_Dt] = a.[GSK_Rec_Dt]),(b.[Fin_Rec_Dt] = a.[Fin_Rec_Dt]),(b.[Inv_No] = a.[Inv_No]) from [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh & Chr(32) & "a" & Chr(32) & ",Receiving b " & Chr(32) & "where b.[PRDOC] = a.[Receiving_No]"

    Debug.Print sSQL
    cn.Execute sSQL


    End Sub

    I am not able to understand the reason for syntax error. Please help.

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Update Access database from excel worksheet data through SQL in VBA Macro

    Can you run it and Post the value of sSQL after its been created?
    If someone has helped you then please add to their Reputation

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need Help Exporting Data From an Excel Worksheet to an Access Database
    By Gabe Whisonant in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2015, 07:28 PM
  2. Insert delete and update from excel userform to access database - help
    By rdm52 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2015, 09:45 AM
  3. Replies: 10
    Last Post: 07-26-2013, 11:52 PM
  4. Replies: 2
    Last Post: 07-05-2013, 09:37 AM
  5. Update/Delete a Record in an Access 2010 Database using Excel VBA
    By tomlancaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2012, 12:36 PM
  6. Update Access database through changes in Excel worksheet
    By munkayboi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2007, 07:16 PM
  7. Replies: 5
    Last Post: 06-11-2006, 04:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1