vb6dbaccess

8/8/2019 VB6DBAccess

1/20


2/20

VB6 Data Access 2 Data Object Library approaches

DAO (Data Access Objects)

Original access strategy (up to VB6)

Closely linked to MS Access

ADO (ActiveX Data Objects)

Newer approach

More generic

Same approach for ASP (VB Script active server

pages)


3/20

Microsoft Components Jet Database Engine: VBs database engine for MS Access

& ISAM (Indexed Sequential Access Method) DBMSs

ODBC (Open Database Connectivity): Interface for SQLrelational databases

RDO (Remote Data Objects): Original component to

access databases across a network

OLE DB (Object Linking & Embedding Databases): newergeneric interface accessing SQL & nonSQL databases


4/20

ADO

VISUAL BASIC 6.0 APPLICATION

ADO

OLE DB

ODBC

SQL DATA NON SQL DATA OTHER

e.g. MS Access (JET) Text Files, Excel LEGACY

SQL Server, ORACLE etc SYSTEMS


5/20

Data Connection Approaches Data Control based

non-programming

associated with form based controls (DataGrid,

DataList etc)

Limited control over data

Programming based uses Connection object (ADO)

allows closer data control


6/20

VB6 ADO Linkage Issues Under Project|References

Microsoft ActiveX Data Objects 2.5 Library

Under Project|Components (added to Toolbox)

Microsoft ADO Data Control 6.0 (OLEDB)

Microsoft Data Grid Control 6.0 (OLEDB)

Microsoft Data List Controls 6.0 (OLEDB)

DataList

DataCombo


7/20

Project|References


8/20


9/20

Form with Data Control Object


10/20

Properties Data Control Object (Adodc1)

Connection String - Click expansion button & build

connection Record Source - SQL query (SELECT * FROM emp;)

or table (emp)

Command Type - 1-adCmdText for SQL query

2-adCmdTable for table

Textbox (Text1)

Data Source - Adodc1

Data Field - ename


11/20

Database Access using VB

Programming Requires ADO object library setup (Project|References)

Requires creation, setup & opening of variables for

Connection object (using Connection String) Recordset object (Using SQL Query/Table)

Recordset Processing

Close Recordset & Connection


12/20

ADO Database Connection

Dim adoconn As ADODB.Connection

Dim recset As ADODB.Recordset

Set adoconn As New ADODB.Connection

Set recset As New ADODB.Recordsetadoconn.Open Provider=Microsoft.Jet.OLEDB.4.0; _

Data Source=C:\projemp.mdb; User ID=Admin;Password=

recset.Open emp, adoconn, adOpenDynamic

Do Until recset.EOF

List1.AddItem recset!enamerecset.MoveNext

Loop

recset.Close

adoconn.Close


13/20

ADO Object Model Connection Object

Recordset Object

Field Object

Command Object

Parameter Object Error Object


14/20

Connection Object Connection String

Provider (e.g. Provider = Microsoft.Jet.OLEDB.4.0;)

Data Source (e.g. Data Source = C:\db\projemp.mdb;)

Username & Password (e.g. User ID = Admin; Password = ;) Dim As ADODB.Connection

Set = New ADODB.Connection

.Open ConnectionString

Other Connection Methods

Close, Cancel Execute (CommandText, RecordsAffected, Options)

CommandText can be SQL query

BeginTrans, CommitTrans, RollbackTrans

Transaction commands for starting, ending and undoing stored

transactions if required


15/20

Recordset Two dimensional array holding a table/result of SQL

ADO allows four different cursor types (ways of using recordsets)

Dynamic Cursor (CursorType = adOpenDynamic)

moves freely through recordset (forwards, backwards, BOF, EOF) all appends, updates, deletes made by other users seen by recordset

and can be changed by recordset (slow)

Keyset Cursor (CursorType = adOpenKeyset)

Similar to dynamic - allows viewing of records changed by other

users but disallows changes on those records (slow) Static Cursor (CursorType = adOpenStatic)

cannot see other users changes (hence works faster)

Forward Only Cursor (CursorType = adOpenForwardOnly)

like static cursor but no backwards recordset movement (fastest)


16/20

Recordset Setup Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset

Recordset Methods

AddNew, Delete, Update

Open, Close, Cancel

rst.Open , ,

e.g. Rst.Open emp, adoconn,

Move, MoveFirst, MoveNext, MovePrevious, MoveLast Seek - searches the index and moves to the row with required value

Find - searches for row with required value (seek is faster but requires

index)

Requery - runs query recordset is based on again


17/20

Fields Collection ObjectDim fld As Field

rst.Open emp table emp from projemp database put into recordset rst

Set fld = rst.Fields(1) assign 2nd recordset field(ename) to fld

Debug.Print fld.Name prints the field name in Immediate window

Debug.Print rst.Fields(2) prints current record value for 3rd field (salary)

Debug.Print rst.Fields.(ename) prints current record value for ename

Debug.Print rst.Fields.Count prints number of fields in tecordset rst


18/20

Displaying RecordsAssuming Connection (conn) to projemp.mdb & recordset (rst)

rst.Open SELECT * FROM emp;, conn

Print eno, ename, salary, dno

Do Until rst.EOF

Print rst(0), rst(1), rst(2), rst(4)

rst.MoveNext

Looprst.Close

conn.Close


19/20

Finding RecordsAssuming Connection (conn) to projemp.mdb & recordset (rst)

rst.Open SELECT * FROM emp;, conn

rst.Find = ename = smith

Print rst.Fields(0), rst.Fields(1) prints 1st &2nd fields (eno&ename) for

record found


20/20

Command Object Queries the data source and returns a recordset

Dim com As ADODB.Command

Set com = New ADODB.Command

com.ActiveConnection "Provider=Microsoft.Jet.OLEDB.4.0;_

Data Source=D:\projemp.mdb;User ID=Admin;Password=

com.CommandText = SELECT * FROM works

com.CommandType = adCmdType

Set rst = com.Execute

Alternatively can use a stored procedure (query created in data source)comCommandText = Query1 query defined in projemp.mdb

comCommandType = adCmdStoredProc

Performs SQL INSERTs, UPDATEs etc to alter data source

vb6dbaccess

Documents