VB Database Connection

It is possible to connect to the database in several ways; using JET ,ODBC or DATAENVIRONMENT.

Data Access Object example - JET Connection
Example 1: Connecting to a Database dynamically using VB Code and SQL

Database connection Example: Public db As New ADODB.Connection 'The above statement creates an ActiveX Data Object Database Connection Public DEL_RS As New ADODB.Recordset 'The above statement creates an ActiveX Data Object table (recordset) Connection Private Sub cmdSave_Click Set db = New ADODB.Connection 'The above statement creates a New ActiveX Data Object Database Connection db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\myDb.mdb;Persist Security Info=False;Jet OLEDB:Database Password=xyz" 'The above statement opens the Database Set DEL_RP = New ADODB.Recordset Set DEL_RS = New ADODB.Recordset 'The above two statements creates a New ActiveX Data Object recordset Connection DEL_RP.Open "INSERT INTO purchaseDetails([productName],[productCategory],[itemCode],[purchasedQuantity],[unitBPrice],[totalPurchaseAmount],[purchaseDate],[productDescription])VALUES('" & txtPName.Text & "', '" & cmbCName.Text & "','" & txtICode.Text & "','" & txtQPurchased.Text & "','" & txtUPrice.Text & "','" & txtTCost.Text & "','" & txtPDate.Text & "','" & txtPDescription.Text & "')", db, adOpenDynamic, adLockOptimistic 'The above statement opens the table and selects the specified fields If DEL_RP Is Nothing Or DEL_RP.State = 1 Then DEL_RP.Close End If 'The above statement checks whether the table is already opened and with records or closed DEL_RP.CursorLocation = adUseClient DEL_RP.Open "SELECT itemNo,[productName],[productCategory],[itemCode],[unitBPrice],[purchaseDate],[purchasedQuantity],[productDescription] FROM purchaseDetails WHERE itemNo>0", db, adOpenKeyset, adLockOptimistic DEL_RP.MoveLast If DEL_RS Is Nothing Or DEL_RS.State = 1 Then DEL_RS.Close End If   DEL_RS.Open "INSERT INTO stockDetails(itemNo,[productName],[productCategory],[itemCode],[unitBPrice],[purchaseDate],[purchasedQuantity],[productDescription])VALUES('" & DEL_RP.Fields(0).Value & "','" & DEL_RP.Fields(1).Value & "','" & DEL_RP.Fields(2).Value & "','" & DEL_RP.Fields(3).Value & "','" & DEL_RP.Fields(4).Value & "','" & DEL_RP.Fields(5).Value & "','" & DEL_RP.Fields(6).Value & "','" & DEL_RP.Fields(7).Value & "')", db, adOpenDynamic, adLockOptimistic 'The above statement inserts data into the table using the insert statement txtPName.Text = "" cmbCName.Clear txtTCost.Text = "" txtICode = "" txtUPrice.Text = "" txtQPurchased.Text = "" txtPDescription.Text = "" cmdSave.Visible = False cmdUpdate.Visible = True frmPurchase.Refresh End Sub Private Sub cmdUpdate_Click If txtPName.Text = "" Or cmbCName.Text = "" Or txtUPrice.Text = "" Or txtQPurchased.Text = "" Then MsgBox "Enter value into text box", vbExclamation Exit Sub End If   txtTCost.Text = Val(txtUPrice.Text) * Val(txtQPurchased.Text) cmdSave.Visible = True cmdUpdate.Visible = False End Sub

Displaying Records via a Data Grid
'Declare in General--->Declaration Dim con As New ADODB.Connection Dim rsGrid As New ADODB.Recordset Dim sql As String Private Sub Form_Load With con .Provider = "sqloledb" .ConnectionString = "user id=sa;pwd=;data source=taposh;database=northwind" .Open End With sql = "select * from employees" rsGrid.CursorLocation = adUseClient rsGrid.Open sql, con, 3, 1, adCmdText Set DataGrid1.DataSource = rsGrid End Sub

Connecting in Design mode using Data Control
To start with, create a folder on the desktop named "collegeMIS"

Creating a database
 Create a sub folder named collegeDB in the collegeMIS folder Create database in the collegeDB folder(in this case we will use dotcomcollege.mdb as the database name) In the database, create a table "tblstudent whose schema is as shown below: tblstudent(regno,sname,fname,bdate,admdate) Populate your table student with at least five records Convert your database to office97 format via the tools menu on the menubar 

Creating a VB Project for the database
 Create a Visual Basic application named dotcomMIS.vbp and save it in collegeMIS folder Create a form whose name (in the property window) is frmstudDetails with the caption Studet Details</li> Place the following controls on the form Label with caption Admission No and name lblRegno</li> Text Box with name txtStudno</li> Label with caption Sur Name and name lblSname</li> Text Box with name txtSname</li> Label with caption First Name and name lblFname</li> Text Box with name txtFname</li> Label with caption Birth Date and name lblBdate</li> Text Box with name txtBdate</li> Label with caption Date of Admission and name lblAdmdate</li> Text Box with name txtAdmdate</li> </ol> Create a data control object with the name datDetails</li> Set the following properties for datDetails: databasename --> dotcomcollege.mdb</li> recordsource --> tblstudent</li> </ul> Connect all the text boxes to the data control by setting the following two properties for each text box: <li>Datasource --> datDetails it specifies the name of the data control </li> <li>DataField --> studNo this property specifies the a field in the table whose conntent will be displayed in the text box </li> </ul> </ol>