Results 1 to 13 of 13
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Look for values on import (2002)

    Hi,

    I'm working with importing files into Access from Excel. Thanks to Han's that piece is working fine. I would like to prohibit the user from importing data that already exists, so as to not duplicate final suns. The data is entered based on a date range. I'd like to have code look to see if a date has already been imported, and if so there would be an error message that states the data has already been imported and the option of do they wish to continue.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    You need to fill in some background. How are you importing the data from Excel? Are you creating a temporary table, importing into an existing table, or what? If there is a previous thread that contains the explanation, post a link to it by using the tag <code><!t>[Post#=<postnumber>]<!/t></code>
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Hi Charlotte,

    The info at this point is being imported into an already existing table. The data needs to be added to on a monthly basis from the excel spreadsheet. At this point the info is imported via a macro, but I'm planning to work the code Han's gave me so that I don't have to rely on the macro. The thread with the code from Han's is <post#=<416483>>post <416483></post#>

    Thanks,
    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    But in one case, that code is deleting the data before importing the new data from Excel. In the other it's appending the import. You are talking now about a date range and checking for existing records. If the date range already exists, do you want to delete the records from the table and append new ones or add possible duplicates? It isn't the code I'm questioning, it's the logic you want to apply. And can you explain what you mean by a date range? Are you talking about testing for records with dates within that range or for duplicates of existing records? Do the records contain actual dates or something like month and year?
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for values on import (2002)

    I would create a link to the Excel spreadsheet in the Access database (File | Get External Data | Link Tables...)
    Create a query based on the linked table that selects records for the appropriate date range, then change it into an append query to add the selected records to the Access table. You can run this append query manually or from a macro (OpenQuery action) or code (DoCmd.OpenQuery).

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Hi Charlotte,

    Sorry to confuse you. When you asked for the link I thought you wanted to see what code I was referring to from Han's. I had asked at that point for code that would delete preexisiting data prior to import. No wonder you were curious as to my rationale.

    In this instance the information from the excel file would be being added to access on a monthly basis. Our software that we now use for scheduling clients exports the data they use to build their reports (in SQL) to excel so we have the option to further manipulate it for our own specific purposes. I've been doing this in excel for a number of years and then adding the monthly data to another spreadsheet in order to run pivot tables that allow me to trend statsover time. The spreadsheet is getting large and unstable to say nothing of slow. I am looking to use Access for storage as well as other reports etc. Primarily I want to be able to pass this monthly task to someone else, which is why I need to be sure they don't import the data twice as it will throw off all of the numbers.

    Hope that explains the rationale a little better. Thanks for the input,
    Leesha

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Hi Hans,

    Ok, this gives me an idea on how to start. Since this needs to be automated so a data entry person can do this, does this sound like I'm on the right track. The code would run in this order:

    1. Link to excel and pull data into a temporary table
    2. Query to pull out data needed
    3. Append query to place the date range needed into the table that is being added to each month.

    If this is correct, then the piece I'm confused on is what prohibits the user from adding the date range twice by accident? For instance if Jan 2004 data is already added and they forget or push the command button twice, how do I stop the code and prompt with an error message? In excel I have the spreadsheet look to see the last date added. If it matches the new info going in than there is an error message stopping the user.

    Thanks,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for values on import (2002)

    There is no need to use a temporary table; you can run a query directly from the linked Excel table.

    Let's say the linked Excel table is named tblExcel, and the existing table in Access is named tblAccess. Both contain a date field named MyDate.
    Create a query based on tblExcel.
    Add the fields you need to import into tblAccess to the query design grid.
    Enter the following in the Criteria line in the MyDate column:

    Not In (SELECT DISTINCT MyDate FROM tblAccess)

    Switch to datasheet view. You should only see those records from tblExcel whose date doesn't occur in tblAccess.
    Switch back to design view.
    Select Query | Append Query... and specify tblAccess as the table to append the records to.
    If necessary, specify or modify the fields to append to.

    If you run the query, it will only append records from the Excel table that have no matching date in the Access table. If the user runs the query twice in a row, there will be no records to append the second time, because all dates in the Excel table already have a match in the Access table.

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Hi Hans,

    I need to clarify as I should've said the information in the excel file is being imported into the access table. I was incorrect to say its being linked. So with this said, how would I proceed? I've been using a macro to import the data.

    Leesha

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for values on import (2002)

    My proposal is to switch to a linked table plus an append query, as outlined in my previous reply. This replaces the import you do now.

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Morning Hans,

    Unfortunately it won't work to link the data. Part of the reason I'm pulling the data into Access each month is that the spreadsheet that presently holds it is getting towards the maximum number of lines that Excel will hold per spreadsheet and its blowing up frequently, requiring that I spend a lot of time fixing it when other users run into issues. There are other issues with linking vs importing this particualr database that I won't wore you with here.

    Thanks anyway,
    Leesha

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Look for values on import (2002)

    I don't know if importing to a temporary table will work if linking won't, but you could do that:
    - Import Excel spreadsheet to temporary table
    - Run query to append non-matching records from temporary table to the Access table.

  13. #13
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Look for values on import (2002)

    Just to let you know that this worked exactly as I hoped it would!!!

    Thanks so much. I'm tickled pink!

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •