Updating Links in External Database

[Guest]

Hi,

I have two frontend databases - db1 and db2 - which link to two separate backend databases. When either db1 or db2 is opened, code is run to refresh the links to the appropriate backend database. The code used to perform the refresh is Dev Ashish's code located at http://www.mvps.org/access/tables/tbl0009.htm, which resides in a module within each of the frontends.

Additionally, db1 has a reference to db2, to allow users of db1 to pull up reports from db2 without having to actually open db2. The problem is that without actually opening db2, the table links in db2 are not refreshed before reports are run. I've tried to resolve this, in db1, by calling db2.fRefreshLinks() prior to opening the report. For some reason, the code completes without error, but when I subsequently open db2 to verify that the links have in fact been refreshed, I find that they haven't.

My code is below; I would greatly appreciate any suggestions as to where I am going wrong.

Thanks in advance for any input,

Matt


Private Sub cmdRefreshdb2_Click()
Dim ref As Reference
On Error GoTo 0

Set ref = References.AddFromFile("c:\database\db2.mdb")
Application.Run "db2.fRefreshLinks"
'fRefreshLinks is a public function located in a module within db2
References.Remove ref
Set ref = Nothing

End Sub

 

[Guest]

OK, I seem to have found a solution to my problem. The following code refreshes the linked tables in db2. However, when I run it, db2 flashes onscreen for a split second. Any suggestion on how to make it so that the entire process is invisible to the user?

Thanks again,

Matt

Nevermind. I found a solution:

Private Sub cmdRefreshdb2_Click()
Dim appDB2 As Object
On Error GoTo 0

Set appDB2 = CreateObject("Access.Application")
appDB2.Visible = False
appDB2.OpenCurrentDatabase ("c:\database\db2.mdb")
appDB2.Run "fRefreshLinks"
appDB2.Quit

Set appDB2 = Nothing

End Sub

 

[Geoff]

If you're still wanting to hide screen activity, here's
two solutions.

Solution 1

Try Application.Echo False
before the activity you want to hide and
Application.Echo True
after that activity.

Solution 2

If that doesn't work, then the following will really shut down
your screen, but be careful to turn screen updates back
on again after the activity AND in your errorhandler.

Make following declaration at top of standard module:

Declare Function acb_apiLockWindowUpdate Lib "user32" _
Alias "LockWindowUpdate" (ByVal hwndLock As Long) As Long

And include this subprocedure in the module:

Sub acbShowUpdates(fShow As Boolean)
If fShow Then
acb_apiLockWindowUpdate 0
Else
acb_apiLockWindowUpdate Application.hWndAccessApp
End If
End Sub

Then, before the activity you want to hide, call the above
subprocedure as follows:

Call acbShowUpdates(False)

And after that activity:

Call acbShowUpdates(True)

You absolutely MUST also include the above line
(to turn on screen updates) at the beginning of
your errorhandler - otherwise, if an error occurs
while the screen is off, nothing will show up on the
screen! So:

On error goto ErrorHandler
...

ErrorHandler:
Call acbShowUpdates(True)

Attribution:
This code is taken from:
"Access Cookbook" by Ken Getz, Paul Litwin and Andy Baron.
(Worth every penny!)

Geoff