Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015
    Posts
    1

    Unanswered: Excel VB how use access xml

    I have one excel file ı want use access but ı dont know vb
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..well in which case now's a good time to start to learn VBA

    usually you can move data easily enough between MS Office applications. the real problem with importing from Excel into a database is that spreadhseets are, unless seriously well tied down a free for all format where users can add or delete rows / columns / cells, throw calcuations out of whack, enbter incorrect types of data (a classic being a string representation of numeric or temporal data, usign the wrong formats and so on. On top of that spreadsheets encourage de-normalisation so when you import data into a db you may have to move imported data into two or more tables. importing data into an existing db, assuming that you have defined relationships (and you should) you may easily get import errors as users create new values for related data.

    so in these sort of cases what I often tend to do, assuming that the process doesn't merit its own custom import process is to import the spreadsheet as is into a temporary table
    then using a suite of queries move the data into the tables (in realtionship order [eg customers, products, orders, order details, deliveries, deliveruy details etc...], so that you dont' get data thrown out because of relationship violations)
    ..those queries can be broken down into individuals steps
    ....first off add rows from the temporary table to the destination table where there is no match on the primary key/primary key data (ie if this is a new row)
    ....then delete those rows in the temporary table
    ....then (assuming you do want to overwrite existing db data with spreadsheet data) update the table rows where there is a match with the primary key or primary key data
    ....then deleted those rows int he temporary table
    ....any rows still remaining in the temp table are unknowns / orphans which require manul intervention

    if you have denormalised data in the temp table then you will need to be very very carefull as to the sequence of processing. say your spreadsheet contains details of customer orders, so
    Pass #1 could insert new customers into the customer table
    Pass #2 could update pre existing customer details (although how you will know that say Fred Smith Inc is the same customer as Fred Smith LLC beats me)
    Pass #3 could insert new products into the product table
    Pass #4 could update product details, althogh as above how you could identify that 4x500g cream cheese is the same as 8x250 cream cheese also defeats me.
    ..you may at this poing say oph but we have a customer number in the spreadsheet, and a product number or SKU... all fine and dandy, in whichcase why the feck re you allowing data capture in a spreadsheet whn it should be done in a db....
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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