Need to display results of Stored Proceedure with Criteria

[Guest]

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.....

 

[Sylvain Lafontaine]

Do you want to display a form or a report?

 

[Guest]

I want to just open it in datasheet 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.

Do you want to display a form or a report?

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

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.....

 

[Sylvain Lafontaine]

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

I want to just open it in datasheet 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.

Do you want to display a form or a report?

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

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.....

 

[Guest]

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 datasheet view. I can have it run the
SP with the code below, but it doesn't open the datasheet 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.

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

I want to just open it in datasheet 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.

Do you want to display a form or a report?

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


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.....

 

[Sylvain Lafontaine]

Instead of opening a datasheet window, the best way is simply to create a
form and set its RecordSource to what you want. See my previous answers to
Ernie for a full explanation.

By setting the display of the form to datasheet, you will have exactly the
result that you want.

If the RecordSource of the form is different from null, the SP is queried a
first time before the OnOpen event of the form and a second time when you
change it; however, this is easy to deal with.

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

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 datasheet view. I can have it run
the
SP with the code below, but it doesn't open the datasheet 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.

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

I want to just open it in datasheet 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.

:

Do you want to display a form or a report?

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


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.....

 

[Guest]

I looked up what you had sent to Ernie and I will try to figure it out later.
I am off on vacation for a week, so the boss will just have to wait.

Thanks!

Instead of opening a datasheet window, the best way is simply to create a
form and set its RecordSource to what you want. See my previous answers to
Ernie for a full explanation.

By setting the display of the form to datasheet, you will have exactly the
result that you want.

If the RecordSource of the form is different from null, the SP is queried a
first time before the OnOpen event of the form and a second time when you
change it; however, this is easy to deal with.

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

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 datasheet view. I can have it run
the
SP with the code below, but it doesn't open the datasheet 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.

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


I want to just open it in datasheet 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.

:

Do you want to display a form or a report?

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


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.....