ADO ConnectionString Maker for Available DSNs

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

John Barker

Version Compatibility: Visual Basic 6

More information:

Using this example, you will create and edit ConnectionStrings based on all availiable DSN and ODBC Driver properties. For example, you can connect to an MS Access Database with 3 different ADO Providers. This code gives you a taste of the many variations of ADO ConnectionStrings that can be arranged with your system's ODBC settings.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
' (None)

Code:

'John B
'VB Programmer
'zippity@leading.net

'******************
'YOU NEED:

'VB 6 with:
'Reference Setting checked - "Microsoft OLE DB Service Component 
'1.0 Type Library"

'Reference Setting checked - "Microsoft ActiveX 
'Data Objects 2.0 Library"

'******************

'STEPS:

'(1)Create EXE project

'(2)Place a long TextBox control named "txtconstr" on form  
'[this is your ADO string]

'(3)Place a CommandButton called "cmdNew" on form

'(4)Place a second CommandButton called "cmdEdit" on form

'(5) Insert this code [new]

Private Sub cmdNew_Click()
On Error GoTo ERRORHANDLER
    Dim objDataLink As New DataLinks
    Dim strConn As String

strConn = objDataLink.PromptNew
    txtConStr.Text = strConn

Exit Sub
ERRORHANDLER:
    txtConStr.Text = ""
End Sub

'(6)Insert this code [edit]

Private Sub cmdEdit_Click()
On Error GoTo ERRORHANDLER
    Dim objDataLink As New DataLinks
    Dim ADOCN As New ADODB.Connection
    Dim strConn As String

ADOCN.ConnectionString = txtConStr.Text
    strConn = objDataLink.PromptEdit(ADOCN)
    txtConStr.Text = ADOCN.ConnectionString

Exit Sub
ERRORHANDLER:
     MsgBox "An invalid ADO ConnectionString was detected. Please try again!"
     txtConStr.Text = ""
End Sub