Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating Linked Table Source Using VBA (97)

    I have a linked table called "enable" and I would like to change the source of the data using VBA.

    I have the following 2 lines in a function:

    CurrentDb.TableDefs("enable").Connect = "DATABASE = j:groupsvaluationewmethfpvafpen0406.mdb"
    CurrentDb.TableDefs("enable").RefreshLink

    The 0406 is the year and month of my datafile. The table is currently linked to the 0405 datafile. When I execute this function, it seems to have no effect. The link stays as 0405. What am I doing wrong?

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating Linked Table Source Using VBA (97)

    This is because CurrentDb creates a new object each time you use it, so the second instance is not connected to the first one. Instead, use an object of type DAO.Database that you set to CurrentDb. That way, its gets created only once:

    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    With dbs.TableDefs("enable")
    .Connect = ";DATABASE=j:groupsvaluationewmethfpvafpen0406.mdb "
    .RefreshLink
    End With
    Set dbs = Nothing

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating Linked Table Source Using VBA (97)

    Thanks so much, Hans. This works perfectly now. I suspect that this has been my core problem causing all of my posts in the Access board recently. I assumed that CurrentDB behaved just like ActiveWorkbook in Excel VBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •