can't append all the records in the query or ODBC--call failed

[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. The SQL tables have Primary keys and are link to each other one way or another as well. If I remove the keys and links it works fine.

If I used the db.excute with dbFailOnError I get an ODBC--Call 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

Is there an index that is holding the value? Help me to understand.

Thanks

 

[jrbarnett]

ODBC call failed normally indicates a data type mismatch between the source (Access) data and the target (SQL Server).

The easiest way around this is to make your temp tables in SQL Server, then use a pass through query in Access (or an SQL Server stored procedure called via ADO) to move the data across.

John