+ Reply to Thread
Results 1 to 9 of 9

Updating MS Access automatically from Excel ouput

  1. #1
    Registered User
    Join Date
    01-15-2021
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Updating MS Access automatically from Excel ouput

    Hi

    I'm hoping someone with more knowledge than me about this can help me out.

    Right now, I have a set-up where I have a big MS Access database with numerous queries. The whole database is based one one table (called Transactions), which I import from Excel that I receive daily. Right now, I have to manually import the Excel file through External Data -> Text file -> Today's Excel file.

    In an ideal world, I would be able to run this automatically. My question is therefore, if there is a way to automatically import the Excel file i'm receiving everyday to my Access database without having to click anything. I'm getting the Excel file from another program, which I have set up to receive daily in a specific folder. Is there a way for Access to import the Excel data into the Transactions-table so all the queries based on the Transaction-table can run everyday automatically?

    I have tried reading this forum and watching videos, and I think I may have to use some VBA. However I've not succeeded yet.

    Many thanks in advance,
    T

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Updating MS Access automatically from Excel ouput

    I think you'll need the VBA on the Access side ... Shall I move the thread for you?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Registered User
    Join Date
    01-15-2021
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Updating MS Access automatically from Excel ouput

    Sure. Thank you!
    Last edited by AliGW; 01-15-2021 at 08:21 AM. Reason: PLEASE don't quote unnecessarily!

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,644

    Re: Updating MS Access automatically from Excel ouput

    Done - it is now in the right place.

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,488

    Re: Updating MS Access automatically from Excel ouput

    Rather than import the file daily, suggest that you link the Excel file to Access. In this manner, any changes to the Excel Range/Table is automatically updated in the Access Data Base.

    Here is a short tutorial on how to achieve this. https://www.chelseadatamanagement.co...t-into-access/
    Alan


    Have you debugged your code?
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Registered User
    Join Date
    01-15-2021
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Updating MS Access automatically from Excel ouput

    Hi again.

    Thanks for the suggestion. It's seems to be working. Atleast it's automatically updating in Access when I change some numbers in Excel.

    I ran into a new problem. I have some fields in Excel which consists of both letters and numbers, e.g. HQ334. All these fields with letters in them will not import to Access.
    I feel like I might miss something really obvious, but do an of you know how I can fix this?

    Thanks,
    T

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,488

    Re: Updating MS Access automatically from Excel ouput

    Is the field in Access formatted as Text? If not, then it will probably gen errors.

  8. #8
    Registered User
    Join Date
    01-15-2021
    Location
    Texas
    MS-Off Ver
    2016
    Posts
    4

    Re: Updating MS Access automatically from Excel ouput

    EDIT: I think I found the solution by changing the Excel column formatting to text.

    Yes, it's says Numbers. However I cannot change it to short text, as I get an error when trying to. I suspect it's because it's linked to the Excel file as I have had no trouble changing formatting on all the other Access tables. Is there a way around this? Perhaps changing something in Excel?

    Thanks,
    T
    Last edited by MrT_; 02-02-2021 at 09:57 AM.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    21,488

    Re: Updating MS Access automatically from Excel ouput

    Have you tried unlinking the table, making the format changes and then re-linking?

+ 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. trying to push data from excel to access but access not updating
    By todaychris in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2014, 05:06 AM
  2. [SOLVED] Excel not updating Access linked data
    By hcyeap in forum Access Tables & Databases
    Replies: 2
    Last Post: 04-25-2014, 04:02 PM
  3. Updating certain forulas when Excel is linked with access
    By anthonyquinn in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-04-2013, 09:25 AM
  4. Updating Access record from Excel
    By P30500612 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-20-2010, 12:21 PM
  5. Updating Access tables from Excel sheets
    By mlewit1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2007, 04:26 PM
  6. Excel to Access: Updating records
    By alexwhite in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-16-2006, 10:40 AM
  7. [SOLVED] Updating Excel forecasts into an Access Database
    By Maree Maxfield in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-07-2005, 12:00 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