Syntax Conundrum

[hbl]

Here's my problem, and I surely hope it's best that I post this here, rather than on the SQL Server board.

I have a SQL Server stored procedure that is set up as follows:

Insert Into myTable Select * from firstTable

This will insert the values from firstTable into myTable, and it will work through everything but access.

In access I use the .execute() method with a QueryDef, i get an error saying that it cannot execute a "select" query because this returns rows. This is not the case, because the select is actually being inserted into the temporary table.

Any ideas as to how I can trick access into seeing this Stored Procedure for what it truly is?

Thanks for all of your help.

[MMcCarthy]

You have posted this question in the Articles section. I am moving it to the Access forum.

ADMIN

[MMcCarthy]

Access won't accept the syntax ...

 Insert Into myTable Select * from firstTable

You will have to actually name the columns.

 Insert Into myTable (Column1, Column2) Select Column1, Column2 from firstTable

This will work with RunSQL

[MMcCarthy]

Access won't accept the syntax ...

 Insert Into myTable Select * from firstTable

You will have to actually name the columns.

 Insert Into myTable (Column1, Column2) Select Column1, Column2 from firstTable

This will work with RunSQL

It should work with .Execute as well.

[NeoPa]

Here's my problem, and I surely hope it's best that I post this here, rather than on the SQL Server board.

I have a SQL Server stored procedure that is set up as follows:

Insert Into myTable Select * from firstTable

This will insert the values from firstTable into myTable, and it will work through everything but access.

In access I use the .execute() method with a QueryDef, i get an error saying that it cannot execute a "select" query because this returns rows. This is not the case, because the select is actually being inserted into the temporary table.

Any ideas as to how I can trick access into seeing this Stored Procedure for what it truly is?

Thanks for all of your help.

To execute a remote SP in SQL from Access you need to do it as a Pass-Through query (which is explicitly SQL).