SQL Append errors (Access 2000/2002)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote