ADO help needed

[gsnidow via AccessMonster.com]

Greetings folks. I am using ADP to connect to SQL Server 2000. I am trying
to use the servers PERMISSIONS() function to restrict who can open what form
rather than have them open the form and get a server error message about you
do not have permission. I need somehow to capture the recordset value. In
this case it will be 32. Any ideas?

Private Sub cmdGrowth_Click()
On Error GoTo err_cmdGrowth_Click

Dim cn As ADODB.Connection
Dim rsPermission As ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 32 "

Set rsPermission = New ADODB.Recordset
Set cn = Application.CurrentProject.Connection
rsPermission.Open strSQL, cn, adOpenForwardOnly, adLockOptimistic


If rsPermission.ActiveConnection = 32 Then
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGrowthTrackingPopup"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
MsgBox "You do not have permission", vbOKOnly
End If

exit_cmdGrowth_Click:
Exit Sub

err_cmdGrowth_Click:
MsgBox Err.Description
Resume exit_cmdGrowth_Click
End Sub

 

[Sylvain Lafontaine]

Give a name to your column and use it when referencing the recordset:

strSQL = "SELECT PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 32 as
CreateDefault"

if (rsPermission("CreateDefault") = 32) then

 

[gsnidow via AccessMonster.com]

Thank you so much Sylvain. Your suggestion is the last piece I needed to
avoid server permission messages. It worked flawlessly. One more question.
Say I have a recordset with multiple columns, can I alias them all and
display them? And if so what kind of object would I use to dispay them? And
could you maybe post a good link or two describing how to work with ADO, as
it is kind of new to me but seems to be a very powerful tool. Thank you
again.

Give a name to your column and use it when referencing the recordset:

strSQL = "SELECT PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 32 as
CreateDefault"

if (rsPermission("CreateDefault") = 32) then

Greetings folks. I am using ADP to connect to SQL Server 2000. I am
trying

[quoted text clipped - 35 lines]

Resume exit_cmdGrowth_Click
End Sub

 

[Sylvain Lafontaine]

You can give a different alias to each column and display them individually:

strSQL = "SELECT PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 32 as
CreateDefault,
PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 64 BackupDatabase"

Msgbox (rsPermission("CreateDefault"))
Msgbox (rsPermission("BackupDatabase"))

Notice in the second example that the use of thee word « as » before the
name of the alias is optional. For the recordset, you can also use the
numerical index of each item but this is prone to error:

Msgbox (rsPermission(0))
Msgbox (rsPermission(1))

The best place to learn ADO is m.p.data.ado. Also, the newsgroup for ADP
m.p.access.adp.sqlserver.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thank you so much Sylvain. Your suggestion is the last piece I needed to
avoid server permission messages. It worked flawlessly. One more
question.
Say I have a recordset with multiple columns, can I alias them all and
display them? And if so what kind of object would I use to dispay them?
And
could you maybe post a good link or two describing how to work with ADO,
as
it is kind of new to me but seems to be a very powerful tool. Thank you
again.

Give a name to your column and use it when referencing the recordset:

strSQL = "SELECT PERMISSIONS(OBJECT_ID('spGROWTH_TRACKING')) & 32 as
CreateDefault"

if (rsPermission("CreateDefault") = 32) then

Greetings folks. I am using ADP to connect to SQL Server 2000. I am
trying

[quoted text clipped - 35 lines]

Resume exit_cmdGrowth_Click
End Sub