Append Excel data into existing Access Table

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Append Excel data into existing Access Table

  1. #1
    New Member
    Join Date
    Aug 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Append Excel data into existing Access Table

     
    Hi Team,

    I am an intermediate VBA programmer in Excel. I do have knowledge about access tables and queries and I do know that it is easier to import excel tables from Access using import function.
    Is there any efficient way to append excel tables into existing access table with the same table format (considering that access and excel table is a mirror to each other) via VBA in excel on monthly or weekly basis?

    Kindly mention the VBA Objects to be used(Tools/VBA Reference) in vb editor if required.

    Regards and thanks in advance.

    Praloy

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Append Excel data into existing Access Table

    The way that I prefer to do it is to import the Excel data into a specific import table, and then append and / or update from there to the main table. That lets you do any required data checks / validation / cleanup before putting the data into the 'real' table.

    First, create an import specification by manually importing the data using the External Data features.
    To import Excel data into Access use Docmd.TransferSpreadsheet. You can reference the Import Spec so that the data types are defined correctly, and ignore any columns that you don't want to import.
    To select the Excel file to import you can use Application.FileDialog

    You may find this tutorial helpful.

    Hope that gets you started.
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Aug 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Excel data into existing Access Table

    Hi There,

    Thank you for responding this question. I do know how to import the data from Access manually.
    But My question is how do i use macro in excel to append/export to Access table.
    This requirement is for automation, which i just have to run the macro in excel to append on a monthly or weekly basis.

    Regards,
    Praloy M Sangma.

  4. #4
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Append Excel data into existing Access Table

    Did you look at the tutorial? It covers creating the Import Spec, and writing / running code to import the files, using the specification you created.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  5. #5
    New Member
    Join Date
    Aug 2009
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Excel data into existing Access Table

    Hi Denis,

    Yes the link you have shared did have both creating and appending tables into access by using excel vba. I really liked the sample file which you have shared to download, it made easier for me to understand the code better.

    Thank you for sharing the link and I appreciate your help on this. May God bless you.

    Regards,
    Praloy M Sangma.
    Last edited by Praloy Sangma; Jun 30th, 2011 at 10:58 PM.

  6. #6
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Append Excel data into existing Access Table

    Thank you, you are welcome

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  7. #7
    New Member
    Join Date
    Dec 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Excel data into existing Access Table

    Hi There!

    I'm using Access 2010 and I got run-time error 2455: You entered an expression that has an invalid reference to the property FileSearch. Any ideas what I'm doing wrong?

    Thank you!



    Quote Originally Posted by SydneyGeek View Post
    Thank you, you are welcome

    Denis

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,636
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Append Excel data into existing Access Table

      
    The FileSearch object seems to come and go in different releases of Access (as I recall). In any case, the thing to do is to replace that part of the code with something else that allows you to iterate through the files you want to import.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

User Tag List

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
  •  

 

 
DMCA.com