Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Invalid Object Error After Refreshing Linked Table

Discussion in 'Information Technology' started by Lisa, Jul 28, 2006.

  1. Lisa

    Expand Collapse

    I have an Access 2000 application that uses the following function to
    re-link my tables when I switch from my Current back end to a Dummy
    back end. I also use it to refresh my links.

    Function CreateODBCLinkedTables() As Boolean
    On Error GoTo CreateODBCLinkedTables_Err
    Dim strTblName As String, strConn As String
    Dim db As DataBase, rs As Recordset, tbl As TableDef
    Dim DatabaseName As String
    Dim ServerName As String
    Dim DSNName As String
    DatabaseName = Forms![frmLINK TABLES]![DatabaseName]
    ServerName = Forms![frmLINK TABLES]![ServerName]
    DSNName = Forms![frmLINK TABLES]![DSNName]
    If Len(DatabaseName) < 2 Or Len(ServerName) < 2 Or
    Len(DSNName) < 2 Then
    MsgBox ("Error - wrong name ?")
    Exit Function
    End If

    ' ---------------------------------------------
    ' Register ODBC database(s)
    ' ---------------------------------------------
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblODBCDataSources")
    With rs
    While Not .EOF
    DBEngine.RegisterDatabase DSNName, _
    "SQL Server", _
    True, _
    "Description=VSS - " & DatabaseName & _
    Chr(13) & "Server=" & ServerName & _
    Chr(13) & "Database=" & DatabaseName
    ' ---------------------------------------------
    ' Link table
    ' ---------------------------------------------
    strTblName = rs("LocalTableName")
    ' display what we are linking
    Forms![frmLINK TABLES]![linking] = rs("LocalTableName")
    Forms![frmLINK TABLES].Repaint
    strConn = "ODBC;"
    strConn = strConn & "DSN=" & DSNName & ";"
    strConn = strConn & "APP=Microsoft Access;"
    strConn = strConn & "DATABASE=" & DatabaseName & ";"
    strConn = strConn & "UID=" & rs("UID") & ";"
    strConn = strConn & "PWD=" & rs("PWD") & ";"
    strConn = strConn & "TABLE=" & rs("ODBCTableName")

    db.TableDefs.Delete strTblName
    Set tbl = db.CreateTableDef(strTblName, _
    dbAttachSavePWD, rs("ODBCTableName"), _
    db.TableDefs.Append tbl


    End With
    CreateODBCLinkedTables = True
    MsgBox "Refreshed ODBC Data Sources", vbInformation
    Exit Function
    MsgBox Err.Description, vbCritical, "MyApp"
    Resume CreateODBCLinkedTables_End

    End Function

    For no apparent reason, whenever I run the function I get the error
    message "Invalid Operation" displayed in a dialog box when one of
    my forms is opened. By going to the Tables view I can look at the
    data in the table that the form is based on and no errors occur. If I
    delete the link to the table, then re-link the table using File, Get
    External Data, the application works perfectly.

    More details:

    1. This code in this application has worked perfectly for years. NO
    changes have been made. Even if I go back to an old backup copy of my
    application I get the same problem.

    2. This code currently works perfectly in all of my other applications.
    Even ones that use the table in question here.

    3. I get the error even if I just refresh the link to my Current back

    4. This occurs on other computers, not just my computer. I am using XP
    and have been for quite a while.

    I thought my database was corrupt so I imported everything into a bland
    database. The problem still exists.

    Any help would be appreciated.
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh Rendered Invalid In ’84 Riots Seeks Justice (Gulf Times) Breaking News Mar 14, 2008
    Sikh News 23 Years Later, Invalid Sikh Seeks Justice (Calcutta News) Breaking News Mar 13, 2008
    Sikh News 23 Years Later, Invalid Sikh Seeks Justice (New Kerala) Breaking News Mar 13, 2008
    Sikh News 23 Years Later, Invalid Sikh Seeks Justice (Hindustan Times) Breaking News Mar 13, 2008
    Sikh Weddings Crashed By Protestors Objecting To Mixed Faith Marriages Sikh Sikhi Sikhism Mar 12, 2013

Since you're here... we have a small favor to ask...     Become a Supporter      ::     Make a Contribution     

Share This Page