setting permissions on ADP project

[belovedcej]

I have an adp project that currently uses bound forms(though that will change in the next phase.)

Our other programmers here use VB.NET and don't know how to help me. I'm also new to SQL Server.

One of the programmers set up a role in SQL to give the correct permissions for my database. He said that I need to set up a connection in VBA that will access that role so that when a user signs on with their windows NT password, the database will also know they can access the database.

We want to set permissions this way instead of letting them just read and write to the tables because we wouldn't want them to access those tables except through the forms I designed.

Here is the VBA I used. I put it on a form that opens as soon as the DB opens, but then that form closes to allow the main screen to open. I have no form that stays open all the time.

It didn't work. My user was unable to access the information. Can anyone help me?

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
                          "Persist Security Info=False;" & _
                          "Initial Catalog=SCO_DB;" & _
                          "Data Source=INTREPID"

cn.Execute "EXEC sp_setapprole 'Mediation_app', {Encrypt N 'mediation'}, 'odbc'"

DoCmd.Close acForm, "Open MCMS"