how to get data using Application Role from VB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to get data using Application Role from VB

Author

Topic

michaelxvo
Starting Member

47 Posts

Posted - 2006-03-31 : 00:27:39

I am sorry that this is a little off SQL Server topics.
I understand Application Role from SQL Server is more secured than Standard Role.
But from Visual Basic, How do I connect to SQL Server using Application Role. Please give me some VB codes in order for SQL Server to recognize I am refering to Application Role

Thank you

michaelxvo
Starting Member

47 Posts

Posted - 2006-03-31 : 00:34:46

I found the answer from here:
http://support.microsoft.com/?kbid=308312
On Error GoTo EH
'This avoids a message that no records were returned.
DoCmd.SetWarnings False
Dim TSQL
TSQL = "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'Password'}, 'odbc'"
'This sets the app role on Connection #2.
Application.CurrentProject.Connection.Execute TSQL
'This sets the app role on Connection #3.
lst_approle.RowSource = TSQL
lst_approle.Requery
DoCmd.SetWarnings True
MsgBox "The application Role is now in effect.", vbInformation
Exit Sub
EH:
MsgBox Err.Number & ": " & Err.Description, vbCritical

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources