Connection to Teradata from Accesss

Database

Connection to Teradata from Accesss

Hello,
I am trying to connect an Access database to Teradata DB. I am trying to import the data from a teradata table into a access Table.
The table should also be able to insert data into the Teradata DB.

I have been able to set up a connection to the Teradata DB using the following code, but an unable to insert the data from the teradata table to the Acesss Table ( Access Table is a replica of the Teradat Table, in terms of structure).

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Dim cmdSQLData As ADODB.Command
Set cmdSQLData = New ADODB.Command

Dim query As String

cn.Open "Data Source=XXX; Database=YYY; Persist Security Info=True; User ID=DGFDSG; Password=shdfgsdgf; Session Mode=ANSI;"

Set cmdSQLData.ActiveConnection = cn

query = "SELECT * FROM ABC;"

cmdSQLData.CommandText = query

cmdSQLData.CommandType = adCmdText

cmdSQLData.CommandTimeout = 0

Set rs = cmdSQLData.Execute()

I was wondering if someone can help me int his concern.

Thanks already :)

Thanks.
3 REPLIES

Re: Connection to Teradata from Accesss

Is there an error being reported? I have a script that reads from an Access table and writes to a Teradata table. See the code below:

Option Explicit

Dim SessionLogon
Dim TableDB
Dim DSN
Dim strMessage

TableDB = "tableDB"
DSN = "DSN=dbTDPID;uid=dblogon;pwd=dbpass"

Sub TDExecQuery(byval strSQL)
' execute the action query (usually a Teradata macro)
set objTDRS = objTDConn.Execute(strSQL)
set objTDRS = nothing
End Sub

'open the Teradata connection
Dim objTDConn, objTDRS

Set objTDConn = CreateObject("ADODB.Connection")
objTDConn.ConnectionTimeout = 60
objTDConn.CommandTimeout = 600
objTDConn.Open DSN

'clear out the target table
strSQL = "delete from " & TableDB & ".ua_status_codes"
TDExecQuery(strSQL)

Dim cdate
Dim objAConn
Dim enabled
Dim ldate
Dim rowcount
Dim rs
Dim skip
Dim sqlcount
Dim strSQL

' now, open the MS Access table
' select all rows
' insert into TD table

' Setup ADO objects
Set objAConn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

' Open the Access database connection (logon)
objAConn.open "EDW_ADMIN",3,3

If (Err.Number = 0) Then

strSQL = "select id "
strSQL = strSQL & ", name "
strSQL = strSQL & "from status_codes "
strSQL = strSQL & "order by id "

' Run the SQL command
rs.Open strSQL, objAConn

If (Err.Number = 0) Then
rowcount = 0
sqlcount = 0

Do While Not rs.EOF

strSQL = "insert into " & TableDB & ".ua_status_codes values ( "
strSQL = strSQL & " " & rs(0) & " "
strSQL = strSQL & ",'" & rs(1) & "' "
strSQL = strSQL & ", '' " 'no description
strSQL = strSQL & "," & ldate & " "
strSQL = strSQL & "," & cdate & " "
strSQL = strSQL & ")"
TDExecQuery(strSQL)
if Err.number = 0 then
sqlcount = sqlcount + 1
end if
rs.movenext
rowcount = rowcount+1

loop

end if
end if

' Close the recordset.
set rs = nothing

' Close the Access connection.
set objAConn = nothing

' close the Teradata connection
objTDConn.Close
set objTDConn = Nothing
set objTDRS = Nothing

strMessage = "Number of records = " & rowcount & vbCRLF & "Number of inserts = " & sqlcount
MsgBox strMessage

Re: Connection to Teradata from Accesss

Hello,
I tried connecting to the DB as specified in your code, however I get an error at

' Open the Access database connection (logon)
objAConn.open "EDW_ADMIN",3,3

The error is no default driver found.

Re: Connection to Teradata from Accesss

You need to use your own ODBC data sources for the Access and TD databases.