Results 1 to 12 of 12

how to import from excel file the NEW RECORDS ONLY into access table

  1. #1
    jkrykewycz is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2016
    Posts
    3

    how to import from excel file the NEW RECORDS ONLY into access table

    i have created a very simple database in access for reporting sake. our data is collected and manipulated in excel (google sheets/form for collaborative purposes). I copy and paste from sheets into an excel form with the hopes of automating the import process. i have created the table in access by importing the data. if there is a way to import into the table without creating duplicates (Append) or creating a new table (which will delete relationships) Im all ears...just keep it in simple terms. I DONT know code, and im not a programmer. i learn fast... Id rather not copy every line and paste them as they are entered into excel.



    Thanks in advance!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,637
    Can you create an append query that will write the records to the table? If so, do that and in design view of the table, select one or more table fields for which you can create a unique index. You can create one index or a composite index. In the first case, one field cannot have more than one row with the same value (such as OrderID). In the second, you might allow two divisions to have the same order id, but as a pair they can still be unique. In a 2 field composite index, this would be allowed because taken as pairs, they are unique.
    DivisionID OrderID
    ABC 123
    ABC 456
    DEF 123
    DEF 456

    Once you have that created, I presume you'll use macros to append the records. You'd turn off warnings at the start, append the records, and turn warnings back on. Thus there'd be no system message saying duplicate records are not allowed, and the process will append those that don't violate your index rules. The rest will be ignored.

    The alternative of course, is to use code. I'll bet there are hundreds of code samples already posted on the web for this, but we can probably help with that too.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    jkrykewycz is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2016
    Posts
    3
    so i did it 2 ways...1)linked my excel worksheet to a table and used that table and the append query to add new records to my "main" table (i indexed two columns, the ID and the Name(full name)...only, the table linked to excel doesnt have an ID "autonumber" column, so the name is the only field indexed and needs to always be unique (which might be hard to prevent). i gues as long as i keep that in mind im good...2) i saved my External Data>append to table process and can select that (as long as my excel worksheet contains the new info)...both worked. Thoughts?

    im taking it from sheets to excel to access...can i get it straight from Google sheets to Access...easily?

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,637
    Sound good. The only caveat is if there are no warnings, how do you know that some records were not appended, thus might need investigating?
    As for your last question, I confess I know nothing of Google sheets. Sorry.

  5. #5
    jkrykewycz is offline Novice
    Windows Vista Access 2003
    Join Date
    Jun 2016
    Posts
    3
    ok. thanks for the help!

  6. #6
    Sealink is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2017
    Posts
    3
    May I please have any code that will delete duplicate information that is being appended to a current worksheet?
    I created a database so that my team could track correspondence and other information that is routed through me. However, I want to be able to finalize a product so that I can share this with other Counselors. I'll need to simplify the process enough that they can just update by importing information. Does this make sense?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,637
    I think you didn't adopt the suggestions from my first post, else there could be no duplicates in your table. The fact that you wrote "being appended to a current worksheet" is a bit confusing. I thought you were putting the data into an Access table. If you're asking for code to delete duplicate worksheet data, that's off topic and more related to an Excel forum as you'll have to provide all your pertinent workbook information.

    If you're actually meaning a table and not worksheet, if you cannot implement those suggestions for some reason, then use the query wizard to create a Find Duplicates query. You can use that to delete the dupes.

  8. #8
    Sealink is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2017
    Posts
    3
    Thank you for your reply! Apologies! I wasn't very clear. I'm trying to import/append an Excel sheet INTO a Microsoft Access Database. I want to be able to pull information from another system as new people are filtering in, instead of entering everything in by 'hand' and possibly mis-typing a date. Maybe even having the data overwrite anything that changes.

    Example: PRD is 01May2018 in MY Database, but the person has extended a year so their PRD is 01May2019 (which has updated to the database I pull from). Would this be possible or be better to do by hand?

    My database is to track quite a few things in the Workplace and this will help my team keep track of what's going on with their personnel. This also helps when the Internet/website goes down. As I want to forward this to my other Counselors when I'm done, I need something that will be relatively pain free. The easier it is for them to Import information, the better.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,637
    Rereading to try to get straight - if asked, I would have said this was not possible
    linked my excel worksheet to a table
    Did you consider just linking to the worksheets from Access and populating an Access table with the records? Or are these worksheets/workbooks frequently being replaced with files of different names? Seems to me this all may have started on square 3 or so, instead of 1 - which would be 'how to get Excel data and manage in Access' or something like that. There are several ways.

    I think you have created 2 separate indexes, not one composite index using 2 or more fields, which would explain why you have duplicate data. After you take care of that and either use the find duplicates as noted to remove redundant data or start over, I'm not sure where you need to go from there. I also think the scope of what you need to do is way bigger than the question you posed. You might want to Google something like the aforementioned in this post and see if you've chosen the right path for your circumstances.

  10. #10
    Sealink is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2017
    Posts
    3
    I went ahead and googled more and found some possible avenues:
    https://support.office.com/en-us/art...8-42b6444619b7

    I think this will have my answer. Worse comes to worse, there'll be a two step process until I can figure out how to consolidate the steps.

    Thank you for taking the time to discuss this with me.
    Last edited by Sealink; 04-26-2017 at 09:59 PM. Reason: Found answer.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,525

    Exclamation

    @Sealink
    FYI, what you have done by posting your question under someone elses post is called "Hi-jacking a post".

    If you have a question, start your own thread and if there are posts close to your problem, you can reference those posts in your thread.
    More people will see the post - has your name and your subject - AND it will be current. Note that the thread by jkrykewycz (the OP) is almost a year old!!


    Good luck with your problem.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,637
    How the h-e-double hockey sticks did I miss that?
    Another name to add to my blacklist...
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Newbie Import. Update Field of Existing Records from Excel File
    By gedwards913 in forum Import/Export Data
    Replies: 8
    Last Post: 03-12-2015, 07:53 PM
  2. Replies: 3
    Last Post: 10-08-2014, 11:05 AM
  3. Import Excel file into a table
    By lucky33 in forum Import/Export Data
    Replies: 2
    Last Post: 09-03-2014, 01:17 PM
  4. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  5. Replies: 5
    Last Post: 01-29-2013, 06:00 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums