Re: Need to display results of Stored Proceedure with Criteria

Thanks for getting me this far....

I was able to now get the SP to have dbo as the owner, surprises me though
that if you make views or tables through access, they are made by dbo, but
inline functions and stored processures are by domainname\username. If you
didn't look at it in SQL system manager you would never know this as it
doesn't show the create proceedure part in the sql part of the query builder
in Access.

Now I have a new problem though. I am trying to figure out how to send the
criteria to the SP and have it open in data*** view. I can have it run the
SP with the code below, but it doesn't open the data*** window. Any hints
on the rest of this would be greatly appreciated.

Dim Cnxn As ADODB.Connection
Dim strCnxn As String
Dim cmdSQL1 As ADODB.Command

Set Cnxn = New ADODB.Connection
strCnxn = "Provider='sqloledb';Data Source='ServerName';" & _
"Initial Catalog='DBName';Integrated Security='SSPI';"
Cnxn.Open strCnxn
Set cmdSQL1 = New ADODB.Command
Set cmdSQL1.ActiveConnection = Cnxn
cmdSQL1.CommandText = "StoredProceedure1"
cmdSQL1.CommandType = adCmdStoredProc
cmdSQL1.Execute , 9 --This is the month number I want to send

I then have the clean up code after this, but it is just beyond me how to
get it to work.


"Sylvain Lafontaine" wrote:

After taking a look at your first post, the most likely explanation is that
you have used [domainname\myname] instead of [dbo] as the owner of your
stored procedure. Instead, you should try something like this:

CREATE PROCEDURE dbo.StoredProcedure1(@Pick_Month nvarchar(50))
AS SELECT Employee, WorkDate, Customer, WorkHours
FROM dbo.tblWorkDetail
WHERE (DATEPART(Month, WorkDate) = @Pick_Month)
GO

After creating this stored procedure, don't forget to refresh the query list
of ADP by using the View|Refresh command when the query list is displayed or
by closing/reopening the ADP project.

Using anything else than dbo can be tricky with ADP; especially when this
will be another user than you who will use the SP that you will create under
your account.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Groucho" <Groucho@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EEBB4242-66BA-4EB7-A482-B98389F2D0D6@xxxxxxxxxxxxxxxx
I want to just open it in data*** view. My boss wants the data, but
wants
to just pick what month to see. He will copy it to excell, but doesn't
want
to have it as an export.

Thanks in advance for any help that you can be.

"Sylvain Lafontaine" wrote:

Do you want to display a form or a report?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


"Groucho" <Groucho@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8679A632-3B94-4644-930C-90577FC45982@xxxxxxxxxxxxxxxx
I am open to using either a stored proceedure or an inline function to
be
able to do this. I have looked trough all 12 pages in this section and
it
starts to talk about what I need and then it goes off on a tangent to
where I
get lost.

My query that I need to run is much more involved, but the one below
will
still be able to show what I want to do and an answer on how it should
be
done would be easily moved to the more complex query.

SELECT Employee, WorkDate, Customer, WorkHours
FROM dbo.tblWorkDetail
WHERE (DATEPART(Month, WorkDate) = @Pick_Month)

I want to either have a field on a form that will set the criteria or
just
have an input box open that would ask for the month.

My problem lies in that I can create the function or SP but when other
users
try and use it, the systems says that the object cannot be found. I
gave
all
uses full permission on the SQL object so that isn't it, but when I
look
at
the SP in SQL it looks like this:

CREATE PROCEDURE [domainname\myname].StoredProcedure1(@Pick_Month
nvarchar(50))
AS SELECT Employee, WorkDate, Customer, WorkHours
FROM dbo.tblWorkDetail
WHERE (DATEPART(Month, WorkDate) = @Pick_Month)
GO

and I think it is because of the domainname\myname that I am having the
issue.

Is there a way to open this like a query window that all can use?

I have tried with connection objects, I have tried making tables that
do
not
show up until you refresh the table list for some reason, etc. I am at
wits
end for something that was so simple with an mdb.....






.