Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: Refreshing Linked Tables

    Hi,

    The code snippet below is how I understand I'm supposed to refresh the link between a table in my currect db (calP2) and a corresonding table (calP) in a different db. The table containing calP changes every week, and inpFN is meant to contain the new date, correctly formatted. File dates are always Saturday; hence the "- Weekday(Date)" in the assignment.
    Dim strConnect As String, inpFN As String, cP2 As TableDef

    inpFN = Format(CDate(InputBox("Enter E-Bill Date", , Date - Weekday(Date))), "yyyymmdd")
    strConnect = "N:\TD\rch\" & inpFN & ".mdb"

    Set cP2 = CurrentDb.TableDefs("calP2")
    With cP2
    .Connect = strConnect
    .RefreshLink
    End With
    The problem is that when the code gets to the line ".Connect = strConnect", I get an error message saying "Object Invalid or No Longer Set," error # 3420.
    In Debug mode, both inpFN and strConnect contain the correct information. How do I fix it?

    Thanks for your help.

    Sam

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    I may be wrong but don't you need a proper Access connection string? Something like this:

    .Connect = ";DATABASE=" & strConnect
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The way I understand the Help on this item, since the source db is MS Access, that parameter can be omitted. Do I misunderstand it?

    Sam

  4. #4
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    To be honest I'm just going from memory so am not 100%.

    Have you tried hardwriting the code i.e.

    .Connect = "N:\TD\rch\..."
    Regards
    JD

    Software-Matters

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The object (cp2) seems to be the problem, not the code, as the error is "object invalid..."

    SL

  6. #6
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Could it be a permissions issue?
    Regards
    JD

    Software-Matters

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I figured out the problem. I once had this before, when I tried opening a linked table as a dbOpenTable object. Access won't let you. It can only be opened as a dbDynaset object. No prob. I simply wrote code to delete the old linked file, and re-link the same table using DoCmd.TransferDatabase, with the string I created as a parameter in the stament. Works like a charm. ("Many ways to skin a cat...")

    Thanks

    Sam

Posting Permissions

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