Results 1 to 9 of 9
  1. #1
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14

    Importing from Excel 2007 into Access 2007

    Hi, I need to import data from Excel into a table in Access and I need to be able to look at the table and readily identify the records I just imported. If Access would import the cell color for the new records, that would be great but I don't know how to make Access do that. Is there a way or can anyopne offer another solution?


    Thanks in advance for any help,
    Jerry

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    No, you cannot import cell color into Access.
    Usually, the best way to determine which records have already been imported is through the use of a unique field (or combination of fields that create a unique entry).

    If you can explain in a little more detail exactly what you are trying to do (and why, what the end goal is), we may be able come up with some other suggestions for you.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would populate a field during entry that identified the records. Date imported or date and time imported come to mind. That could be done with an append or update query, or a default value.

    Oops, sorry Joe. Your post wasn't there when I started typing (yeah, I'm slow).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Oops, sorry Joe. Your post wasn't there when I started typing (yeah, I'm slow).
    No worries Paul! I am quite the slow typist myself, and have done this many, many times myself (probably to you on occassion, I imagine!).

    I would populate a field during entry that identified the records. Date imported or date and time imported come to mind. That could be done with an append or update query, or a default value.
    I was going to suggest if this is just an import into an empty table, maybe they could use Excel row number (by placing =ROW() in Excel and copying down for all rows before importing).
    Or if the table they are importing to already has data in it, where the ROW number may not be unique (because of past imports), maybe they can do a combination of row number and current date, i.e.
    =TEXT(TODAY(),"mm-dd-yyyy") & "_" & ROW()

  5. #5
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14
    Sorry I got tied up for a while and couldn't reply. Before we get too deep into things, I know I can change the color scheme in an existing Access table. When I import into the table, will Access automatically apply the color scheme to the records added or will they appear with no fill color? If they appear with no fill color, that would serve my purpose.

    I am importing into an existing table (call it Table 1) and I want to be able to quickly identify the records imported. I will be adding new records each month. Having the date of the import appear would accomplish what I need, I think. I say I think because Table 1 is used in an append query to add the new records to Table 2 if they meet certain criteria. Will the append query still work if I add a field that is not currently in Table 2?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Joe appears to be offline goofing off.

    From the sound of it, you can add a date/time field to table 1 and give it a default value of Date(). When you import records into it, the date field should automatically populate. That gives you something you can query on to identify the records. That field wouldn't need to be in table 2 if you don't want/need it there. Your query is

    INSERT INTO Table2...
    SELECT...
    FROM Table1
    WHERE DateField = #9/5/12#
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Joe appears to be offline goofing off.
    That's right, at 5:00 PM EST, I am out of this joint and on the road home!

  8. #8
    jk1809 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Aug 2012
    Posts
    14
    Thanks Guys, that does the trick! I really appreciate your help!

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Export Table in Access 2007 to Multiple Workbooks in Excel 2007
    By hutchinsm in forum Import/Export Data
    Replies: 5
    Last Post: 03-01-2012, 05:23 PM
  2. Ms Access 2007 report export to excel 2007
    By Stan2man in forum Access
    Replies: 6
    Last Post: 11-23-2011, 01:24 PM
  3. Importing files into Access 2007
    By rjani1 in forum Access
    Replies: 6
    Last Post: 07-19-2011, 09:33 AM
  4. Importing Excel 2007 spreadsheet into Access 2002
    By jhjr in forum Import/Export Data
    Replies: 1
    Last Post: 06-17-2010, 02:05 PM
  5. Replies: 0
    Last Post: 11-17-2009, 02:35 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