Results 1 to 2 of 2
  1. #1
    Erictsang is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    154

    Importing data to Access in a timely manner

    Dear all



    I have a problem that i do not understand and would like to get an advice


    I have a table as the sources of data which is extracted from company system.

    So i import once , got everything and i use an ID number as the primary key.

    I append it to the data base. Data duplicated. but i follow the ID sequence.

    Did i did i wrong, or i should use append query??

    Eric

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you are doing it fine.
    But if you are using AUTONUM as the ID, then you will get duplcates.
    IF your data has its own key , like a ClientID already in the data , then you do not need the autonum.
    You would key the CLIENTID field to prevent duplicates.

    Do you have a key field in your data?

    If NOT, then you must do this:
    I save my import data as it is, AND as a generic import file ,with the same name everytime: File2Import.xls
    Link this file as an external table. (then you just overwrite it with the next download)
    now make 2 queries,
    Q1, qaImportNew
    this query imports ONLY new data that does not exist. This is done with an outer join to your main data table
    join the 2 tables, tFile2Import and tData on a name , then make an outer join to show records that do not exist.

    Q2, quUpdExisting.
    this query updates the existing items already there.(they all are now) and updates data (it does not append)

    put it all in a macro,
    1. overwrite existing File2Import.xls with new data
    2. run macro
    done.

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

Similar Threads

  1. Importing data from PDF to Access
    By RAYHIGHERS3 in forum Access
    Replies: 1
    Last Post: 09-09-2015, 09:37 AM
  2. Importing data from PDF to Access
    By jigg14 in forum Import/Export Data
    Replies: 1
    Last Post: 02-10-2012, 12:57 AM
  3. Importing Data from Excel to Access
    By dkatorza in forum Import/Export Data
    Replies: 3
    Last Post: 11-05-2011, 09:17 AM
  4. iMPORTING DATA FROM EXCEL TO ACCESS
    By Rameez in forum Import/Export Data
    Replies: 7
    Last Post: 06-16-2009, 12:05 PM
  5. creating a report in this manner
    By jiwatramani in forum Reports
    Replies: 1
    Last Post: 11-07-2008, 11:39 PM

Posting Permissions

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