Connecting to Microsoft Access with ADO - Write code to open and close the ADODB connection to the database
(Page 6 of 6 )
This is a very short script and you get the assistance of intellisense technology. Without this help, lazy as I am, I would not have taken to Microsoft technology; no need to thumb hefty volumes in the shadow of your manager hawk. Just type in the code window ADODB, and you will get a drop-down as shown, giving you all the object choices you can make: it could be a connection, it could be a command, and so forth. Insert the following code as shown in the next paragraph in the click event of the button.
Private Sub Command0_Click()
Dim conn As New ADODB.Connection
Dim prop As Property
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\
Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Persist Security Info=False"
conn.Open strg
MsgBox ("connection open")
conn.Close
MsgBox ("Connection closed")
End Sub
You created a new connection object called conn. The variable string is the one that you copied from the UDL's file while it is displayed as a text, the mantra. The connection can be opened, if you provide the connection string. The First message says the connection is Open. Trust me, the connection is really open. It is always a good practice to close the connection, and therefore you see the conn.close statement. The second message box shows that the connection is closed. If the connection is closed you have no access to the data. This is a basic step you need to take when you want to access data.
Review connection properties using codeYou saw earlier in the Object Browser the driver specific connection properties for the referenced ADO library. You might also have seen some of them in the All tab of the data link window. We will look at the same using code since we have been able to open a connection.
Add some code to modify the earlier code as follows. In ADO it is all about collections. The property collection is one such collection (refer to the ADO Model). The variable prop is declared to be a property, conn.Properties gives you access to all the properties, and the For... Next logic block gives you all the properties pertinent to this connection. We will also find out certain other properties of the connection object, such as the Ado Version in use, the connection state, and the default connection timeout.
Just remember to add a textbox to the form before running the page and make sure the Text box number matches with what you are adding to the form. The message boxes when they appear should be clicked OK; they are left intentionally in code for you to follow the code as it gets executed. The variable strAdd just gathers up all the strings and finally places them in the text box.
Private Sub Command0_Click()
Dim conn As New ADODB.Connection
Dim strAdd As String
strAdd = ""
MsgBox ("ADO version used is: " & conn.Version)
strAdd = strAdd + "ADO version used is: " & conn.Version
Dim prop As Property
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\
Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;
Persist Security Info=False"
conn.Open strg
MsgBox ("connection open")
'
MsgBox (conn.State)
strAdd = strAdd & vbCrLf & "Connection status (open) is :
" & conn.State MsgBox ("The default connection timeout is: " &
conn.ConnectionTimeout)
strAdd = strAdd & vbCrLf & "The default connection timeout is:
" & conn.ConnectionTimeout
'
For Each prop In conn.Properties
Debug.Print prop.Name, "=", prop.Value
Next prop
'
conn.Close
MsgBox ("Connection closed")
MsgBox (conn.State)
strAdd = strAdd & vbCrLf & "Connection status(closed) is:
" & conn.State
With Text7
.SetFocus
.Text = strAdd
.BackColor = RGB(0, 0, 0)
.ForeColor = vbYellow
End With
End Sub
When you run this code you will see the following window after you click OK to the various messages that show up.
In the immediate window you will see the message printed that lists all the properties for this source. Go to Microsoft Visual Basic Editor screen and open up the immediate window. The following is what you will see (only a part presented).
Summary
ADO is easier to handle than DAO. Anyway DAO is out and ADO is in. It has great scripting support and you have intellisense to make life a lot easier. The UDL file is a powerful means to connect to OLEDB sources, of which there are very many. In this tutorial only a couple of properties of the Connection object are described. There are other properties, and these will be presented after looking at some of the related other objects.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 18 
