Invalid Operation when using Insert to SQL table

[cdck]

I have an Access front-end with some tables in a SQL back end. I can open and view the SQL table without issues, and there is a field marked as key in the SQL table. I can run a query to select data from this table. However, when I run the following query, I get "Invalid Operation":

[tt]
INSERT INTO dbo_TempSOcapture ( fsono, finumber, fpartno, flshipdate, forderqty, fstatus, funetprice, fcudrev )
SELECT dbo_sorels.fsono, dbo_sorels.finumber, dbo_sorels.fpartno, dbo_sorels.flshipdate, dbo_sorels.forderqty, dbo_sorels.fstatus, dbo_sorels.funetprice, dbo_sorels.fcudrev
FROM dbo_sorels;
[/tt]

If I run the SELECT section of the query on its own, I get no error. dbo_TempSOcapture is the linked SQL table mentioned above, dbo_sorels is another linked SQL table from another database (I am using tables in 3 locations in this project - 2 SQL databases and local tables).

Can anyone see anything wrong with my query, or explain the error that I am getting?

Thanks,

Cheryl dc Kern

 

[dhookom]

This may be caused by differences in the table constraints such as allowing null values or primary keys or referential integrity.

Have you tried performing this in a pass-through query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jsteph]

Also check things like NULL dates--sql server doesn't allow the 'null' Dec 30, 1899 in a smalldate field.
--Jim

 

[cdck]

I'm looking into all of the above. Some further odd behaviours have us restarting the SQL server tonight after the employees have left, but I am not sure if this will help me at all.

I've never used a pass-through query, and am not sure how to do that. Can anyone direct me to a resource on this subject?

I'll report more tomorrow when the server is restarted and I can determine more.

Cheryl dc Kern

 

[dhookom]

A pass-through query is created by selecting "SQL Specific" in the query design menu. You have the opportunity to select a DSN/connection and the actual SQL syntax must match your database server syntax. Access/JET doesn't provide any assistance or hinderance in running a P-T query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jsteph]

You could probably paste what you have above--but--do a find/replace on dbo_ and replace with blank. As long as they're in the same database (schema), you're ok with that syntax if the dsn has that database. You can always preface the statement with the USE [database name] statement if the dsn points to another db.
--Jim