Connecting to DB in Visual Basic

[jlipsit]

I am working on a large application in Visual Basic 5.0 and one element of the application must connect to a Teradata database and perform a query, then disconnect.

I have done some minimual programming with ODBC, but need help with the connection string.

I am using Windows NT 4.0 and have alrady installed the NCR Teradata driver and configured a datasource "taradevelopment" to the database.

In the VB Project I have declared:

Dim tdata As New ADODB.Connection

Next I try to connect to the database:

tdata.Open "Provider=Teradata;" & _
"DBCName=wdev;" & _
"Database=mprodv;" & _
"Uid=USERNAME;" & _
"Pwd=PASSWORD;"

However I get an error saying the PROVIDER cannot be found.

I know I am doing something completely wrong. Can anyone help me connect properly to the database via ODBC with Visual basic? Any resources that give examples would be appreciated.

Thanks in advance.

Jim Lipsit.

 

[bengalliboy]

You need to set up the ODBC first (Say MDSN) and now try this:
Set tdata = New ADODB.Connection
With tdata
.ConnectionString = "DSN=" & "MDSN" & ";" &_
"User ID=" & UserrName & ";" & _
"Password=" & UserPassword & ";" & _
"Database=" & "mprodv"
.Open

Hope this will help U/
Good Luck

 

[PC210018]

I am in need of a DSNless connection into Teradata VIA VB. I can get a DSN version working, but I need to install this on many PC's within the company and would rather not set up DSN on each client

Thanks
Phil

 

[bgruver]

Phil,

Try this:

DIM cnnTeradata as ADODB.Connection

strConnection = _
"Provider=Teradata;" & _
"DBCName=[ip address of server];" & _
"Database=[database name];" & _
"UID=[UserId];" & _
"Password=[Password];" & _

Set cnnTeradata = New ADODB.Connection

cnnTeradata.open strConnection

 

[Pinan]

One thing to note....if oyu are using the teradata driver that has a dll version newer than 2.06, then you will need to use an RDO connection. Otherwise, the connections listed above will work.

When I get back into work in the morning, I will post the connection that I use.

 

[Pinan]

DoEvents

'define the worskpace and connection
Dim wrkSpace As Workspace
Dim conPubs2 As Connection

'make the appropriate settings
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "", "", dbUseODBC)
Set conPubs2 = wrkODBC.OpenConnection("Connection2", dbDriverPrompt, False, "ODBC;&quot

'grab the max record number from the database
Set QI = conPubs2.CreateQueryDef("", "SELECT SQL GOES HERE&quot
Set RS = QI.OpenRecordset


'insert the information into the database

Set QD = conPubs2.CreateQueryDef("",INSERT SQL GOES HERE&quot
'QD.Execute dbRunAsync

This is the connection that I use. It causes a dialog box to come up where the user selects the driver to connect with (exactly like queryman, if you have that)

 

[BharathKumarS]

Hi,
Given below is code for connecting Teradata database to VB through Data Provider

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

con.ConnectionString = "Provider=MSDASQL.1;Password=PASSWORD;" & _
"Persist Security Info=True;User ID=USER1;" & _
"Data Source=TeradataDataSource"
con.Open

rs.Open "select * from d1.customer, con, adOpenDynamic, adLockOptimistic

Bharath Kumar S You need to run fast, to stay where you are.