Returning a fields object - Access 2007 VBA

Question

I've created the following code which I want to use in the future to get a list of all the fields in a table:

Private Sub btnGetFields_Click()


   Dim myDBS As Database
   Dim fldLoop As Fields
   Dim fld As Field
   Dim relLoop As Relation
   Dim tdfloop As TableDef

   Set myDBS = CurrentDb

   With myDBS

  ' Display the attributes of a TableDef object's
  ' fields.
  Debug.Print "Attributes of fields in " & _
    .TableDefs("ALT_IDENTIFIER").Name & " table:"

  'Error occurs in line below
  Set fldLoop = .TableDefs("ALT_IDENTIFIER").Fields 

  For Each fld In fldLoop
     Debug.Print "  " & fld.Name & " = " & _
        fld.Attributes
  Next fld

.Close
   End With

End Sub

But I'm getting a Type Mistmatch - Runtime Error 13 back when I run the code.

Why? fldloop is a Fields object - i.e. a collection of field objects right? which is what the TableDefs.Fields procedure returns so why am I getting this error?

Thanks

i said that - a collection is basically 1 or more objects housed under another — Katana24, Jun 13 '13 at 14:50

score 1 · Answer 1 · answered Jun 18 '15 at 21:32

1

I was having same issue and i resolved it by changing "Field" to "DAO.Field":

Dim fld As DAO.Field

Maybe it helps another one.

Best regards

answered Jun 18 '15 at 21:32

Lionel T.

1,194
1
13
17

score 0 · Answer 2 · edited May 23 '17 at 12:14

0

Sometimes passing values to their literal types in Access causes these kinds of errors, not sure why, a quick fix is usually to dimension your variable as an open data type instead e.g:

Dim fldloop as object

Otherwise you could re-write this line:

For Each fld In fldLoop

to

For Each fld In .TableDefs("ALT_IDENTIFIER").Fields

and forget dimensioning a separate variable all together

UPDATE:

Perhaps this would be more useful for SQL Server, if you only have access via MS Access then you should be able to use this example by looping through your linked tables and dynamically re-building a a Pass Through Query

What is the equivalent of 'describe table' in SQL Server?

edited May 23 '17 at 12:14

Community

1
1

answered Jun 13 '13 at 14:43

Matt Donnan

4,933
3
20
32

won't that make it rather large then, like declaring a variable as variant instead of its usual type? – Katana24 Jun 13 '13 at 14:51
also - the i tried what you suggested (rewriting the line) and got the same error. Also tried changing it to an object but got the same error :/ – Katana24 Jun 13 '13 at 14:53
I believe that this error I'm getting may well have something to do with the table being a Linked Table. I ported the code to another database and had it look at a local table and it worked fine. Still need to find the solution for linked tables – Katana24 Jun 13 '13 at 15:42
Not sure if I've ever used this sort of approach for linked tables, please see the link to another question I have inserted. – Matt Donnan Jun 13 '13 at 20:29

Katana24 · Accepted Answer · 2013-06-18T08:31:13.603

Found the problem: the reason I was getting the error was because I wasn't referring to the exact field. Though I'm still unsure as to why an error was thrown on a Fields object that was assigned a Fields value.

Here's the code:

   Dim f As Field
   Dim fldTableDef As Field
   Dim Rst As DAO.Recordset
   Dim numField As Integer

   Dim linkedTable As String
   linkedTable = "ALT_IDENTIFIER"

   Set Rst = CurrentDb.OpenRecordset(linkedTable)
   numField = Rst.Fields.Count

   'Loop through
   Dim index As Integer

   For index = 0 To numField - 1
        If Rst.Fields(index).Type = dbDate Then
            Debug.Print "Field: " & Rst.Fields(index).Name; " = Date/Time" & Rst.Fields(index).Value
        End If
   Next

Returning a fields object - Access 2007 VBA

3 Answers3