How do I import data from a Teradata table to MS access in an automated way?

Question

I am new to MS Access. I have been trying to import data from a Teradata table to MS Access database. I could establish the connection between the two using VBA. However, I am not being able to write the contents to the access database.

For Excel, we generally use objects like sheets and range to populate the values. What are Access counterparts of these objects?

Given below is the code that I have been using:

Sub TBEN_PR_DSM_SEAS()

    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 "DRIVER={Teradata}; DBCNAME=ABC2; Persist Security Info=True; User ID= ******; Password=******; Session Mode=ANSI;"

    Set cmdSQLData.ActiveConnection = cn
    query = "SELECT * FROM PRODBBYCIADHOCWRK.TBEN_PR_DSM_SEAS;"
    cmdSQLData.CommandText = query
    cmdSQLData.CommandType = adCmdText
    cmdSQLData.CommandTimeout = 0
    Set rs = cmdSQLData.Execute()

End Sub

Can anyone please help me out with the rest of the part? I am using Access 2007-2010.

Thanks and regards, Nirvik

Access is a DB, understands SQL. In what environment was the above code written? — marlan, Jun 01 '16 at 14:34

score 2 · Answer 1 · answered Jun 01 '16 at 17:25

MS Access is an interesting piece of software as it can serve as both a RDMS database and GUI console to a database. By default, it connects to the Jet/ACE SQL Engine (Windows .dll files) which would compare to SQLite another file-level RDMS. However, with MSAccess.exe Office program, this default can be switched or supplemented with any other ODBC/OLEDB compliant database including the server-level RDMS (Oracle, SQL Server, MySQL, Sybase, even Teradata) using linked tables. And in connecting to external backends it would compare to MySQL's phpmyadmin, SQL Server's Management Studio, PostgreSQL's pgAdmin, and other consoles.

Therefore, consider creating a linked table to Teradata using DoCmd.TransferDatabase where changes reflect on both ends without constant import and export of table data:

DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DRIVER={Teradata}; DBCNAME=ABC2; Persist Security Info=True; User ID= ******;" _
     & "Password=******; Session Mode=ANSI;", acTable, "TBEN_PR_DSM_SEAS", "NewAccessTable"

And for a static, local copy (which would add redundancy to your application needs) you can run an append or make-table query in Query Window or VBA's DoCmd.RunSQL or CurrentDb.Execute to a local Access table using above linked table.

INSERT INTO NewAccessTable SELECT * FROM [TBEN_PR_DSM_SEAS]

SELECT * INTO NewAccessTable FROM [TBEN_PR_DSM_SEAS]

Last parameter in TransferDatabase should also be `"TBEN_PR_DSM_SEAS"` so that linked table name is `[TBEN_PR_DSM_SEAS]`, not `NewAccessTable` — marlan, Jun 01 '16 at 19:47
@Parfait thanks a lot for your response. I tried the DoCmd.TransferDatabase . But it prompts me to select the ID variables through the GUI in MS Access. Is this selection a one time thing? Ideally I want a code where I don't need to select anything through the Access GUI every time the table in Teradata changes — Nirvik Banerjee, Jun 02 '16 at 06:41
That's because you do not have a primary key or any unique index in the Teradata table. Set that in table, add autonumber if needed, and try relinking again — Parfait, Jun 02 '16 at 13:09

marlan · Answer 2 · 2016-06-02T12:49:16.683

Insert the data in the recordset into the Access table, using new recordset, Connection and Command objects.

Sub TBEN_PR_DSM_SEAS()

    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 "DRIVER={Teradata}; DBCNAME=ABC2; Persist Security Info=True; User ID= ******; Password=******; Session Mode=ANSI;"

    Set cmdSQLData.ActiveConnection = cn
    query = "SELECT * FROM PRODBBYCIADHOCWRK.TBEN_PR_DSM_SEAS;"
    cmdSQLData.CommandText = query
    cmdSQLData.CommandType = adCmdText
    cmdSQLData.CommandTimeout = 0
    Set rs = cmdSQLData.Execute()
'Up to here is your code. 
'Asuming you have a table in Access with identical number of fields, and field names:
    dim dRst as dao.Recordset, fld as Variant
    set dRst = CurrentDb.("AccessTable")
    Do While Not Rs.EOF
        dRst.AddNew
        For Each fld in dRst.Fields
            dRst.Fields(fld.Name) = rs.Fields(fld.Name)
        Next 
        'Update an entire record:
        dRst.Update
    Rs.MoveNext: Loop

End Sub

@nirvikbanerjee , First, I Like Parfait's approach, just note my comment, fix that point, I think that should work. — marlan, Jun 02 '16 at 12:20
@nirvikbanerjee , Second, I usually use linked tables and/or dao... I found [this answer here](http://stackoverflow.com/questions/19257599/how-to-insert-several-fields-into-a-table-using-ado-when-two-of-the-fields-conta). I'll modify my answer. — marlan, Jun 02 '16 at 12:26

Orthogod · Answer 3 · 2017-08-31T20:30:17.980

Tables are Sheets and Queries select Ranges. Fields are Columns and Records are Rows.

'Loop through Records
Do Until rs.EOF
'rs.Fields(0) is the first field returned from the Teradata query 
'executed in your initial question.
    Debug.Print rs.Fields(0)
    rs.MoveNext
Loop

'Append to Table
'Have to create Table1 in Access database. 
'Table1 
'Field Name: Column1 
'Data Type: Text

If rs.BOF = False Then rs.MoveFirst   'BOF = Beginning of file

DoCmd.SetWarnings False
Do Until rs.EOF   'EOF = End of file
    DoCmd.RunSQL ("INSERT INTO Table1 (Column1)  SELECT '" & rs.Fields(0) &   "'")
    rs.MoveNext
Loop
DoCmd.SetWarnings True

cn.Close
Set cn = Nothing

How do I import data from a Teradata table to MS access in an automated way?

3 Answers3