MS Access VBA - returning a collection

Posted on 2012-09-06
Last Modified: 2012-09-06
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

Question by:keschuster
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Interesting ... I do not get the error ...


    Author Comment

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

    seems to have resolved itself
    LVL 2

    Accepted Solution

    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
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    What fixed the problem ?
    It worked as is for me.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now