Linked Tables - Check if tables are available on startup?

[chris]

Hi,

I have a split mdb application that has all the functionality in one
MDB and all the data in another. The application can run with the data
either self-contained on a user's computer, or with limited sharing
across a network. There is already functional code to manage relinking
the tables using ADO depending on the availability of the databases.
If they are set in network mode but their network is unavailable then
the application will fall-back to a local copy.

Although these checks are done as the first thing, in testing the
application performs very slowly. If we rename the network database it
falls back gracefully and quickly, but if we ust disable the network
connection there is a very long pause. Presumably this is as Access is
trying to get to the network database but cannot, so we have to wait
for a timeout. The wait is not acceptable, so I'm looking for
potential solutions.

Is there a way to stop Access looking for linked tables when it first
starts up, or at least delaying it until I have verified the links are
correct? Would Access normal verify linked tables before it starts the
execution of my application? I have to admit that I'm the latest in a
number of developers on this one, and although I've ironed it out
pretty flat there's still the odd surprise.

If that is not the case, would we have to leave the application in a
"guaranteed working" state (i.e. remove all the links / relink to the
local copy) when it shuts down? This isn't a difficult proposition,
but could cause problems if the application/computer doesn't shut down
properly.

If anyone can shed any light or has any good ideas please feel free.
I'm open to your suggestions. Thanks in advance!

Chris.

 

[Douglas J Steele]

How are you currently testing?

 

[chris]

Hi Douglas,

Thanks for your response.

I am currently testing by disabling the network connection in Control
Panel. This models the situation the end user will be in, i.e. they
want to work but are not connected to a network.

From running the program MDB it takes about 15 seconds before the

Access window appears, and another 1min 30secs before I see any signs
of life (I have a status bar message as the first thing the program
does, before it starts my checking/relinking code). The code then runs
OK, but there is still the problem of the nearly 2 minute wait before
the program appears to do anything.

FYI: There will also be testing where the program is looking for an
unreachable server/file, as if they were on a network but not their
"work" network.

If you require any further information please ask.

Chris.

 

[Douglas J Steele]

One possibility (rather drastic) would be to delete all of the linked tables
when you shut down the application.

When you open it, check for the connection, and reestablish the linked
tables if it's present.

 

[Albert D.Kallal]

Hum, I test for a good link on startup..and I normally don't get such a
large delay.....

However, I think this is a network issue....

ms-access does not "check", or test the links at startup...so, that is not a
issue..

My code to "test" if the link ok does NOT try and open a table, but does the
following....


If Dir(strBackEnd) = "" Then

If MsgBox("Location of data cannot be found" & vbCrLf _
& "Perhaps you are not logged on correctly, or perhaps " &
vbCrLf _
& "the data is on another computer." & vbCrLf _
& " Do you want to try and re-link to the back end data ?", _
vbCritical + vbOKCancel, AppName) = vbOK Then

...... code here to "browse" to a file..and re-link...

So, I don't actually try and open the table across the network...I check for
the existence of the file. This works fast...

and, here is the function I use to get the path name to the back end..

Function strBackEnd() As String

Dim mytables As TableDef

Dim strTempBack As String
Dim strFullPath As String
strFullPath = ""

For Each mytables In CurrentDb.TableDefs
If Left(mytables.Connect, 10) = ";DATABASE=" Then
strFullPath = Mid(mytables.Connect, 11)
Exit For
End If
Next mytables

strBackEnd = strFullPath

End Function



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal




is

 

[Chris]

Hi Douglas,

Thanks for your continued help.

The application can be relinked (or tables deleted), but this could
cause problems if the application doesn't shut down properly, or the
computer crashes. This is a solution, but I'd rather see if there's a
graceful way to check for the reachability of the network.

Chris.

 

[Chris]

Hi Albert,

Thanks for your reply.

I also check that the file exists (from my application's point of view)
using the same DIR command. This should not be the problem. I also
don't try to open a connection to the data until I have verified the
file is there.

In my investigations I have discovered an autoexec macro that ran it's
own table-link verify routine. As this was trying to open connections
I think it was this that caused the initial problem. I have removed
it, and the program now starts up in a much more timely fashion. It's
still not quite perfect, but it's acceptable.

Thanks for your help, and the confirmation that Access doesn't try to
verify linked tables on it's own.

Chris.

 

[Guest]

The application can be relinked (or tables deleted), but this could

cause problems if the application doesn't shut down properly, or the
computer crashes. This is a solution, but I'd rather see if there's a
graceful way to check for the reachability of the network.

Figured I might chime in as I'm working on this now... Mary Chipman has
just recently suggested that best practice is to delete the links on
application close and on application open. Then re-link on application open.
This takes care of any crashing problems, and in the case of my ODBC
connections to SQL server my potential security issues.

hope this helps.

-David