+ Reply to Thread
Results 1 to 11 of 11

Excel VBA writing to Access table

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,183

    Excel VBA writing to Access table

    If I add a column to an access table (column 10) and I have an existing array of record IDs from an excel vba process, what would be the protocol for updating those specific records in that column. I want to mark those records to exclude them from the next search of that access table.
    I assume that this is better practice than deleting the records although the record is passed to another table so deleting original is an option.
    Any pointers appreciated

  2. #2
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    I'd probably run an update query using an IN clause for the record ID.
    Rory
    I drink, and I know things

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,183

    Re: Excel VBA writing to Access table

    Hi Rory
    I'll take a look, would that be akin to looping records or would the update query amend multiple records in one pass?

    Thought this would work but error occurs on field update - records do exist

    Please Login or Register  to view this content.
    Last edited by nigelog; Yesterday at 06:27 AM.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    It can do multiple in one pass. The syntax is basically something like:

    Please Login or Register  to view this content.
    (I had to put a space in the word 'update' to get past the firewall)

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,183

    Re: Excel VBA writing to Access table

    Thanks Rory i'll have a look at solving the syntax. In the meantime I managed to get this to work with a loop
    Please Login or Register  to view this content.
    on the subsequent refresh query although code looks as if should retrieve records where column delivered is not "X" but it returns records where it does = "X" . Any idea of syntax for search
    Please Login or Register  to view this content.
    Sorted
    Please Login or Register  to view this content.
    Last edited by nigelog; Yesterday at 09:00 AM.

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    I can't see why you are using wildcards there:

    Please Login or Register  to view this content.
    should be enough.

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    FWIW the update query would just be something like:

    Please Login or Register  to view this content.

  8. #8
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,183

    Re: Excel VBA writing to Access table

    Hi Rory, thanks for bearing with this
    if I enter that line with the space omitted from update It gives an Expected end of statement and highlights the X

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    Sorry - should be single quotes either side of the X

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,183

    Re: Excel VBA writing to Access table

    Hi Rory
    thanks I'll look into that. At the moment as the max number of records to amend in one pass is 5 (to fit a printed form) the above method will do until I get more adventurous
    I can seem to edit first post to mark as solved

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    5,933

    Re: Excel VBA writing to Access table

    Does the Thread Tools link at the top not work?

+ 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. Writing the loop for updating multi-table MS access file using multi-sheet excel
    By relabz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-29-2016, 03:10 PM
  2. Use an Excel Macro in Access VBA - Import text file in Access Table
    By joogibabu in forum Access Tables & Databases
    Replies: 3
    Last Post: 10-08-2014, 10:04 AM
  3. Restrict Writing Access To Excel Spreadsheet
    By saucey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2012, 04:09 AM
  4. writing excel VBA Query for fetching data from Access
    By vikas.bhandari in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-16-2007, 05:47 AM
  5. Writing an Excel macro to place Access fields in specific cells.
    By mdub72 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-06-2007, 01:53 AM
  6. [SOLVED] retain table formatting when writing excel table to a txt file
    By deanop in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-03-2005, 01:05 AM
  7. [SOLVED] Writing Excel to Access
    By mburkett in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-20-2005, 06:05 AM

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