locked
set access database password from VB

    Question

  •  

    Hi,

     

    I want to be able to change the username and password of an access database from a Visual Basic.Net application. I also want to be able to add new users. How would I do this?

     

    Thanks In Advance for any Help

     

    Will

    Sunday, August 12, 2007 8:47 PM

Answers

  • Hi Will,

     

    The following code can be used to change Access database password in VB.NET by executing SQL statement "ALTER DATABASE PASSWORD [newPassword][OldPassword]".

    Code Snippet
    Dim cn As OleDbConnection = New OleDbConnection
    cn.ConnectionString 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\VS.Net\nwind.mdb;Jet OLEDB:Database Password=OldPassw
    ord;Mode=Share Deny Read|Share Deny Write;"
    cn.Open
    Dim cmd As OleDbCommand = New 
    OleDbCommand
    cmd.Connection 
    cn
    cmd.CommandText 
    "ALTER DATABASE PASSWORD [newPassword][OldPassword]" 'change password
    cmd.ExecuteNonQuery
    cn.Close

    Note: If password is empty, please don't use the square bracket "[]" but only leave Null instead.

     

    In addition, if you want to modify or add one record including username and password fields to access database, here is the code sample. (which may be not what you want)

    Add an user record
    Imports System.Data.OleDb

        
    Public Function AddUser(ByVal username As StringByVal password As StringByVal confirmPassword As StringAs Boolean
            If 
    (password <> confirmPassword) Then
                
    MessageBox.Show("The Password don't match,  please try again! ")
                
    Return false
            End If

            Try 
                Dim 
    constr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" + ("Data Source=D:\\User.mdb;" "Persist Security Info=False"))
                
    Dim myconn As OleDbConnection = New OleDbConnection(constr)
                myconn.Open()
                
    Dim myCommand As OleDbCommand = New OleDbCommand
                myCommand.Connection 
    myconn
            myCommand.CommandText 
    ("select * from usertable where UserName='"+ username + "'")
                
    Dim reader As OleDbDataReader myCommand.ExecuteReader
                
    If reader.HasRows Then
                    
    MessageBox.Show(("The user'"+ (username + "'  already exists,please try again!")))
                    
    Return false
                End If
                
    reader.Close
     
               'Add a user record
                myCommand.CommandText 
    "Insert into usertable (UserName,Password) Values ('"+
    username + "','" + password + "')"
     
              ' Modify a user record
              ' myCommand.CommandText "update usertable set Password='"+ password + "'  where UserName='"+ username + "'"

                MessageBox.Show(myCommand.CommandText)
                Dim row As Integer = myCommand.ExecuteNonQuery
                
    If (row >1Then
                    Return true
                End If

            Catch 
    ex As Exception
                
    MessageBox.Show(ex.Message)
            
    Finally
                
    myconn.Close()
            
    End Try
            Return false
        End Function

     

     

    By the way, the following KB article may be helpful to you.

    http://support.microsoft.com/kb/311058

    INFO: Microsoft .NET Framework Does Not Support Data Access Object

    DAO usually works on Access database only, but it's not supported to be used with .Net.

    Microsoft recommends that you use Microsoft ActiveX Data Objects (ADO) or Microsoft ADO.NET with Microsoft Jet OLE DB Provider to connect to an Access database from a Microsoft Visual Studio .NET application.

     

    I hope that can help you.

     

    Best regards,

    Martin Xie

    Tuesday, August 14, 2007 2:52 PM

All replies

  • Hi Will,

     

    The following code can be used to change Access database password in VB.NET by executing SQL statement "ALTER DATABASE PASSWORD [newPassword][OldPassword]".

    Code Snippet
    Dim cn As OleDbConnection = New OleDbConnection
    cn.ConnectionString 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\VS.Net\nwind.mdb;Jet OLEDB:Database Password=OldPassw
    ord;Mode=Share Deny Read|Share Deny Write;"
    cn.Open
    Dim cmd As OleDbCommand = New 
    OleDbCommand
    cmd.Connection 
    cn
    cmd.CommandText 
    "ALTER DATABASE PASSWORD [newPassword][OldPassword]" 'change password
    cmd.ExecuteNonQuery
    cn.Close

    Note: If password is empty, please don't use the square bracket "[]" but only leave Null instead.

     

    In addition, if you want to modify or add one record including username and password fields to access database, here is the code sample. (which may be not what you want)

    Add an user record
    Imports System.Data.OleDb

        
    Public Function AddUser(ByVal username As StringByVal password As StringByVal confirmPassword As StringAs Boolean
            If 
    (password <> confirmPassword) Then
                
    MessageBox.Show("The Password don't match,  please try again! ")
                
    Return false
            End If

            Try 
                Dim 
    constr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" + ("Data Source=D:\\User.mdb;" "Persist Security Info=False"))
                
    Dim myconn As OleDbConnection = New OleDbConnection(constr)
                myconn.Open()
                
    Dim myCommand As OleDbCommand = New OleDbCommand
                myCommand.Connection 
    myconn
            myCommand.CommandText 
    ("select * from usertable where UserName='"+ username + "'")
                
    Dim reader As OleDbDataReader myCommand.ExecuteReader
                
    If reader.HasRows Then
                    
    MessageBox.Show(("The user'"+ (username + "'  already exists,please try again!")))
                    
    Return false
                End If
                
    reader.Close
     
               'Add a user record
                myCommand.CommandText 
    "Insert into usertable (UserName,Password) Values ('"+
    username + "','" + password + "')"
     
              ' Modify a user record
              ' myCommand.CommandText "update usertable set Password='"+ password + "'  where UserName='"+ username + "'"

                MessageBox.Show(myCommand.CommandText)
                Dim row As Integer = myCommand.ExecuteNonQuery
                
    If (row >1Then
                    Return true
                End If

            Catch 
    ex As Exception
                
    MessageBox.Show(ex.Message)
            
    Finally
                
    myconn.Close()
            
    End Try
            Return false
        End Function

     

     

    By the way, the following KB article may be helpful to you.

    http://support.microsoft.com/kb/311058

    INFO: Microsoft .NET Framework Does Not Support Data Access Object

    DAO usually works on Access database only, but it's not supported to be used with .Net.

    Microsoft recommends that you use Microsoft ActiveX Data Objects (ADO) or Microsoft ADO.NET with Microsoft Jet OLE DB Provider to connect to an Access database from a Microsoft Visual Studio .NET application.

     

    I hope that can help you.

     

    Best regards,

    Martin Xie

    Tuesday, August 14, 2007 2:52 PM
  •  Martin Xie - MSFT wrote:

    Hi Will,

     

    The following code can be used to change Access database password in VB.NET by executing SQL statement "ALTER DATABASE PASSWORD [newPassword][OldPassword]".

    Code Snippet
    Dim cn As OleDbConnection = New OleDbConnection
    cn.ConnectionString 
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\VS.Net\nwind.mdb;Jet OLEDB:Database Password=OldPassw
    ord;Mode=Share Deny Read|Share Deny Write;"
    cn.Open
    Dim cmd As OleDbCommand = New 
    OleDbCommand
    cmd.Connection 
    cn
    cmd.CommandText 
    "ALTER DATABASE PASSWORD [newPassword][OldPassword]" 'change password
    cmd.ExecuteNonQuery
    cn.Close

    Note: If password is empty, please don't use the square bracket "[]" but only leave Null instead.

     

    In addition, if you want to modify or add one record including username and password fields to access database, here is the code sample. (which may be not what you want)

    Add an user record
    Imports System.Data.OleDb

        
    Public Function AddUser(ByVal username As StringByVal password As StringByVal confirmPassword As StringAs Boolean
            If 
    (password <> confirmPassword) Then
                
    MessageBox.Show("The Password don't match,  please try again! ")
                
    Return false
            End If

            Try 
                Dim 
    constr As String = ("Provider=Microsoft.Jet.OLEDB.4.0;" + ("Data Source=D:\\User.mdb;" "Persist Security Info=False"))
                
    Dim myconn As OleDbConnection = New OleDbConnection(constr)
                myconn.Open()
                
    Dim myCommand As OleDbCommand = New OleDbCommand
                myCommand.Connection 
    myconn
            myCommand.CommandText 
    ("select * from usertable where UserName='"+ username + "'")
                
    Dim reader As OleDbDataReader myCommand.ExecuteReader
                
    If reader.HasRows Then
                    
    MessageBox.Show(("The user'"+ (username + "'  already exists,please try again!")))
                    
    Return false
                End If
                
    reader.Close
     
               'Add a user record
                myCommand.CommandText 
    "Insert into usertable (UserName,Password) Values ('"+
    username + "','" + password + "')"
     
              ' Modify a user record
              ' myCommand.CommandText "update usertable set Password='"+ password + "'  where UserName='"+ username + "'"

                MessageBox.Show(myCommand.CommandText)
                Dim row As Integer = myCommand.ExecuteNonQuery
                
    If (row >1Then
                    Return true
                End If

            Catch 
    ex As Exception
                
    MessageBox.Show(ex.Message)
            
    Finally
                
    myconn.Close()
            
    End Try
            Return false
        End Function

     

     

    By the way, the following KB article may be helpful to you.

    http://support.microsoft.com/kb/311058

    INFO: Microsoft .NET Framework Does Not Support Data Access Object

    DAO usually works on Access database only, but it's not supported to be used with .Net.

    Microsoft recommends that you use Microsoft ActiveX Data Objects (ADO) or Microsoft ADO.NET with Microsoft Jet OLE DB Provider to connect to an Access database from a Microsoft Visual Studio .NET application.

    I hope that can help you.

    Best regards,

    Martin Xie

    Hi Martin,

    I need some help!

    I also want to be able to add a new user and password. Also to change user and password.

    I do have the access database.

    I did create a form with two button, two textbox and one checkbox, but don't how apply your code

     

    I did try as your code with changes that I did to the Access Database and this error from VB:

    cmd.ExcecuteNonQuery - Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'

    a Visual Basic.Net application.

     

    regards

     

    newVB
    Saturday, March 29, 2008 5:23 AM
  •  

    The original question dealt with how to change the database's password.

     

    That's a different question than how do "I add a user". In your database do you have a table called usertable?

     

    Use the forum search and look for the word login.

    Saturday, March 29, 2008 12:31 PM
  • Glad to see you again, dear Renee!

     

    Hi newVB,

     

    Here are some code samples about login routine:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2949175&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2676391&SiteID=1

     

    If you don't mind telling me your email, I would like to send you one complete sample project (VB.NET) for you to check.

     

    Regards,

    Martin Xie

    v-maxie@microsoft.com

    Monday, March 31, 2008 4:00 AM
  •  

    Hello Martin, Smile

     

    It's wonderful to see you again. We've missed you.

    Monday, March 31, 2008 4:31 AM
  • Hi Martin,
    Pls send me the sample project at rajeev.acpl@aol.in

    regards
    rajeev
    Monday, March 31, 2008 7:13 PM
  • Thank you Renee for your greeting. I miss you too.

     

     rajeev_learning_vb2005 wrote:
    Hi Martin,
    Pls send me the sample project at rajeev.acpl@aol.in

    Hi rajeev,

    Thank you for contacting me. The VB.NET Sample Project: "Login Verification and Add/Remove Users" has neen sent to you.

    Tuesday, April 01, 2008 2:39 AM
  • Martin Xie, how did u get host address from?

    Sunday, November 29, 2009 5:31 PM