By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,805 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

Syntax Conundrum

P: 2
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.
May 8 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You have posted this question in the Articles section. I am moving it to the Access forum.

ADMIN
May 8 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Access won't accept the syntax ...

Expand|Select|Wrap|Line Numbers
  1.  Insert Into myTable Select * from firstTable
You will have to actually name the columns.

Expand|Select|Wrap|Line Numbers
  1.  Insert Into myTable (Column1, Column2) Select Column1, Column2 from firstTable
This will work with RunSQL
May 8 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Access won't accept the syntax ...

Expand|Select|Wrap|Line Numbers
  1.  Insert Into myTable Select * from firstTable
You will have to actually name the columns.

Expand|Select|Wrap|Line Numbers
  1.  Insert Into myTable (Column1, Column2) Select Column1, Column2 from firstTable
This will work with RunSQL
It should work with .Execute as well.
May 8 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
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).
May 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.