D
Dale Fye
I've completed my own Linked Table Manager wizard, so that the connections I
create to SQL Server and Oracle database on my network don't require a DSN
(DSN-less connections). This wizard exists in its own mda file, which I have
added as a reference in several of my databases.
In my test database, this wizard runs great. I've got a command button that
calls a function (fnRefreshLinks) that exists in the wizard. It opens a
form, refreshes a list of the linked tables, tests the link, and provides me
with some options.
When I run this same function from another database, it freezes. If I
display the Windows TaskManager (as if I'm going to end the MS Access
process), it unfreezes and works fine. It also works fine if I put a
breakpoint at the beginning of the
Do While ...: DoEvents: Loop code, and then remove the breakpoint the first
time it stops.
Cannot figure out what is causing this. Code follows.
Public Function fnRefreshLinks(Optional ByVal ForceRefresh As Boolean, _
Optional ByVal RefreshAll As Boolean, _
Optional ByVal PromptForDataSource As
Boolean, _
Optional ByVal Hidden As Boolean)
Dim strCriteria As String
Dim frm as Form
On Error GoTo RefreshLinks_Error
DoCmd.Hourglass True
'This code only fires if I pass the ForceRefresh parameter as True
'This is not what is causing the problem
If fnForceRefresh(ForceRefresh) Then
Call LoadLinkedTables
Call RefreshAccessLinks
strCriteria = "[Status] IS NULL OR [Status] <> 'Complete'"
If MyCount("ID", "tbl_Linked_Tables", strCriteria) = 0 Then
GoTo RefreshLinks_Exit
Endif
End If
'Hide the form initially to allow the additional steps below
DoCmd.OpenForm "frm_Linked_Table_Manager", , , , , acHidden
If RefreshAll Then
Set frm = Forms("frm_Linked_Table_Manager")
frm.cbo_Action = 2 'Refresh links
Call frm.cbo_Action_Change
strSQL = "UPDATE tbl_Linked_Tables SET RefreshLink = -1"
CodeDb.Execute strSQL, dbFailOnError
frm.sub_Linked_Tables.Requery
Call frm.EnableNext
frm.chk_Prompt = PromptForDataSource
frm.Visible = PromptForDataSource Or (Not Hidden)
End If
'Loop while the linked table manager form is loaded
'This is similiar to using acDialog, but allows the steps after the form
is
'opened to be processed, without dropping out of this function and
returning
'to the code that opened the wizard
Do While FormIsLoaded("frm_Linked_Table_Manager")
DoEvents
Loop
RefreshLinks_Exit:
DoCmd.Hourglass False
Debug.Print "Done"
Exit Function
RefreshLinks_Error:
MsgBox Err.Number & vbCrLf & Err.Description, _
vbOKOnly, "ERROR: fnRefreshLinks"
Err.Clear
Resume RefreshLinks_Exit
End Function
create to SQL Server and Oracle database on my network don't require a DSN
(DSN-less connections). This wizard exists in its own mda file, which I have
added as a reference in several of my databases.
In my test database, this wizard runs great. I've got a command button that
calls a function (fnRefreshLinks) that exists in the wizard. It opens a
form, refreshes a list of the linked tables, tests the link, and provides me
with some options.
When I run this same function from another database, it freezes. If I
display the Windows TaskManager (as if I'm going to end the MS Access
process), it unfreezes and works fine. It also works fine if I put a
breakpoint at the beginning of the
Do While ...: DoEvents: Loop code, and then remove the breakpoint the first
time it stops.
Cannot figure out what is causing this. Code follows.
Public Function fnRefreshLinks(Optional ByVal ForceRefresh As Boolean, _
Optional ByVal RefreshAll As Boolean, _
Optional ByVal PromptForDataSource As
Boolean, _
Optional ByVal Hidden As Boolean)
Dim strCriteria As String
Dim frm as Form
On Error GoTo RefreshLinks_Error
DoCmd.Hourglass True
'This code only fires if I pass the ForceRefresh parameter as True
'This is not what is causing the problem
If fnForceRefresh(ForceRefresh) Then
Call LoadLinkedTables
Call RefreshAccessLinks
strCriteria = "[Status] IS NULL OR [Status] <> 'Complete'"
If MyCount("ID", "tbl_Linked_Tables", strCriteria) = 0 Then
GoTo RefreshLinks_Exit
Endif
End If
'Hide the form initially to allow the additional steps below
DoCmd.OpenForm "frm_Linked_Table_Manager", , , , , acHidden
If RefreshAll Then
Set frm = Forms("frm_Linked_Table_Manager")
frm.cbo_Action = 2 'Refresh links
Call frm.cbo_Action_Change
strSQL = "UPDATE tbl_Linked_Tables SET RefreshLink = -1"
CodeDb.Execute strSQL, dbFailOnError
frm.sub_Linked_Tables.Requery
Call frm.EnableNext
frm.chk_Prompt = PromptForDataSource
frm.Visible = PromptForDataSource Or (Not Hidden)
End If
'Loop while the linked table manager form is loaded
'This is similiar to using acDialog, but allows the steps after the form
is
'opened to be processed, without dropping out of this function and
returning
'to the code that opened the wizard
Do While FormIsLoaded("frm_Linked_Table_Manager")
DoEvents
Loop
RefreshLinks_Exit:
DoCmd.Hourglass False
Debug.Print "Done"
Exit Function
RefreshLinks_Error:
MsgBox Err.Number & vbCrLf & Err.Description, _
vbOKOnly, "ERROR: fnRefreshLinks"
Err.Clear
Resume RefreshLinks_Exit
End Function