Linking of tables at startup

[robert d via AccessMonster.com]

I'm doing some testing on what happens if a user starts my app and the path
to the back-end file is not valid.
I puposefully changed the folder name for the backend file, so that Access
would have a problem. Thereafter, I immediately get an error message when I
startup my app that says:

"'Pathname' is not a valid path......"

I want to confirm that this is being caused by Access looking in the
MSysObjects table for the path to the existing link and determining that it
is not valid (because I changed the folder name). If this is the case, then
I guess there is no way to intercept this message. I have not been able to
get Access to stop at the first line of code it encounters upon opening my
app. The error mentioned above happens prior to the 1st line of code.

I would appreciate a confirmation of my understanding or lack thereof.

Thank you.

 

[John Nurick]

Hi Robert,

AFAIK his means that something in your application - maybe the startup
form, maybe something invoked by the AutoExec macro - is trying to
access data in a linked table *before* any of your code runs.

If so the thing to do is to check the linked tables at the beginning of
your startup form's Open or Load event procedure.

One technique is to iterate over CurrentDB.TableDefs, checking
TableDef.Connect which indicates whether it's a linked table and what
it's linked to. Parse the path to the back end out of .Connect and see
if the file can be found. If not, put up a dialog to let the user
specify the new back end. See e.g.
http://www.mvps.org/access/tables/tbl0009.htm

Another way is to iterate over TableDefs trying to access each table,
e.g.

Dim Dummy As Variant

On Error Resume Next
Dummy = CurrentDb.TableDefs(strTable).Fields(0).Name
If Err <> 0 Then
'Table can't be accessed: if it's a linked table, relink it
Else
'Table is OK
End If
On Error Goto 0

A web search for
"relink tables" access database tabledefs
will find more.

 

[robert d via AccessMonster.com]

Thanks, John.

You were right. Somehow, the Recordsource of my StartAppForm got set to one
of the tables. It's supposed to be an unbound form. I do have code that
checks the links, but I kept getting these other errors before that code got
invoked.

I've got a few other problems now, but at least I'm getting into my code!

Thanks, again.

Hi Robert,

AFAIK his means that something in your application - maybe the startup
form, maybe something invoked by the AutoExec macro - is trying to
access data in a linked table *before* any of your code runs.

If so the thing to do is to check the linked tables at the beginning of
your startup form's Open or Load event procedure.

One technique is to iterate over CurrentDB.TableDefs, checking
TableDef.Connect which indicates whether it's a linked table and what
it's linked to. Parse the path to the back end out of .Connect and see
if the file can be found. If not, put up a dialog to let the user
specify the new back end. See e.g.
http://www.mvps.org/access/tables/tbl0009.htm

Another way is to iterate over TableDefs trying to access each table,
e.g.

Dim Dummy As Variant

On Error Resume Next
Dummy = CurrentDb.TableDefs(strTable).Fields(0).Name
If Err <> 0 Then
'Table can't be accessed: if it's a linked table, relink it
Else
'Table is OK
End If
On Error Goto 0

A web search for
"relink tables" access database tabledefs
will find more.

I'm doing some testing on what happens if a user starts my app and the path
to the back-end file is not valid.

[quoted text clipped - 14 lines]

Thank you.

 

[robert d via AccessMonster.com]

I have a custom logon form that puts together the shortcut to my app
(including workgroup security).

Is it a good idea to allow the user the ability to set the folder location to
the back-end file on this form. Just in case someone moves it, they could
find the new location and the path to the folder would then be shown on the
custom logon form.

So, if that is a good idea, then how do I get the path to the main app which
is to be opened. Do I have to open the main app first and then somehow read
the text box in the logon app that has this information. Or is there an
equivalent to OpenArgs for opening a database?

Thanks for advice

Thanks, John.

You were right. Somehow, the Recordsource of my StartAppForm got set to one
of the tables. It's supposed to be an unbound form. I do have code that
checks the links, but I kept getting these other errors before that code got
invoked.

I've got a few other problems now, but at least I'm getting into my code!

Thanks, again.

Hi Robert,

[quoted text clipped - 40 lines]

Please respond in the newgroup and not by email.

 

[John Nurick]

It's a matter of taste, I think. What I like to do is this:

1) In three records in a "Settings" table in the front end, store the
default location and the name of the back end file (e.g.
"\\SERVER\Share\", "My_BE.mdb"), and the name of a linked table.

2) In the startup form's Open or Load event:

-Retrieve these settings and try to access the linked table. If the
table can be accessed _and_ the path stored in its Connect property
matches the location and name retrieved from the Settings table, then
the front end is connected to its normal back end, and we are in a
normal launch.

-If the table can be accessed but the path in the Connect property does
not match that in the Settings, the front end is connected to _a_ back
end, but not the usual one (e.g. if it's been used for development work
on an local copy of the production back end. In this case, I do (e.g.)

strMsg = "Currently I'm not connected to the standard FIDO back
end " & vbCrLf _
& "(" & strDefaultBE & ") but to a different one, " &
vbCrLf _
& "(" & strActualBE & "). Do you want to continue " _
& "using the non-standard back end?"
Select Case MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Fido
data store")

If the user clicks Yes, the database launches using the current
(non-standard) back end. If No, then I call code to put up the Open File
dialog so the user can navigate to the correct back end, after which the
code relinks all the tables. And clicking Cancel aborts the launch and
closes Access.

-In the remaining case,when the linked table can't be accessed, I go
straight to the Open File dialog as above.

I have a custom logon form that puts together the shortcut to my app
(including workgroup security).

Is it a good idea to allow the user the ability to set the folder location to
the back-end file on this form. Just in case someone moves it, they could
find the new location and the path to the folder would then be shown on the
custom logon form.

So, if that is a good idea, then how do I get the path to the main app which
is to be opened. Do I have to open the main app first and then somehow read
the text box in the logon app that has this information. Or is there an
equivalent to OpenArgs for opening a database?

Thanks for advice

Thanks, John.

You were right. Somehow, the Recordsource of my StartAppForm got set to one
of the tables. It's supposed to be an unbound form. I do have code that
checks the links, but I kept getting these other errors before that code got
invoked.

I've got a few other problems now, but at least I'm getting into my code!

Thanks, again.

Hi Robert,

[quoted text clipped - 40 lines]

Please respond in the newgroup and not by email.

 

[Guest]

Id like to do this as well....unbound startup form that checks the default
directory for valid links, if not asks user for directory where all tables
are located.

Assume the following:
1. There are multiple backend mdb's (each containing some of the tables)
2. All the mdb's are always in the same directory.

The Dev A code seems to want to make the end user choose each mdb for each
table (eccchhh!)

Any assistance would be appreciated.

 

[John Nurick]

1) If all the backend mdbs are guaranteed to be in one folder, and if
their names don't change, you only need to test the first linked table
you come to.

If the link doesn't work, put up the Browse Folder dialog (there's code
on the Access Web site) to get the new location from the user. Then
update the Connect property of all the linked TableDefs: parse the file
name out of the existing Connect property and concatenate it to the
folder name you got from the user.

2) If the names of the back end mdb files are liable to change, or if
they are not guaranteed to be all in one folder, modify the Access Web
code so that each time you call the File Open dialog to get the name and
location of a backend mdb you iterate through collTbls looking for other
tables that were connected to the same backend as the one you just
updated, and updating their Connect properties.