Controlling Access MDW through VBA 1

[afryer]

Hi All,

I am working on a Microsoft Access 2003 application that connects to a user defined Workgroup file. Are there any API calls I can use to perform password changes on this file whilst the application is running?

Thanks


Andrew

 

[PHV]

Have a look at the NewPassword method of the DAO.User object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JerryKlmns]

Or this

CurrentProject.Connection.Execute "Alter User " & txtUser & " Password " & txtOldPswrd & " " & txtNewPswrd

 

[Ed2020]

Have a look at the ChangePass sub in this set of routines. I have posted the rest as they may come in handy too...



Option Compare Database
Option Explicit

'Contains procedures to manage MS Access security via VB.
'Uses Jet 4 ANSI-92 extensions (for simplicity) accessible via SQL so will only work in
'MS Access 2000 or later.
'Written by Ed Metcalfe, 28/11/2002.


Public Sub CreateUser(ByVal strUserName As String, ByVal strPWord As String, ByVal lngPID As Long)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "CREATE USER " & strUserName & " " & strPWord & " " & lngPID & ";"

'All users must be members of users group
Call AddToGroup(strUserName, "Users")

cnn.Close
Set cnn = Nothing

End Sub

Public Sub CreateGroup(ByVal strGroupName As String, ByVal lngGID As Long)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "CREATE GROUP " & strGroupName & " " & lngGID & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub ChangePass(ByVal strUserName As String, strNewPass As String, strOldPass As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "ALTER USER " & strUserName & " PASSWORD " & strNewPass & " " & strOldPass & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub AddToGroup(ByVal strUserName As String, ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "ADD USER " & strUserName & " TO " & strGroupName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub RemoveFromGroup(ByVal strUserName As String, ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP USER " & strUserName & " FROM " & strGroupName

cnn.Close
Set cnn = Nothing

End Sub

Public Sub DeleteUser(ByVal strUserName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP USER " & strUserName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub DeleteGroup(ByVal strGroupName As String)

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

cnn.Execute "DROP GROUP " & strGroupName & ";"

cnn.Close
Set cnn = Nothing

End Sub

Public Sub AssignPermission(ByVal strAction As String, ByVal strPermissionName As String, ByVal strObjectName As String, ByVal strObjectType As String, ByVal strAccountName As String)
'strAction - GRANT Or REVOKE
'strPermissionName - SELECT, DELETE, INSERT, UPDATE, DROP, SELECTSECURITY, UPDATESECURITY,
'DBPASSWORD, UPDATEIDENTITY, CREATE, SELECTSCHEMA, SCHEMA, UPDATEOWNER
'strObject Name - Name of table/form/report etc.
'strObjectType - "TABLE", "OBJECT" OR "CONTAINER"
'strAccountName - name of user or group
'How do you give design privs then??

Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection

Select Case strAction
Case "GRANT"
cnn.Execute "GRANT " & strPermissionName & " ON " & strObjectType & " " & strObjectName & " TO " & strAccountName & ";"
Case "REVOKE"
cnn.Execute "REVOKE " & strPermissionName & " ON " & strObjectType & " " & strObjectName & " FROM " & strAccountName & ";"
End Select

cnn.Close
Set cnn = Nothing

End Sub

Please do not feed the trolls.....

 

[afryer]

cheers - thats a great help!

Andrew