Inlavid Operation ereror when refresh table link

[Lisa]

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

[CDMAPoster]

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
CD********@FortuneJames.com

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

[Tom van Stiphout]

On 21 Jun 2006 10:42:45 -0700, CD********@FortuneJames.com wrote:

Hi James,
I disagree with your first point, where you are saying the Append
tries to start before the Delete is finished.
Consider this program fragment:
x=10
x=20
msgbox x
Are you saying in some rare cases x=20 may execute before x=10 is
finished, after which the x=10 line finishes, so the end result is
that the messagebox would show 10? Not likely.
Timing issues only occur if asynchronous processes are involved,
meaning a "shoot-and-forget" type of process that spins off on its own
while the rest of the program continues to execute. Something like
this pseudo-code:
Shell "c:\do_something.bat"
Print "c:\do_something_result_file.txt"
Shell starts an async process, so the Print statement can execute
before the batch file has created the file, causing a FileNotFound
error.
Tabledef.Delete is not an async method.

Back to the code at hand, I think that after Tabledefs.Delete, you
need to run:
Tabledefs.Refresh
And do this again after the Tabledefs.Append line.
The reason is that for performance reasons Access does not refresh the
Tabledefs collection until you forces it to.

I'm not sure why this has never been a problem for the OP. Still I
think it's a good idea to fix the code and try again.

<clip>

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
CD********@FortuneJames.com

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

[CDMAPoster]

Tom van Stiphout wrote:

On 21 Jun 2006 10:42:45 -0700, CD********@FortuneJames.com wrote:

Hi James,
I disagree with your first point, where you are saying the Append
tries to start before the Delete is finished.
Consider this program fragment:
x=10
x=20
msgbox x
Are you saying in some rare cases x=20 may execute before x=10 is
finished, after which the x=10 line finishes, so the end result is
that the messagebox would show 10? Not likely.
Timing issues only occur if asynchronous processes are involved,
meaning a "shoot-and-forget" type of process that spins off on its own
while the rest of the program continues to execute. Something like
this pseudo-code:
Shell "c:\do_something.bat"
Print "c:\do_something_result_file.txt"
Shell starts an async process, so the Print statement can execute
before the batch file has created the file, causing a FileNotFound
error.
Tabledef.Delete is not an async method.

Back to the code at hand, I think that after Tabledefs.Delete, you
need to run:
Tabledefs.Refresh
And do this again after the Tabledefs.Append line.
The reason is that for performance reasons Access does not refresh the
Tabledefs collection until you forces it to.

I'm not sure why this has never been a problem for the OP. Still I
think it's a good idea to fix the code and try again.

<clip>

Tom,

My speculation doesn't make a lot of sense to me either but I have seen
Access code turn some kinds of what I expected to be non-asynch tasks
into asynch tasks, perhaps through some kind of code optimization.
Plus, your x = 10 program fragment doesn't match the kind of situation
where Access tries to be so helpful with code execution. I am only
trying to find some reason that takes into account what the OP
experienced. Your Tabledefs.Refresh idea is a good one, but doesn't
make sense of the situation either. Perhaps a Tabledef.Refresh will
also force Tabledef.Create to finish synchronously. I was especially
looking for some reason for a Tabledef to be created, yet be invalid.
It seems that a Tabledef not being refreshed is not as likely as code
optimization to invalidate a Tabledef, but I don't know for sure.
Let's see what the OP discovered.

James A. Fortune
CD********@FortuneJames.com

3D Sidewalk Artist:
http://en.wikipedia.org/wiki/Julian_Beever

[Lisa]

Thanks guys. I will give these ideas a try and keep you posted.
Lisa

[Lisa]

Tried the above options both together and seperately with no luck.
This is the code with both fixes at the same time:

db.TableDefs.Delete strTblName
'new code to fix Invalid Operation error
Dim counter As Integer
Do While counter < 1020 ' Inner loop.
counter = counter + 1 ' Increment Counter.
If counter = 1000 Then ' If condition is True.
Exit Do ' Exit inner loop.
End If
Loop
'also added following line
db.TableDefs.Refresh
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
'also added following line
db.TableDefs.Refresh

Also, the problem does not seem intermittent. Once it is broken the
only way to fix it is to re-link the table manually through File, Get
External Data, Link Tables.

Thanks for your help so far,
Lisa

[Tom van Stiphout]

On 22 Jun 2006 10:26:43 -0700, "Lisa" <lb***@sierrapine.com> wrote:

If your purpose of the loop was to wait a while, remember that you
need to yield the CPU or you will not achieve the goal of allowing
another operation to use the CPU and finish up:

Dim counter As Integer
Counter=0
Do
Counter = Counter + 1
If Counter >= 1000 Then Exit Do
DoEvents 'Yield the CPU
Loop

Personally I like loops that run for a certain time better, e.g. using
the Windows API GetTickCount.
Or call the Windows API Sleep.

-Tom.

Tried the above options both together and seperately with no luck.
This is the code with both fixes at the same time:

db.TableDefs.Delete strTblName
'new code to fix Invalid Operation error
Dim counter As Integer
Do While counter < 1020 ' Inner loop.
counter = counter + 1 ' Increment Counter.
If counter = 1000 Then ' If condition is True.
Exit Do ' Exit inner loop.
End If
Loop
'also added following line
db.TableDefs.Refresh
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
'also added following line
db.TableDefs.Refresh

Also, the problem does not seem intermittent. Once it is broken the
only way to fix it is to re-link the table manually through File, Get
External Data, Link Tables.

Thanks for your help so far,
Lisa

[CDMAPoster]

Tom van Stiphout wrote:

On 22 Jun 2006 10:26:43 -0700, "Lisa" <lb***@sierrapine.com> wrote:

If your purpose of the loop was to wait a while, remember that you
need to yield the CPU or you will not achieve the goal of allowing
another operation to use the CPU and finish up:

Dim counter As Integer
Counter=0
Do
Counter = Counter + 1
If Counter >= 1000 Then Exit Do
DoEvents 'Yield the CPU
Loop

Personally I like loops that run for a certain time better, e.g. using
the Windows API GetTickCount.
Or call the Windows API Sleep.

-Tom. From the A97 Help file for DoEvents Function:

"For long-running processes, yielding the processor is better
accomplished by using a Timer or delegating the task to an ActiveX EXE
component. In the latter case, the task can continue completely
independent of your application, and the operating system takes care of
multitasking and time slicing."

GetTickCount looks promising. Simply running code for enough time
should work just as well as using DoEvents. This suggests that using
wisdom about the order of doing things can often eliminate the need for
a DoEvents or GetTickCount loop entirely. Within a single procedure a
DoEvents loop seems to solve timing problems such as those associated
with code not waiting for database object interaction to complete. I
tried the Sleep API function for that situation long ago and, IIRC, it
was totally ineffective. It seemed to stop the database object
interaction also. The Sleep function should resolve timing issues
involving code running in totally different threads. I speculate that
this interaction stopping behavior, if true, indicates that VBA running
some database object interaction tasks asynchronously for speed
optimization does the interaction task within the same thread since the
Sleep function is only supposed to suspend the current thread. Note
that an argument to the Sleep function of 0 is rumored to cause the
thread to relinquish the remainder of its time slice to any other
thread of equal priority, but I have not experimented with putting
Sleep(0) in a loop, at least not knowingly :-). I hate to do so much
speculating but I don't know enough about how VBA handles database
object interaction to speak authoritatively about it. I apologize in
advance if my recollection of the Sleep function behavior is incorrect.

James A. Fortune
CD********@FortuneJames.com