[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

MS Access VBA - returning a collection

I dug up a bit of code to handle relinking tables in MS Access.  Are part of it uses this function below to grab all of the linked table names.

when I run it I get an error I can't figure out.

Run-time error '450'
Wrong Number of Arguments or invalid property assignment.

Can anyone tell me why?

Function fGetLinkedTables() As Collection
'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As Database
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        With tdf
            If Len(.Connect) > 0 Then
                If Left$(.Connect, 4) = "ODBC" Then
                    collTables.Add Item:=.Name & ";" & .Connect, Key:=.Name
                'ODBC Reconnect handled separately
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                    'Debug.Print tdf.Name
                End If
            End If
        End With
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function

Open in new window

  • 2
1 Solution
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Interesting ... I do not get the error ...

keschusterAuthor Commented:
I've requested that this question be deleted for the following reason:

seems to have resolved itself
I tested it.  You need to call this function from another one.  Here is a function that calls your function.  Paste it into your module and run this and see if that works for you.

Public Function GetLinkedTables()
Dim colTables As New Collection
Dim varItem As Variant

Set colTables = fGetLinkedTables

For Each varItem In colTables

    Debug.Print varItem

Next varItem

Set colTables = Nothing

End Function
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What fixed the problem ?
It worked as is for me.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now