link to SQL view and list fieldnames: error 3420 1

[fiep]

Hi,

I have linked my access front-end to an SQL server. Now I have linked to a view and want to list all field names in the view.
I use this code:

Set tblView = CurrentDb.TableDefs(strView)
For Each fldName In tblView.Fields <-- gives error 3420
Me.kzlFieldNames.AddItem fldName.Name
Next fldName

The error invalid object does not occur on the set tblView line so the table is linked correctly.
I can open the view without problem so the link is correct.

Any ideas?

Thanks.

 

[jordanking]

what is the variable definition of fldname?

.....
I'd rather be surfing

 

[fiep]

This is in the declaration part of the procedure:

Dim qdfSelect As QueryDef
Dim tblView As TableDef
Dim strView As String
Dim fldName As Field
Dim arArgs As Variant

This is part of the code works:

Set qdfSelect = CurrentDb.QueryDefs(arArgs(1))
For Each fldName In qdfSelect.Fields
Me.kzlFieldNames.AddItem fldName.Name
Next fldName

The problem is in the tblView.fields part of the code. I changed the name but still it does not work. I also created a table and tried the code with a table. Same problem.

Thanks

 

[jordanking]

i am not sure, but what are you trying to do. Why do you need the names of the fields?

try this

    rs.Open "SELECT TOP 1 FROM Customer", cnn, adOpenStatic, adLockReadOnly
    For i = 0 To (rs.Fields.Count - 1)
        Debug.Print i & " " & rs.Fields(i).Name
    Next

.....
I'd rather be surfing

 

[fiep]

Works like a charm, Thanks