=> Permissions for Stored Proceedures

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

I have created a Microsoft Access Project which makes
use of the stored procedures on SQL Server. This works
on my computer however the Users for which I am
attempting to release my software have a blank page
appear where the form is based on an SQL stored proceedure?

I have attempted to change the permissions for these
stored procedures, within the appropriate group. It still
doesn't work. What else could I do?

Any suggestions would be terrific.

Thank you
Rhonda
 
are the name of the server, Database, and table the same in both
configuration? (Home & Client site). How do you access your sp with ado?
what's you connection string?
 
When I created my MS Access Project I chose:
-> Access database wizards, pages, and projects
-> Tab: General -> Project (Existing Database)

at which point all the tables and stored proceedures
that existing in my SQL Server database called ZSQUIRREL
appeared in my MS Access Project. So the tables and
queries are stored on SQL Server but referenced in the
MS Access Project.

So to answer your first question; yes the name of the
server, database and table are the same in both.

I access the stored procedures in a couple of ways,
which both work on my machine, which has SQL Server
loaded. I access my stored proceedure as a
recordsource on a form.

I'm not sure where I would set the properties for a
connection string. Should it be somewhere in the
query itself.

Current example of a stored Proceedure:
=======================================

Alter Procedure spTodaysInLoadsUntipped
As
SELECT * FROM [Turners Movements]
WHERE CONVERT(VARCHAR,[Date],103) = CONVERT
(VARCHAR, getDate(),103)
AND [In/Out] = 18 AND Trunkin = -1 AND TipTime
Is Null
return
========================================

The only place I make use of a
connection string is when I am coding directly i.e.

I'm not sure how I would make us of a connection
string for a stored proceedure referenced as a
recordsource for a form?

=========================================

Function checkNoDuplicateTrailerHistoryEntry(myForm) As
Boolean
On Error GoTo Err_checkNoDuplicateTrailerHistoryEntry
'Form: frmMgmtSupplierNew
'Button: Save

'Declaration
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset

'Open the connection
cnn.Open "Provider=sqloledb; Data
Source=ZSQUIRREL;Initial Catalog=TurnersMgmtSystem;
Integrated Security=SSPI"

'Set up the Command objects's Connection, SQL and
parameter types
With cmd
.ActiveConnection = cnn
.CommandText = "SELECT * " & _
"FROM TrailerHistory WHERE
FleetNumber = ? " & _
"AND DateOnly = ? AND Status = ? "

.CreateParameter , adVarChar, adParamInput,
30 'Trailer Number
.CreateParameter , adDate,
adParamInput 'DateOnly
.CreateParameter , adInteger,
adParamInput 'Status
End With

cmd.Parameters(0) = Forms(myForm)!txtTrailerNo
cmd.Parameters(1) = Format(Forms(myForm)!
txtTrailerReloadDateTime, "dd/mm/yyyy")
cmd.Parameters(2) = Forms(myForm)!cboStatus


Set rst = cmd.Execute

'An empty recordset means the Trailer Movement is not
contained in the TrailerHistory table
'and may be added without risk of duplication
checkNoDuplicateTrailerHistoryEntry = rst.EOF

cnn.Close
Set cnn = Nothing
Set cmd = Nothing

Exit_checkNoDuplicateTrailerHistoryEntry:
Exit Function

Err_checkNoDuplicateTrailerHistoryEntry:
MsgBox Err.Description
Resume Exit_checkNoDuplicateTrailerHistoryEntry

End Function
 
Back
Top