can't append all the records in the query

[soujam69]

I've got an issue trying to do an INSERT INTO command from Access 2003 to a SQL server 2000 Database. All the 'temp' tables are Access tables and All others are SQL tables.

If I used the db.excute with dbfailerror I get an ODBC--Failed error.

If I use the following DoCmd.RunSQL I get the 'Can't append' error.

The First command will run, but the reset won't with key violations - but there are no key violations. If I wait for a period of time and switch the order then the one that ran previously won't and the new first one will.

Any help would be greatly appreciated. Below is my current code

intQuoteID = 354

DoCmd.RunSQL ("INSERT INTO dbo_Quote SELECT * FROM dbo_Quote_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges
DoCmd.RunSQL ("INSERT INTO dbo_QuoteDetail SELECT * FROM dbo_QuoteDetail_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges
DoCmd.RunSQL ("INSERT INTO dbo_QuoteSubRouting SELECT * FROM dbo_QuoteSubRouting_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges
DoCmd.RunSQL ("INSERT INTO dbo_QuoteRouting SELECT * FROM dbo_QuoteRouting_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges
DoCmd.RunSQL ("INSERT INTO dbo_QuoteGradeMakeup SELECT * FROM dbo_QuoteGradeMakeup_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges
DoCmd.RunSQL ("INSERT INTO dbo_QuoteQty SELECT * FROM dbo_QuoteQty_Temp WHERE QuoteID = " & intQuoteID & ";"), dbSeeChanges

 

[johnwm]

That looks like Access VBA, not VB - try forum181

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[PWise]

how about

DoCmd.RunSQL "INSERT INTO dbo_Quote SELECT * FROM dbo_Quote_Temp WHERE QuoteID = " & intQuoteID & ";"
    DoCmd.RunSQL "INSERT INTO dbo_QuoteDetail SELECT * FROM dbo_QuoteDetail_Temp WHERE QuoteID = " & intQuoteID & ";"
    DoCmd.RunSQL "INSERT INTO dbo_QuoteSubRouting SELECT * FROM dbo_QuoteSubRouting_Temp WHERE QuoteID = " & intQuoteID & ";"
    DoCmd.RunSQL "INSERT INTO dbo_QuoteRouting SELECT * FROM dbo_QuoteRouting_Temp WHERE QuoteID = " & intQuoteID & ";"
    DoCmd.RunSQL "INSERT INTO dbo_QuoteGradeMakeup SELECT * FROM dbo_QuoteGradeMakeup_Temp WHERE QuoteID = " & intQuoteID & ";"
    DoCmd.RunSQL "INSERT INTO dbo_QuoteQty SELECT * FROM dbo_QuoteQty_Temp WHERE QuoteID = " & intQuoteID & ";"

 

[soujam69]

That didn't work either. I believe it has something to do with the indexes on the SQL server even though the record no longer exist (I delete it just before I'm trying to insert from the temp table) it still runs into a validation problem.

If I remove the Primary Keys and the Relationships then it works.