Access Connection query

g4naq

Member
Joined
Sep 6, 2021
Messages
7
Programming Experience
1-3
I have just created my 2nd VB.Net application today where I have created the database in MS Access 2019 and created the project in VS. The project consists of 2 forms at present. A data entry form and a database editor which I have setup using a datagridview. The datagrid is working fine (so far) and loading the data I have created..

My problem is with the data entry form. I am assuming because I created the connection in the datagrid form this has all the details configured I need to access the database in the data entry form. With the first application I wrote I did not use the datagrid so defined all the connection details within the main application.. if I do this now I get the database is in use & cannot be opened which I suspect is because of the datagrid..

.. the cmd1.Connection=DBconnection is where I am struggling.. where can I find the variable for DBConnection as I am assuming I can reuse the details from the datagrid connectivity?

SQL Query:
Private Sub SQL_Read_Defaults()
        'Raad default values for logging
        Dim cmd1 As New OleDb.OleDbCommand

        Try
            sqlSelect1 = "SELECT RSTTX, RSTRX, Mode, Band FROM Defaults"
            cmd1.Connection = DBconnection
            cmd1.CommandText = sqlSelect1

            Dim NAQdbReader As OleDbDataReader = cmd1.ExecuteReader()

            If (NAQdbReader.HasRows) Then
                'Retrieve data from SQL query
                While NAQdbReader.Read
                    txtRSTTX.Text = NAQdbReader(0).ToString
                    txtRSTRX.Text = NAQdbReader(1).ToString
                    comMode.Text = NAQdbReader(2).ToString
                    comBand.Text = NAQdbReader(3).ToString
                End While
            Else
                MsgBox("Defaults Tables empty!")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,548
Location
Sydney, Australia
Programming Experience
10+
Don't try to reuse connection objects. Store the connection string somewhere that is accessible throughout the application and then create a new connection object where and when it's required:
VB.NET:
Using connection As New OleDbConnection(connectionString)
    'Use connection here'
End Using
You should create all your ADO.NET objects with Using statements. They will then be disposed at the end of the block. For things like connections and data readers, disposing them also closes them.
 

g4naq

Member
Joined
Sep 6, 2021
Messages
7
Programming Experience
1-3
Thanks for the advice... is there a sample VB.Net / Access application anywhere I can use for reference that anyone is aware of? for instance I am unclear on how the above response works when adding a datagrid?

Clive

 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,548
Location
Sydney, Australia
Programming Experience
10+
There are ADO.NET tutorials and the like all over the place. It's all relevant because ADO.NET works the same way regardless of the data source provider, e.g. you use an OleDbConnection for Access in exactly the same way as you would a SqlConnection for SQL Server, a MySqlConnection for MySQL or any other connection type.

ADO.NET has got nothing directly to do with DataGridViews or any other controls. Getting data into a DataTable is the same regardless of what that table will be used for. Likewise, binding data to a DataGridView is the same regardless of what that data is. They are two completely separate operations. You can put them together, i.e. populate a DataTable and bind that to a DataGridView, but they are still separate operations. Think of like making a meal is not dependent on where the ingedients came from and shopping at the supermarket is not dependent on what you're going to do with what you buy. This is how you should ALWAYS approach programming problems, i.e. break them down into the smallest parts you can and address each part individually.
 
Top Bottom