Why is one Access query a View and the other a Stored Procedure?

  • Thread starter Thread starter Rob Richardson
  • Start date Start date
R

Rob Richardson

Greetings!

I am rewriting a VB6 application in VB .Net. The database (which was
converted from Access 97 into Access 2000) has two nearly identical queries.
One, called GetNewOrderNumber, is:

SELECT IIF ((SELECT COUNT(*) FROM Orders), Max(Order_number) + 1, 1) AS
NewOrderNumber
FROM Orders;

The other, called GetNewHoldingNumber, is:

SELECT IIF ((SELECT COUNT(*) FROM Holdings), Max(Holding_number) + 1, 1) AS
NewHoldingNumber
FROM Holdings;

When I look at the database using the VB .Net's Server Explorer, I see that
GetNewHoldingNumber is listed under the Views and GetNewOrderNumber is
listed under the Stored Procedures. Why is that? Is this going to affect
how I use these queries in my application at all?

Thanks very much!

Rob

P.S. This was originally posted on the m.p.d.f.adonet newsgroup, but it
didn't get an answer, and I think this is more VB .Net than ADO.Net anyway.
 
Rob Richardson said:
Greetings!

I am rewriting a VB6 application in VB .Net. The database (which was
converted from Access 97 into Access 2000) has two nearly identical queries.
One, called GetNewOrderNumber, is:

SELECT IIF ((SELECT COUNT(*) FROM Orders), Max(Order_number) + 1, 1) AS
NewOrderNumber
FROM Orders;

The other, called GetNewHoldingNumber, is:

SELECT IIF ((SELECT COUNT(*) FROM Holdings), Max(Holding_number) + 1, 1) AS
NewHoldingNumber
FROM Holdings;

When I look at the database using the VB .Net's Server Explorer, I see that
GetNewHoldingNumber is listed under the Views and GetNewOrderNumber is
listed under the Stored Procedures. Why is that? Is this going to affect
how I use these queries in my application at all?

Thanks very much!

Rob

P.S. This was originally posted on the m.p.d.f.adonet newsgroup, but it
didn't get an answer, and I think this is more VB .Net than ADO.Net anyway.
Click to expand...

Generally, an Access stored query is displayed as a view if it has no parameters, and as a stored procedure if it does. You
probably already know this, however. Is it possible that Holding_number is not a field in the database (i.e. the column name has
been renamed)? Otherwise, it seems that both should be listed as views.
 
Hi Rob,

I saw this also in the adonet group.

But this question is more for an access group I think.

Someone has put that procedure in the access database on 2 places, why he
did I think you have to ask the person who did it.

But maybe you get your answer here.

Another better newsgroup approach is to divide your question in two.

It are two questions and maybe there is someone who knows one, often than
you get no answer.

I hope this helps a very little bit?

Cor
 
Cor,

I doubt very much that it is even possible to create two queries with the
same name. I checked the database to make sure, and it is not there. Also,
I am the one who created the database.

This question only has one part.

This question is about how VB.Net handles an Access database, not about the
Access database itself. Therefore, a VB.Net newsgroup is more appropriate.

Rob
 
Hi Rob,

I did a quick test by creating a couple tables, Orders and Holdings with columns that match your queries. I then duplicated your queries in access and open the database in VS. Net Server Explorer.
Both the Queries show up under Queries. So I have a couple questions that may help to narrow down what may be causing the issue.

What version of VS .Net are you using?
What version of the Jet OLEDB Provide are you using?
Does it matter if you create the database in Access 2000 verses coverting it from Access 97?

If you can provide some answers to these questions I can try to duplicate here.

Cameron Slade
Visual Studio Database Tools
(e-mail address removed)
--------------------
 
Back
Top