locked
VB.NET connectionstring using access 2007 password RRS feed

  • Question

  • hi expert, i'm using VB 2010 express edition (WinForm), Microsoft access 2007 as database with username and password. Username: Admin and Password: studentdb123. My question is how do i construct  a proper connection string and modifying app.config to add username and password because i got this Error "Not a valid password" when form is loaded.

    on form_load at line:

    Me.StudentTableAdapter.Fill(Me.DbstudentDataSet.student)

    Error: Not a valid password

    my connection string:

    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                            "Data Source=" & System.Environment.CurrentDirectory.ToString() & "\Dbstudent.accdb;" & _
                                                            "User IDPassword=studentdb123;")

    also changed and try this:

    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                            "Data Source=" & System.Environment.CurrentDirectory.ToString() & "\Dbstudent.accdb;" & _
                                                            "Uid=Admin;Pwd=studentdb123;")

    and this

    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                            "Data Source=" & System.Environment.CurrentDirectory.ToString() & "\Dbstudent.accdb;" & _
                                                            "Jet OLEDB:Database Password=studentdb123;")

    still got the same error: "Not a valid password"

    then i open the app.config

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="SPDPV11.My.MySettings.DbstudentConnectionString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Dbstudent.accdb"
                providerName="System.Data.OleDb" />
        </connectionStrings>
        <system.diagnostics>
            <sources>
                <!-- This section defines the logging configuration for My.Application.Log -->
                <source name="DefaultSource" switchName="DefaultSwitch">
                    <listeners>
                        <add name="FileLog"/>
                        <!-- Uncomment the below section to write to the Application Event Log -->
                        <!--<add name="EventLog"/>-->
                    </listeners>
                </source>
            </sources>
            <switches>
                <add name="DefaultSwitch" value="Information" />
            </switches>
            <sharedListeners>
                <add name="FileLog"
                     type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
                     initializeData="FileLogWriter"/>
                <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
                <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
            </sharedListeners>
        </system.diagnostics>
    </configuration>

    and i thought there maybe somewhere missing in my app.config (i don't see username and password in my app.config)

    then how do i construct  a proper connection string and modifying app.config to add username and password.

    Thanks in Advance....

    Tuesday, May 3, 2011 1:46 AM

Answers

  • Access 2007/2010 files do not support user-level security, only a database password. Did you add a database password to your database? The below connection string looks OK to me with respect to syntax:

    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & System.Environment.CurrentDirectory.ToString() & "\Dbstudent.accdb;" & _
                                "Jet OLEDB:Database Password=studentdb123;")
    
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Kee Poppy Tuesday, May 10, 2011 3:27 AM
    • Unmarked as answer by cahmad Friday, May 13, 2011 6:13 PM
    • Marked as answer by cahmad Friday, May 13, 2011 6:37 PM
    Tuesday, May 3, 2011 12:54 PM

All replies

  • Access 2007/2010 files do not support user-level security, only a database password. Did you add a database password to your database? The below connection string looks OK to me with respect to syntax:

    Private con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=" & System.Environment.CurrentDirectory.ToString() & "\Dbstudent.accdb;" & _
                                "Jet OLEDB:Database Password=studentdb123;")
    
    
    

    Paul ~~~~ Microsoft MVP (Visual Basic)
    • Marked as answer by Kee Poppy Tuesday, May 10, 2011 3:27 AM
    • Unmarked as answer by cahmad Friday, May 13, 2011 6:13 PM
    • Marked as answer by cahmad Friday, May 13, 2011 6:37 PM
    Tuesday, May 3, 2011 12:54 PM
  • initially, the database did not have password, then i add a password. open Microsoft access 2007 -> (select database)open file Exclusive, then select database tools, -> Encrypt with Password.

    should i delete database in VB 2010 IDE, and add new datasource so that it reread database with its password?

     

    Tuesday, May 3, 2011 3:39 PM
  • If you have to made changes to the data source it's probably easiest to delete it and then recreate it.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Tuesday, May 3, 2011 6:23 PM
  • i got the error message:

    Could not find installable ISAM.

    any helps, advices, tips are very appreciated

     

    Friday, May 13, 2011 6:15 PM
  • working now.. Thanks...
    Friday, May 13, 2011 6:37 PM
  • Please tell me what was the issue, as i am facing the same problem with ASP.NET page and Access 2007 database. i always get a not a valid password error, when i run the page from the web server. i appreciate your help. below is a thread i opend on asp.net forms that explains the problem. thank you.

    http://forums.asp.net/p/1686842/4446016.aspx/1?Re+Access+2007+Not+a+valid+password

     

    Sunday, June 5, 2011 11:33 AM
  • I found some conflict here, when using sqldatasource, better don’t used oledb.data, oledbdataadapter,  instead just use sql.data, sqldataadapter  or something like that. Secondly problem is if you used access database, then better used Oledb, and all its types.

    Yes permission error maybe occurred because you are using keyword “DIM”. Used keyword “Private” or “Public” or “shared”. Try see what happen.
    Sunday, June 5, 2011 4:22 PM
  • Thanks cahmed, but was your problem?
    Monday, June 6, 2011 11:26 AM
  • Thanks for abreviated correct answer
    Friday, September 14, 2012 3:36 AM
  • Hi Paul, 

    I am in urgent need of solution for the issue- "Not a valid password". 
    My client is using Ms Access 2000 and MS Access 2007 databases. I have used the above given solution with both Ms Access versions. Unfortunately it's not working. could you please provide me alternate solution for this.



    I tried to recreate the database in MS Access 2000 and 2007 with the help of solution link- http://support.microsoft.com/kb/838593#appliesto. It didn't work.
    Thursday, May 9, 2013 12:17 PM
  • I'm assuming that you are using a database password and not user level security. If so, I would need to see the connection string.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 9, 2013 12:22 PM
  • I do not know how to explain to you. My code above is ok.. I just Saved, close Visual Studio, restart Windows, then running back Visual Studio, open the project and run it... It works. So... Maybe some old or piece of code still stuck in memory. you need to restart all.
    Thursday, May 9, 2013 12:30 PM
  • Hi Paul,

    Please find my code. My problem is that I am setting password in the connection string as- Jet OLEDB:Database Password=" & pwd & "; . My aim is that as the user will browse and select the access database from OpenfileDialog, that moment he  shud be prompted to input the database password.

        

        Public Function ConnString(ByVal DSOURCE As String,ByVal pwd As String) As String
            Dim str As String = ""
            If InStr(DBPath, ".accdb", CompareMethod.Text) > 0 Then

                str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DSOURCE & ";Jet OLEDB:Database Password=" & pwd & ";"

            ElseIf InStr(DBPath, ".mdb", CompareMethod.Text) > 0 Then

                str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DSOURCE & ";Persist Security Info=False;Jet OLEDB:Database Password=" & pwd & ";"

            End If
    Return (str)
        End Function

    '*****above connection string is being used in a method LoadSchema, and I am receiving error by exception raised here-

       Private Sub LoadSchema()

            Dim SchemaTable As DataTable

            Try
                lstTables.Items.Clear()
                CON = New OleDbConnection(ConnString(DBPath,password))
                CON.Open()

                'Get table and view names, here SchemaTable is datatable
                SchemaTable = CON.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

                Dim int As Integer
                For int = 0 To SchemaTable.Rows.Count - 1
                    If SchemaTable.Rows(int)!TABLE_TYPE.ToString = "TABLE" Then
                        'ADD column names to list box
                        lstTables.Items.Add(SchemaTable.Rows(int)!TABLE_NAME.ToString())
                    End If
                Next

            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString(), "Data Load Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            End Try

            CON.Close()

        End Sub

    ' The code starts executing on SelectedIndexChanged event of combobox1, In this block I am opening an OpenFileDialog to select different databases.

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Dim openFileDialog1 As New OpenFileDialog
            Dim myStream As Stream = Nothing


            openFileDialog1.Title = "Please select a database"
            openFileDialog1.InitialDirectory = "D:\"
            If ComboBox1.SelectedIndex = 0 Then
                openFileDialog1.Filter = " (*.accdb)|*accdb|" & " (*.mdb)|*.mdb"   

                openFileDialog1.FilterIndex = 2
                openFileDialog1.RestoreDirectory = True

                If openFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                    Try
                        DBPath = openFileDialog1.FileName
                        myStream = openFileDialog1.OpenFile()
                        If (myStream IsNot Nothing) Then
                            ' code to read the stream here. 
                            LoadSchema()

                        End If
                    Catch Ex As Exception
                        MessageBox.Show("Cannot read file from disk. Original error: " & Ex.Message)
                    Finally
                        ' Check this IF need to see thrown an exception on open. 
                        If (myStream IsNot Nothing) Then
                            myStream.Close()
                        End If
                    End Try
                End If
            End If

            ComboBox1.Enabled = False

    End Sub




    • Edited by aprajitaj Friday, May 10, 2013 7:32 AM
    Friday, May 10, 2013 3:36 AM
  • As I use the database password in for the attribute - Jet OLEDB:Database Password=mypasswrd; , the application runs smooth. I face problem when I try to make it parametric.

    Please have a look at my code in this post.

    Friday, May 10, 2013 7:41 AM
  • Did you try setting a debug breakpoint in code on the following line:

    CON.Open()

    ...and then checking the value of CON.ConnectionString to make certain that it is correct?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, May 10, 2013 12:22 PM
  • I debugged the code and found that Jet OLEDB:Database Password=" & pwd & "  has no value.

    Since application doesn't give a prompt for entering password of MS Access database when the .mdb/.accdb Database is opened via OpenFileDialog inside the application and instead produces Error "Not a valid password". I don't know the workaround for forcing the MS Access database to give password input box at time when it is selected there...

    Please advice.

    Friday, May 10, 2013 2:52 PM
  • When using Access via OLEDB you will not be prompted for a password. You will need to display a dialog box from your app to request the database password.

    You can do this by creating a Form and the ShowDialog method:

    http://msdn.microsoft.com/en-us/library/c7ykbedk.aspx

    Just make sure to set the PasswordChar property of the TextBox so it doesn't display the password in clear text.


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Friday, May 10, 2013 3:37 PM
  • Thanks for the solution provided.

    As you said using Access via OLEDB you will not be prompted for a password, could the same make possible with ODBC?

    Saturday, May 11, 2013 1:14 PM