Introduction
|
To use or access a database, a user typically launches
it and opens the necessary object(s) from it. You too will need to access
a database but with code. To programmatically access a database using the
ADO library, you must first establish a connection. To support this, the
ADODB namespace provides a class named Connection. You can also
use ConnectionClass. To create a connection to a database, declare a variable of type ADODB.Connection
or ADODB.ConnectionClass and
initialize it using the New operator. This would be done as follows:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
conADO = New ADODB.Connection
End Sub
|
Opening a Connection
|
After declaring and initializing the Connection
object, you can then open the connection. To support this, the Connection
class is equipped with a method named Open. The syntax of the Connection.Open
method is:
Open([ConnectionString As String = ""],
[UserID As String = "",
[Password As String = "",
[Options As Integer = -1]
This method takes four arguments and all of them are
optional. In reality, the first argument must be defined in order to
establish as connection.
|
The Connection String
|
When establishing a connection to a database, you have
two alternatives, you can use the first argument to the Connection.Open()
method or you can separately create a connection string.
The connection string is text made of various sections separated by
semi-colons. Each section is made of a Key=Value expression.
Based on this, a connection string uses the following formula:
Key1=Value1;Key2=Value2;Key_n=Value_n;
One of the expressions you can specify in the
connection string is the name of the provider. To do this, type Provider=
followed by the provider you are using. For most databases we will create
or use here, the provider will be Microsoft.JET.OLEDB.4.0. This
means that our connection string can start with:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
Dim strConnection As String
conADO = New ADODB.Connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" . . .
End Sub
You can also include the value of the provider in
single-quotes to delimit it. If you were working on an MSDE or a Microsoft
SQL Server database, the provider would be SQLOLEDB.
The second part of the connection string specifies the
data source. To provide this information, you can assign the path and name
of the database to the Data Source attribute. Here is an example:
Provider='Microsoft.JET.OLEDB.4.0';Data Source='C:\Programs\Example1.mdb';"
It is important to note that the
content
of the connection string differs from one provider to another. If you were
working on a Microsoft SQL Server database, your connection string would
be different from the above done for a Microsoft JET database.
You can pass this connection string as the first (and
probably the only) argument to the method. Here is an example:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
conADO = New ADODB.Connection
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'")
End Sub
As mentioned earlier, the first argument is optional but you
must find a way to specify it. In effect, there is an alternative. To separately
support the connection string as its own object, the Connection class is
equipped with a property named ConnectionString, which is of type String.
To use it, declare a String variable, assign the connection string to that
variable, and assign that variable to the Connection.ConnectionString
property. You must do this prior to calling the Open() method. Here is an
example:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
Dim strConnection As String
conADO = New ADODB.Connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'"
conADO.ConnectionString = strConnection
conADO.Open()
End Sub
|
The Login Credentials
|
When creating your database, if you are working in a secure
environment and the database requires authentication, you may need to provide
login credentials, which include a username and a password. Normally, these
properties are mostly applied if you are working on a Microsoft SQL Server
database.
To specify the login credentials when accessing the
database, you can pass the second and the third arguments to the Open()
method of the Connection class.
|
Executing a SQL Statement
|
After creating a connection to a database, you can
specify what you want to do on the database. One of the
most usual operations you can perform is to submit a SQL statement to it
(the connection). This is also equivalent to executing the statement.
To execute a statement, the Connection class
is equipped with the Execute() method. Its syntax is:
Execute(CommandText As String, [ByRef RecordsAffected As Object, [Options As Integer = -1]) As ADODB.Recordset
The first argument, CommandText, can be a SQL
statement. We will study SQL in future lessons.
The second and the third arguments are optional. Here is an example:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
Dim strConnection As String
Dim strStatement As String
conADO = New ADODB.Connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'"
strStatement = "Blah Blah Blah"
conADO.Open(strConnection)
conADO.Execute(strStatement)
End Sub
When it is called, the Execute() method of the Connection
class examines and executes its (first) argument, in this case strStatement.
If this method succeeds, it returns an object called a record set. We will
study record sets in future lessons.
When using a connection, it consumes resources that
other applications may need. Therefore, after using it, you should close
it and free the resources it was using so they can be made available to
the other parts of the computer. To close a connection, the Connection
class is equipped with the Close() method. This can be done as follows:
Private Sub btnConnection_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnConnection.Click
Dim conADO As ADODB.Connection
Dim strConnection As String
Dim strStatement As String
conADO = New ADODB.Connection
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\SchoolRecords.mdb'"
strStatement = "Blah Blah Blah"
conADO.Open(strConnection)
conADO.Execute(strStatement)
conADO.Close
End Sub
No comments:
Post a Comment