Results 1 to 2 of 2

Thread: excel 2007 to access 2003

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Nov 2006
    Posts
    736

    excel 2007 to access 2003

    Hi Guys,

    Sorry for the absence of any code I am trying to figure this out. I have office 2003 and will be recieving excel 2007 xlsm. I can have this saved as csv as the file will have too many rows for 2003.

    I basically want to put this xlsm file into my access 2003 db. so my thoughts where once it is csv I can treat it like a database and import the records to a temp table in the database. then with sql i need to both insert any records that do not exist and update those that do in the main table. I know I can query the tables with a simple select but I suspect there may be a much more efficient way using joines or something.

    sub question: should i just do this direct from the csv or should i dump whole csv into a temp table and then work between the two?

    thanks in advance.

  2. #2
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: excel 2007 to access 2003

    in principle it is simple:
    attach the .csv to the access db
    to insert new records: insert into theaccesstable select * from the csvtable
    that will insert every record that will not cause a constraint conflict from the .csv into the access table
    records that caused a constraint conflict will be put in an import errors table
    what is very convenient because that will be the only records that may have been changed

    i would recomment using msaccess via 'external data' to do that, and not dao or ado
    because i dont think an errors table will be generated if you use dao or ado
    do not put off till tomorrow what you can put off forever

Posting Permissions

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



Click Here to Expand Forum to Full Width