Easily Create ODBC Connection with DSN to Database in VB6.0

In this post we will learn how to Create ODBC Connection with DSN 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, MySQL, SQL Server etc. 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 Connection Setting?

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

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 with Fields And Data Types
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

ADODC use to Show database record in MSHFlexGrid in VB6.0

(3). How to Create DSN?

Now, if you want to create Data Source Name name with this DB, simply follow steps describe bellow:

➤ First, 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 you have to locate your .mdb path and then select DB file. Then press “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

Now this way you can Create DSN in VB6.0.

(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.

Items add in Listview at runtime in VB6.0 easy tips

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.

In this way you can easily use DSN connection String in VB6.0 application.

Listview Steps and process to Edit or Delete Items in VB6.0 tips 

(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.

• Tweet
• Share
• Share
• Share