ASP.Net VB Access DataTable fill changes connection string

[dellis]

Problem:
My project was initially created without putting passwords in the connection string. When I added the password in the connection string, it disappears when performing a database fill. I have stepped through the code in debug and the connection string is correct up til the Fill command. Once the fill executes, the password disappears from the connection string. This particular Fill is performed to check that the user is valid for this database and are able to proceed further in the app.

I see no reason that the password should drop out of the connection string. Does anyone have any suggestions or reasons why this is happening? Any way of making it NOT happen? I know Access is crap. I have assurances that the company will convert to SQL Server and it can't happen soon enough to suit me.

Before executing the fill, the connection string inside dbConnection is
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\database.mdb;User ID=user;Password=password;Jet OLEDB:System database=C:\DB\Security.mdw"
After, it is
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DB\database.mdb;User ID=user;Jet OLEDB:System database=C:\DB\Security.mdw"

If I put dbConnection in a Session variable before executing the Fill, the value in the Session is changed after the Fill as well.

Relevant Code:

Public Class Constants
    Public Class ConfigurationKeys
        Public Const CONNECTION_STRING As String = "ConnectionString"
...
' Data Source and System Database are derived from which market a user chooses
' User ID and Password are entered by the user
<connectionStrings>
    <add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User ID={1};Password={3};Jet OLEDB:System database={2}" providerName="System.Data.OleDb"/>
</connectionStrings>
...
    Public Class SelectCommands
        Public Const TEST_DB_ACCESS As String = "SELECT TOP 1 Table_ID FROM tblTable"
...
    Dim m_dsMKTs As New DataSet()
...
 
        Dim currentMKT As New MKTInfo
        Dim iIndex As Integer = ddlMKT.SelectedIndex
        Dim strUserId As String = tbUserID.Text
        Dim strPassword As String = tbPassword.Text
 
        ' If a MKT has been selected, populate the MKTInfo class
        If iIndex > 0 Then
            Dim dtJDE_ID As New DataTable
 
            currentMKT.Name = m_dsMKTs.Tables(0).Rows(iIndex - 1)("Name")
            currentMKT.DatabasePath = m_dsMKTs.Tables(0).Rows(iIndex - 1)("DatabasePath")
            currentMKT.SecurityFile = m_dsMKTs.Tables(0).Rows(iIndex - 1)("SecurityFile")
 
            ' Build the access database connection string based on the MKT specified and current user
            Dim strConnectionFormat As String = ConfigurationManager.ConnectionStrings(Constants.ConfigurationKeys.CONNECTION_STRING).ConnectionString
            Dim strConnectionString As String = String.Format(strConnectionFormat, currentMKT.DatabasePath, strUserId, currentMKT.SecurityFile, strPassword)
            Dim dbConnection As OleDbConnection = New OleDbConnection(strConnectionString)
            Dim strSelectCommand As String = Constants.SelectCommands.TEST_DB_ACCESS
            Dim daJDE_ID As New OleDbDataAdapter(strSelectCommand, dbConnection)
 
            Try
                daJDE_ID.Fill(dtJDE_ID) ' this is where I have problems.
 
                'Workaround - have to re-establish the connection string because the previous line removes the password for some reason
                dbConnection.Close()
                dbConnection = New OleDbConnection(strConnectionString)

[StevieTimes]

add "Persist Security Info=True" to your connection string.