Invalid Object error after refreshing linked table



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.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question