Monday, June 11, 2018

Easily Ceate ODBC with DSN connection to database in vb6.0

Easily Ceate ODBC with DSN connection to database in vb6.0

In this post we will learn how to Create ODBC with DSN connection to database. In ODBC Connection, we attach any database with any type of database format like: MS-Access, SQL, MYSQL, Excel, ORACLE etc. But first we understand about some point which is mention bellow.
  1. What is ODBC or DSN?
  2. Where to find ODBC?
  3. How to Create DSN?
  4. How to Connect Database with DSN?
  5. Advantage of ODBC or DSN connectivity?
(1). What is ODBC or DSN?
The full form of ODBC is Open Database Connectivity. That means you can create a structure of database which contains information in same manner, but you can change source name with different or same with DB name.

Whenever you want to use connection such as MS-Access, then you have to need a particular drive or path location, where you can put DB and indicate as connection sting. But, if another system does not exist that drive, then we have to face problem to modify our application with drive path which is exist on that particular system.
You can lean connection to MS-Access with Login Form in VB6.0.

So, for these types of problem’s solution, we use ODBC with DSN connectivity.
(2). Where to find ODBC?
If you want to create DSN name, you have follow some steps.
  • Open Control Panel
  • Go to Administrative Tools
  • Select Data Source (ODBC) and Click on it
Open ODBC with DSN connection
Open ODBC
Create MS-Access Database:
Suppose we create DB with name “skotest.mdb” in “C:\” drive. And create Table with name “SampleTbl”. Suppose we define field description like bellow.

SampleTbl
Field Name Data Type
Indx_No Number
C_details Text
C_contact Text
Tot_Employee Text
C_Status Text
And add some record like:
SampleTbl
Indx_No C_details C_contact Tot_Employee C_Status
1 AAAAA 002220033333 230 Normal
2 XYXABC 001112224444 1200 Excellent
3 LMNOP 113332222222 540 Good
4 YYRRPPFF 115677888888 430 Good
Now, if you want to create Data Source Name name with this DB, you have to click on “System DSN” tab from “ODBC Data Source Administrator” window. 

Click on “Add” button, this process will present “Create New Data Source” box. Select driver, In which you have created your database. We select “Driver do Microsoft Access (*.mdb)” option. Then press “Finish” button.

When you press “Finish” button, it will present “ODBC Microsoft Access Setup” screen. In this Screen, First click on “Select..” button. This will show “Select Database” Box. In this box locate path and select DB file. Then click on “OK” button.

After that input “Data Source Name” and input Description. Suppose we input Data Source and Description with “MYDataSource”. Then Press “OK” button.

Now, This Source Name will add on “System Data Source” List.

The above given description define in following image with step by step process.
DSN Creation Process on ODBC
DSN Creation Process
Now this way you can create DSN.

(4). How to Connect Database with DSN?
When you want to connect DB in Visual Basic Project, There are many components available for this process. But, here we use ADODC (ADO Data Control) component for connectivity. 

You have to discover connection string. For this string process, right click on ADODC, and select “ADODC Properties”.

Click on “Build...” button from “Use Connection String” option.

This will present “Data Link Properties” box. Select “Microsoft OLE DB Provider for ODBC Drivers” Option from it, then press “Next>>” button.

This process will switch on “Connection” Tab. Expand “Use data source name” combo box and this will present data source name including your created Data Source Name. Select your created Data Source name such as “MYDataSource”.

Then click on “Test Connection” Button. This will show message saying “Test connection succeeded”. After that press “OK” Button.

Now, you will see, there is sting will be present in “Use Connection String” text box. Copy this string for further connectivity process.
Follow these steps through given bellow image instruction.
Connection String Process
 Now you can copy this string to following code. Suppose we write code on Command button for show data in List Box.
VB Code:
Private Sub Command1_Click()

'Define dscn as DB connection
Dim dscn As New ADODB.Connection
dscn.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=MYDataSource"

'define recd as record set of table and data
Dim recd As New ADODB.Recordset
recd.Open "Select * from SampleTbl", dscn, adOpenKeyset, adLockOptimistic

While Not recd.EOF

List1.AddItem recd!C_details
recd.MoveNext

Wend

recd.Close
dscn.Close

End Sub
When you run your project, this will present output like bellow.
Data Source Name Connection Output in VB
In this way you can easily use DSN connection String in VB6.0 application.
(5). Advantage of ODBC with DSN connectivity?
There is following advantage of using ODBC with DSN connection.

(a). There is no need to define particular drive to another system. You can put DB file in any drive location and create DSN name with locating that drive. And easily access data from created DB.

(b). You can put DB in a system’s drive and map this drive and assign DSN with this path. That means you can use one system DB with LAN connected other system with created Application.

Now, SKOTechLearn taught you the ODBC use and process to define the connection with database, Just follow the steps as mention in Easily create ODBC with DSN connection to database in vb6.0.

So, friends, be with us and learn simple process like this related to programming and DBMS.

0 comments: