Teradata > MsAccess table (1 Viewer)

termsig · Mar 18, 2019

Hello,

I'm trying to pull table/view from Teradata via VBA without any joy.
Did look on few different websites and the closes one which is looking like working example would be

Code:

Sub TBL()

    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= USER; Password=PASS; Session Mode=ANSI;"

    Set cmdSQLData.ActiveConnection = cn
    query = "SELECT * FROM DB.TABLE;"
    cmdSQLData.CommandText = query
    cmdSQLData.CommandType = adCmdText
    cmdSQLData.CommandTimeout = 0
    Set rs = cmdSQLData.Execute()
    dim dRst as dao.Recordset, fld as Variant
    set dRst = CurrentDb.("tbl")
    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

however for some reason it's failing on

Code:

set dRst = CurrentDb.("tbl")

line.

Any idea / guide ?

The_Doc_Man · Mar 18, 2019

First, if it is failing, what is the error message? (Text AND number, please.)

Second, is there a table or query withing the current db named "TBL" ? We don't see such a definition in this code, but in this context it could be externally defined.

Third, that syntax seems a bit wrong. I believe that even for ADO you need to open a recordset using the OpenRecordset function, which returns a recordset object.

termsig · Mar 18, 2019

The_Doc_Man said:
First, if it is failing, what is the error message? (Text AND number, please.)

Compile Error : Expected: identifier or bracketed expression

Second, is there a table or query withing the current db named "TBL" ? We don't see such a definition in this code, but in this context it could be externally defined.

Yes , table exist in the current table which mirror all headers from Teradata view.

Third, that syntax seems a bit wrong. I believe that even for ADO you need to open a recordset using the OpenRecordset function, which returns a recordset object.

Can you elaborate more what you think that is wrong as I'm fine with changing the code based on something already pre-written - still trying learn VBA.

termsig · Mar 19, 2019

anyone have any clue ?

The_Doc_Man · Mar 19, 2019

You need to provide something that would return an object when dealing with the SET verb, because it associates an object (in this case, you want a recordset) to an object variable (in this case, the variable dRst). The syntax you used did not involve anything that was an object.

If you were going to pick out a property of CurrentDB (implied by the dot that follows it), the parentheses would be wrong because you would need the name of the property in that spot. The () simply confuse the parser, hence the error you reported. And the default property wouldn't be a recordset anyway.

You probably want something like:

Code:

set dRst = CurrentDb.[COLOR="Red"]OpenRecordset[/COLOR]("tbl")

Teradata > MsAccess table (1 Viewer)

termsig

Registered User.

The_Doc_Man

Immoderate Moderator

termsig

Registered User.

termsig

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread