Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Append errors (Access 2000/2002)

    I'm trying to take append to a table by first setting up a query that is supposed to find unmatched records from table A in the traditional Access way:


    INSERT INTO OrdMain
    (Group_ID, CO_Number)
    SELECT CustHeader.Cust_ID + '000000' AS GroupID, CustHeader.CO_Number

    FROM CustHeader LEFT OUTER JOIN
    OrdMain ON CustHeader.CO_Number = OrdMain.CO_Number
    WHERE (OrdMain.CO_Number IS NULL)
    GROUP BY CustHeader.CO_Number
    HAVING (CustHeader.CO_Number <> 'MICKEY')[


    The CustHeader is the everything-since-the-dawn-of-time data and the OrdMain is the only-what-they-want-to-see data. I'm using this in a project against a SQL database. The old way it worked was that if there was, by any chance, a matching record, it would append the rest and give us either a table of paste errors or just not process the data. Now that we're using SQL, the stored procedure representing this append just fails.

    Iif I'm left joining two tables where the key field on the second table is null, shouldn't this result in just new records? Is this just an SQL thing? Should I be asking this in another forum? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Carpy Diem, it&#39;s .

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Append errors (Access 2000/2002)

    Hi Peggy,

    Sorry for not responding sooner - and you are in the right forum, as we don't have a separate SQL Server or ADP forum. How is the SQL sproc failing - does it give you an error message, crash Access, or just never append any records? As a debugging process, what you might try is running just the SELECT portion of the data and see what you get. Conversion of this sort of thing from Jet to SQL Server syntax is a wee bit tricky, and breaking it into two can help isolate where the problem is - I would be inclined to suspect the GROUP BY or HAVING clauses. Another way is to create a traditional ODBC link to the SQL Server tables, use the prior Jet query, and turn on the trace function in the ODBC driver and look at the SQL string that the ODBC driver creates from your Jet query. It's a bit of a messy process, but should give you some clues.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Append errors (Access 2000/2002)

    Wendell,

    I thought about this a little more and I'm going to try to not delete and re-append the data but append the new data and update the existing data. Wouldn't you think this would be the same thing as deleting and appending? I can't truncate and append, as there is some data in the table that needs to stay there "forever and eternity".

    By the way, I'm running a DTS and all it tells me is that it failed. When I run the query in the .adp it gives me that ever-so-helpful message "The stored procedure executed successfully but did not return any records." Then when I look at the table, there's a chance that the data wasn't really deleted after all. Then when I append, I think I'm appending all the data, when I'm only appending the new ones. Gets kinda confusing all around, espeicially when all the balancing reports show that the two applications balance, as far as record counts go. I'll let you know it this new way works or not. <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>
    Carpy Diem, it&#39;s .

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Append errors (Access 2000/2002)

    I don't think I know enough about your application to know which would be preferred, but in general an append for new records only would be more efficient than deleting and then reappending all the data. And I'm not sure what you mean by truncate - are you referring to it in the SQL Server syntax? By the way, I learned something I didn't know a few weeks ago - DTS actually uses the Jet engine for most of what it does! I'll share a couple of other things as well a bit later.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Append errors (Access 2000/2002)

    Wendell,

    What I mean by Truncate and Append is the way I've been informed is the best way to delete records and start over. Basically:

    Truncate Table T_Customer

    Insert into T_Customer(ID, CharName,MovieName)
    Select MovieID, Character_Name, Movie_Title
    from T_Movie


    This would first clear all the data out of the T_Customer table but retain the field names, field types, etc. and then the contents of T_Movie would be appended into T_Customer. Providing the field types matched and the tables exist, the code would be successful. However, if there are duplicate records in the T_Miovie table, then we end up with and empty T_Customer table and a failed insert procedure. Now these "duplicate" records could consists of movies that were reissued (so would have different reissue dates) but the T_Customer table doesn't need the date. This is one of my issues. The other is wiping out the data in the T_Customer table when we want to keep some of it for historical reasons.

    I was able to work something out so I'm updating the existing records (providing they don't represent closed items) and then adding the new records. If all goes well, this should solve this issue and I can go fight another application conversion gotcha.
    Carpy Diem, it&#39;s .

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: SQL Append errors (Access 2000/2002)

    That's what I was presuming, and yes it is more efficient that running a delete query. I understand now why you need to tackle that problem, but it seems you should be able to identify the ones that already exist and simply append only the ones that don't already exist - perhaps that's the approach you adopted. Anyhow, glad to hear it's working.
    Wendell

Posting Permissions

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