Re: Inlavid Operation ereror when refresh table link
- From: CDMAPoster@xxxxxxxxxxxxxxxx
- Date: 21 Jun 2006 10:42:45 -0700
Lisa wrote:
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"), _
strConn)
db.TableDefs.Append tbl
rs.MoveNext
Wend
End With
CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
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
end.
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 blank
database. The problem still exists.
Any help would be appreciated.
Lisa
Since I can't duplicate your situation easily I'll offer a shot in the
dark. My best guess of the situation given the symptoms is that a
timing issue has developed due to network access or code being slowed
down. I suspect particularly the section of code:
db.TableDefs.Delete strTblName
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
If the code continues on after the Delete too quickly (i.e.,
equivalently, the Delete happens too slowly), there's a possibility
that the Append tries to start before the Delete has completed. Such a
situation could cause the unusual effects you're seeing. The "Invalid
Operation" would then be an unusual result of the Append starting
before the Delete is finished. Try putting a loop of maybe 1000
DoEvents after the Delete statement to see if the timing has become an
issue.
Concerning Details:
1) This theory would account for this behavior.
2) This theory would account for this behavior if the code in the
offending database is running slower than in the other databases. For
example, I have seen situations where the number of controls on a form
alone can slow down combobox rowsource query evaluation.
3) This theory would account for this if you're using the same code to
refresh the link.
4) Same as 2)
If that doesn't work, perhaps we can find another way to isolate the
problem.
Hope this helps,
James A. Fortune
CDMAPoster@xxxxxxxxxxxxxxxx
What works for blogs and news we think actually works for a much wider
set of scenarios... RSS is actually a very interesting way of not only
subscribing to news and blogs, but actually subscribing to files as
well... This idea about subscription is actually a very powerful
concept and actually applies to a lot of file concepts... At the end of
the day, RSS is just XML. -- Amar Gandhi, PDC05, DAT320
.
- Follow-Ups:
- Re: Inlavid Operation ereror when refresh table link
- From: Tom van Stiphout
- Re: Inlavid Operation ereror when refresh table link
- References:
- Prev by Date: Re: Office2007 and emailing
- Next by Date: Re: ADO Filter With Dates
- Previous by thread: Inlavid Operation ereror when refresh table link
- Next by thread: Re: Inlavid Operation ereror when refresh table link
- Index(es):
Relevant Pages
|
