Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Appending Excel Data into Access Database

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Toronto, ON
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm stumped on a problem and hoping someone out there can give me some pointers. It deals with both MS Access and Excel. Here goes:

    I have an Access database with 20,000 rows of Item Numbers, and an Excel spreadsheet of 5,000 rows with Item Numbers. I have to search through the rows of Item Numbers in Access and see if they match with an item number in Excel. If the item number from Access matches with an item number in Excel, I have to append the entire row of data from Excel into the corresponding row in Access.

    On top of this, I'm supposed to flag the row with a matching Item Number in Excel if a match occurs.

    Any help would be greatly appreciated.

    Tony

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can import the excel file into your access database as a new table. this can be done by going to "get external data" in the file menu at the top left of the access screen and "import" not link. this will take you through a wizard that will make your excel workbook a table. then you can link the tables and do a query to bring together the information. Really shouldnt be too hard. write back if you need more info.
    Casey

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Ontario, Canada
    Posts
    337
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Check out this site it is similar to this one but for Access,

    http://www.access-programmers.co.uk/...assCookie=true

  4. #4
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-28 13:16, Casey wrote:
    You can import the excel file into your access database as a new table. this can be done by going to "get external data" in the file menu at the top left of the access screen and "import" not link. this will take you through a wizard that will make your excel workbook a table. then you can link the tables and do a query to bring together the information. Really shouldnt be too hard. write back if you need more info.
    Casey
    Hi Casey:
    In reference to your statement of importing a table from excel and then linking it! Technically you can link the excel(list or table) directly into Access without importing it.



    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A simple piece of ADO, DAO would handle this quite easily, if your intention is to automate the process, for example if you need to do it very often. If you want me to elaborate then give me some more precise details (especially the access table fields and data types).

    If you do not need to do it very often then the manual processes would work fine.


Some videos you may like

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
  •