none
Global connection object RRS feed

  • Question

  • I have an application that uses an Access db.
    On loading the application has to open a number of form that have datagridviews, combo boxes etc

    Currently each form, for instance, opens a connection to the database, fills the grid, closes the connection, opens the connection, fills the combo box, closes the connection etc.

    I have discovered that opening the connection is taking up to 1.5 sec and over a number of forms this adds up to around 11 secs.

    So I decided to look at the performance improvement of using a global connection object. 
    So the main form that opens when the application starts in turn opens the other forms that in turn load their data using the global connection object and then closes the connection. This works very well and is much faster.
    The problem is if I try to do another operation on the database I get a 'File in use' exception and sure enough if I try to open the database in explorer I get the same error. I guessed that some other rogue connection must be being made to the database but for the life of me I can't find it. If I comment out the last form to load the error goes away but if I uncomment that and comment out the next to last form (That doesn't load any data), the error similarly goes away? 

    I also check the connections state after everything has loaded and con.close is called. It is connectionstate closed.

    Is there any way I can view connections to the database to help me track what is happening?
    Or is there something I need to know about using a global connection object?

    Sorry to be so long winded

    Friday, February 23, 2018 8:45 AM

All replies

  • Is there any way I can view connections to the database to help me track what is happening?

    https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer

    <copied>

    Ever wondered which program has a particular file or directory open? Now you can find out. Process Explorer shows you information about which handles and DLLs processes have opened or loaded.

    <end>

    Friday, February 23, 2018 9:05 AM
  • Hello,

    maybey this could help.

    put this code in a seperate module and get the connection through the function getConnection

    Private cn As ADODB.Connection


    Public Function getConnection(Optional strUser As String = DB2_USER, Optional strPassWord As String = DB2_PASSWORD) As ADODB.Connection

    On Error GoTo err_getCn


        
        If cn Is Nothing Then
            Set cn = New ADODB.Connection
        End If
        
        If cn.State = adStateClosed Then
            Call cn.Open(DB2_DSN, strUser, strPassWord)
        End If
        
        Set getConnection = cn
    Exit Function
    err_getCn:
         'your message
        Set cn = Nothing
    End Function

    hope this helps


    • Edited by Wouter Defour Friday, February 23, 2018 10:06 AM edit erro hand
    Friday, February 23, 2018 10:06 AM
  • Here is a thought, have a singleton class to manage your connection.

    Imports System.Data.OleDb
    
    Public Class DatabaseConnections
        Private Shared _instance As DatabaseConnections
        Private _mainConnection As OleDbConnection
        Private _connectionString As String
        ''' <summary>
        ''' Get connection
        ''' * Determine if we need to create a new connection or if the
        '''   connection is broken or closed.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property MainConnection() As OleDbConnection
            Get
                If _mainConnection Is Nothing OrElse _mainConnection.State = ConnectionState.Broken OrElse _mainConnection.State = ConnectionState.Closed Then
                    Try
                        _mainConnection = Nothing
                        _mainConnection = New OleDbConnection(ConnectionString)
                        _mainConnection.Open()
                    Catch ex As Exception
                    End Try
                End If
    
                Return _mainConnection
            End Get
        End Property
        ''' <summary>
        ''' Connection string to database
        ''' </summary>
        ''' <returns></returns>
        Protected Property ConnectionString As String
            Get
                Return _connectionString
            End Get
            Set
                _connectionString = Value
            End Set
        End Property
    
        ''' <summary>
        ''' Set hard coded connection string or
        ''' get it from My.Settings etc.
        ''' </summary>
        Public Sub New()
            ConnectionString = "TODO"
        End Sub
        ''' <summary>
        ''' Entry into our connection
        ''' </summary>
        ''' <returns></returns>
        Public Shared Function GetInstance() As DatabaseConnections
    
            If _instance Is Nothing Then
                _instance = New DatabaseConnections()
            End If
    
            Return _instance
    
        End Function
    End Class
    

    Example using the above

    Imports System.Data.OleDb
    Public Class demo
        Public Sub Read()
            Dim cmd As New OleDbCommand() With
                    {
                        .Connection = DatabaseConnections.GetInstance().MainConnection,
                        .CommandText = "SELECT FirstName, LastName FROM Customers"
                    }
    
            Dim dt As New DataTable
            dt.Load(cmd.ExecuteReader())
        End Sub
    End Class
    
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 23, 2018 11:00 AM
    Moderator
  • Hi, Thanks for the suggestions.

    Will look at process explorer later.

    I am opening the connection in a separate module. 

    I'll have to study yours Karen, don't understand at the moment.

    I'm confused about what is going on here. The database connection is always opened exclusive which I thought means it wouldn't accept two connections anyway.

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbLoadSource & "';Jet OLEDB:Database Password='" & pSwRD & "';Mode=Share Exclusive"
    I've tried con.dispose too. No difference, and the state reports closed anyway.

    This is the error message if it means anything to anyone


    System.Data.OleDb.OleDbException (0x80004005): Could not use 'C:\Users\ADMIN\AppData\Local\Apps\2.0\Data\AXE2X599.4HD\YHCLH9HZ.POE\tes-..tion_aa64d97359edeb19_0002.0003_16ac6dd51fea1958\Data\Test.accdb'; file already in use.

       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)

       at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)

       at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

       at System.Data.OleDb.OleDbConnection.Open()

       at TEST_NEW_CON_REGIME.ModRecs.OpenConnectionLoad()
    Could not use 'C:\Users\ADMIN\AppData\Local\Apps\2.0\Data\AXE2X599.4HD\YHCLH9HZ.POE\tes-..tion_aa64d97359edeb19_0002.0003_16ac6dd51fea1958\Data\Test.accdb'; file already in use.

    Friday, February 23, 2018 11:48 AM
  • There is no need to do it in a module, that is thrashing your program. 


    It is not nice, but if you make your Connection Public (or friend) in your MainForm 

    Public Connection as new OleDB.OleDBConnection("thePath")

    And open that in the load event, 

    You can do, 

    TheConnection = MyMainForm.Connection

    Maybe because of that an Access database is just a file, we should not be so straight with it. 

    Be aware with this it becomes a single user database


    Success
    Cor



    Friday, February 23, 2018 12:08 PM
  • I put together a simple example. In the screenshot I have several Console.WriteLine that indicate what is going on. In this case I use the class (have enhanced it from the initial post) to do two SELECT statements. The first time the connection is created and used, second time just used.

    The DataGridView is loaded via a DataTable, the ComboBox is loaded via a DataTable, both use the one connection.

    New connection class (note it inherits another class)

    Imports System.Data.OleDb
    
    Public Class DatabaseConnections
        Inherits BaseExceptionProperties
    
        Private Shared _instance As DatabaseConnections
    
        Private _mainConnection As OleDbConnection
        Private _connectionString As String
        ''' <summary>
        ''' Get connection
        ''' * Determine if we need to create a new connection or if the
        '''   connection is broken or closed.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property MainConnection() As OleDbConnection
            Get
    
                _hasException = False
    
                If _mainConnection Is Nothing OrElse
                   _mainConnection.State = ConnectionState.Broken OrElse
                   _mainConnection.State = ConnectionState.Closed Then
    
                    Console.WriteLine("Need to create connection")
                    Try
                        _mainConnection = Nothing
                        _mainConnection = New OleDbConnection(ConnectionString)
                        _mainConnection.Open()
                    Catch ex As Exception
                        _hasException = True
                        _lastException = ex
                    End Try
                Else
                    Console.WriteLine("Has been created just use it")
                End If
    
                Return _mainConnection
            End Get
        End Property
    
        ''' <summary>
        ''' Connection string to database
        ''' </summary>
        ''' <returns></returns>
        Protected Property ConnectionString As String
            Get
                Return _connectionString
            End Get
            Set
                _connectionString = Value
            End Set
        End Property
    
        ''' <summary>
        ''' Set hard coded connection string or
        ''' get it from My.Settings etc.
        ''' </summary>
        Public Sub New()
            Dim builder As New OleDbConnectionStringBuilder With
                    {
                        .Provider = "Microsoft.ACE.OLEDB.12.0",
                        .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
                    }
    
            ConnectionString = builder.ConnectionString
        End Sub
        ''' <summary>
        ''' Entry into our connection
        ''' </summary>
        ''' <returns></returns>
        Public Shared Function GetInstance() As DatabaseConnections
    
            If _instance Is Nothing Then
                _instance = New DatabaseConnections()
                Console.WriteLine("Creating Instance")
            End If
    
            Return _instance
    
        End Function
    End Class
    

    The above class inherits this

    Public Class BaseExceptionProperties
    
        Protected _hasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return _hasException
            End Get
        End Property
        Protected _lastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException() As Exception
            Get
                Return _lastException
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in LastException this 
        ''' provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return _lastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul As Boolean
            Get
                Return Not _hasException
            End Get
        End Property
    End Class
    

    Data class

    Imports System.Data.OleDb
    
    Public Class Operations
        Inherits BaseExceptionProperties
        Public ReadOnly Property ReadCustomers As DataTable
            Get
    
                _hasException = False
    
                Dim dt As New DataTable
    
                Try
                    Using cmd As New OleDbCommand With
                        {
                            .Connection = DatabaseConnections.GetInstance().MainConnection
                        }
                        cmd.CommandText =
                            "SELECT Identifier, CompanyName, ContactName, ContactTitle FROM Customer;"
    
                        dt.Load(cmd.ExecuteReader())
    
                    End Using
                Catch ex As Exception
                    _hasException = True
                    _lastException = ex
                End Try
    
                Return dt
            End Get
        End Property
        Public ReadOnly Property ReadCustomersUK As DataTable
            Get
    
                _hasException = False
    
                Dim dt As New DataTable
    
                Try
                    Using cmd As New OleDbCommand With
                        {
                            .Connection = DatabaseConnections.GetInstance().MainConnection
                        }
                        cmd.CommandText =
                            "SELECT CompanyName FROM Customer WHERE Country = ?;"
                        cmd.Parameters.AddWithValue("?", "UK")
    
                        dt.Load(cmd.ExecuteReader())
    
                    End Using
                Catch ex As Exception
                    _hasException = True
                    _lastException = ex
                End Try
    
                Return dt
            End Get
        End Property
    End Class
    

    Form code (note I use Shown rather than the form's load event)

    Public Class Form1
        Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
            If Not Debugger.IsAttached Then
                MessageBox.Show("Best to try this in the IDE")
            End If
    
            Dim ops As New Operations
            Dim custTable As DataTable = ops.ReadCustomers
            DataGridView1.DataSource = custTable
    
            Dim custCountryTable As DataTable = ops.ReadCustomersUK
            ComboBox1.DisplayMember = "CompanyName"
            ComboBox1.DataSource = custCountryTable
    
    
            If Debugger.IsAttached Then
                MessageBox.Show("Look at the IDE output window")
            End If
        End Sub
    End Class
    



    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, February 23, 2018 12:33 PM
    Moderator
  • Hi, Thanks for the suggestions.

    Will look at process explorer later.

    I am opening the connection in a separate module. 

    I'll have to study yours Karen, don't understand at the moment.

    I'm confused about what is going on here. The database connection is always opened exclusive which I thought means it wouldn't accept two connections anyway.

    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbLoadSource & "';Jet OLEDB:Database Password='" & pSwRD & "';Mode=Share Exclusive"

    Unless you need exclusive access to the database, which essentially means only one user connection is allowed at a time, I would remove the SHARE EXCLUSIVE arguments from the connection string. When an Access database is opened for exclusive use the corresponding .laccdb or .ldb file (users file) is not created by the database engine and multiple user access cannot be managed.

    Paul ~~~~ Microsoft MVP (Visual Basic)


    Friday, February 23, 2018 1:46 PM
  • Hi Paul,
    I just did that, I replaced 'mode=share exclusive' with 'mode=share deny none'

    I can then see the .laccdb file after the application loads now.

    This stays there until I close the app.

    The app works correctly with no errors. What is using the file I have no idea.

    If I just remove the mode argument what default value does it use?

    I used share exclusive because it's said Access may be subject to problems if too many users (Actually the chances of two people writing to the database at the same time are minimal) Maybe I'm playing too safe.

    Friday, February 23, 2018 2:02 PM
  • Hi Paul,
    I just did that, I replaced 'mode=share exclusive' with 'mode=share deny none'

    I can then see the .laccdb file after the application loads now.

    This stays there until I close the app.

    The app works correctly with no errors. What is using the file I have no idea.

    If I just remove the mode argument what default value does it use?

    I used share exclusive because it's said Access may be subject to problems if too many users (Actually the chances of two people writing to the database at the same time are minimal) Maybe I'm playing too safe.

    If not specified the default Mode is Read/Write. You can omit this argument for your application. You could use the Process Explorer utility to see what app is keeping the database open. You also have to keep in mind that if your app is still open connections can be pooled after being closed by the app, where they remain until the application is closed or a certain period of non use has occurred.

    BTW, Visual Studio could also be the culprit if you are working with the database or running the app from the development environment.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 23, 2018 2:53 PM
  • Andy,

    A pity that you spent so much time on Access in a multi user environment simply because you don't want to spent some hours to install SQL Server Express. 


    Success
    Cor

    Friday, February 23, 2018 3:02 PM
  • Well, the reason I'm using Access is because this is an old application and it's actually used every day for the past few years. I can run it on XP, it's easy to backup the database and just copy it to another computer. I can look at it at home or wherever.

    It's not that I don't want to spend some hours with SQL Express, I do. It's just that I don't have those hours.

    Friday, February 23, 2018 5:08 PM
  • Well, the reason I'm using Access is because this is an old application and it's actually used every day for the past few years. I can run it on XP, it's easy to backup the database and just copy it to another computer. I can look at it at home or wherever.

    It's not that I don't want to spend some hours with SQL Express, I do. It's just that I don't have those hours.

    Yea that argumentation I've heard more in my live. For instance from persons who did not want to use a computer. They had no time for that, their job was already so overloaded. 

    Success
    Cor

    Friday, February 23, 2018 6:09 PM
  • Andy,

    A pity that you spent so much time on Access in a multi user environment simply because you don't want to spent some hours to install SQL Server Express. 


    Success
    Cor

    Cor,

    Over. Kill. ;-)


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, February 23, 2018 10:14 PM

  • Cor,

    Over. Kill. ;-)

    Paul,

    That is my perception also mostly. But when Andy started to write about more users, chance on concurrency errors and knowing which problems currently using an Access file can give. I came to the decisions to advice him this. 

    It can be that Andy has the same big experience with Access like you. But I'm not able to use it anymore in a VB program with all the booby traps created for Windows 10. And I was always better with Access than Bill (William V). 

    :-)


    Success
    Cor



    Friday, February 23, 2018 10:29 PM