• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

SQL views with parameter, select...into

Greetings to all Experts,
I hope that one of you can help me this afternoon.

I have an Access 2003 database in which I am trying to recreate a nonworking InfoMaker report.  The report references what I believe to be a view in a Sybase ARIA database.  

The view seems to have a parameter '@id' and will not run without an id being provided.  
I need this thing to allow me to query several hundred ids.

If I set an Access query and priivde a single id in the criteria row the view returns data.

It will not run when related to an Access table only containing the id.  
It will not 'SELECT ...INTO' (create a table) even if provided with an id.
Pass-through query with ReturnsRecords property set to True did not return any records (although if 'I SELECT * FROM vv_Name WHERE id = 1234' I get records).

It will not run with a subquery feeding it the id.

I am uncertain how to procede and seek my expert's advice.  
Thanks in advance for you time and trouble.
2 Solutions
I don't wait follow what you're doing. Are you trying to import the table info Access? Or dId you link the table and replaced the recordsource of the report with a query that refers to this table?.
Vadim RappCommented:
> Pass-through query with ReturnsRecords property set to True did not return
> any records

So set ReturnsRecords to False.
I meant I don't quite follow...  
This predictive text on my phone is unpredictable.  :-)
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

QlemoC++ DeveloperCommented:
SELECT INTO is treated as SELECT - which always returns a Resultset, even if empty. But SELECT INTO does not retrieve any rows, it is a DDL like CREATE TABLE. Why you would want to do that? The SELECT INTO will be executed remotely, and create a table in Sybase.

What you could try is to create the IDs table in Sybase. This table can be joined to the view then, and you are getting your rows as expected by a simple SELECT.
Prisoner362670Author Commented:
A Good Afternoon to All,

Although I have been unable to write I have been able to work on my query in both InfoMaker and Access 2003.  I started to rebuild the SQL query in InfoMaker but found ultimately I could not pull what I needed from the view.  So I moved totally into Access which is better for the user I am developing for anyway because they actually need an application and not a report.

Anyway, in Access I found sucess not in trying to 'feed' the view the @id via a query or a vba codeset for recordset.  What I did was create a SQL query pulling four fields from the view (any more fields triggered an 'illegal' from Sybase).  This seems to work reliably and I have decided to continue development in Access with linked Sybase tables.

I was unable to test Olemo suggestion as I do not have rights to create tables in ARIA/Sybase.

I thank you both for your time and suggestions as so split the points between you.
Persistance is Success!
Prisoner362670Author Commented:
see above from congogrey.

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now