Relink question

[BruceM]

I'm not sure this is where to post this question, but here it is anyhow.
Somebody who was working with me a while ago came up with the following code
to relink back end files. I don't know if it is adapted from the code at
the mvps web site, or what exactly. Tables may be in more than one BE
database. In particular, the Employee table is used by a number of
applications. I don't want to present the user with any options, as most
users wouldn't know what to do if presented with something like the Linked
Table Manager, or for that matter if asked whether they want to relink the
tables. Rather, I want the code to run invisibly on startup. The BE files
will not move, and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load event,
and have not had a problem with users not being able to get at the data.
Debug.Print lists the tables and the paths correctly. I wonder, though, if
I am leaving out something, as other code I have seen for relinking the BE
files seems to be rather more complex than this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" & strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[Graham Mandeno]

Hi Bruce

This code is not really doing anything useful at all. If the local table is
present, and the Connect string is valid, then the table is already linked.

For relinking code to be useful, it needs to be able to fix problems where
the tables are *not* already correctly linked. This means it must be able
to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not

 

[BruceM]

OK, thanks for the information. I have seen code, such as at the mvps web
site, to refresh table links, but message boxes asking the user if they want
to refresh the links, etc. are out of the question. The only thing I want
is for a message box to appear if there is a problem. I am still trying to
sort out how to use the code without message boxes, at least not routine
ones that appear every time the application starts. Users will simply click
through such messages. I probably would do so myself for messages that
appear every time.
Will the code I posted at least generate a message if there is a problem?
It seems to me it will. Perhaps an error could cause an otherwise invisible
command button to appear, and the mvps refresh links code could be run from
the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local table
is present, and the Connect string is valid, then the table is already
linked.

For relinking code to be useful, it needs to be able to fix problems where
the tables are *not* already correctly linked. This means it must be able
to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is anyhow.
Somebody who was working with me a while ago came up with the following
code to relink back end files. I don't know if it is adapted from the
code at the mvps web site, or what exactly. Tables may be in more than
one BE database. In particular, the Employee table is used by a number
of applications. I don't want to present the user with any options, as
most users wouldn't know what to do if presented with something like the
Linked Table Manager, or for that matter if asked whether they want to
relink the tables. Rather, I want the code to run invisibly on startup.
The BE files will not move, and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at the
data. Debug.Print lists the tables and the paths correctly. I wonder,
though, if I am leaving out something, as other code I have seen for
relinking the BE files seems to be rather more complex than this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" & strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[Graham Mandeno]

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the option
of doing nothing and asking for support, or attempting to find the correct
backend themselves.

One trap to watch for is that a user will often browse to the *frontend* and
choose that, instead of the backend. Of course, the frontend does contain
tables with all the correct names, so a link to these tables can be created,
but it will not work because it is circular! So always check, after a user
has selected a backend file, that it is not CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the mvps web
site, to refresh table links, but message boxes asking the user if they
want to refresh the links, etc. are out of the question. The only thing I
want is for a message box to appear if there is a problem. I am still
trying to sort out how to use the code without message boxes, at least not
routine ones that appear every time the application starts. Users will
simply click through such messages. I probably would do so myself for
messages that appear every time.
Will the code I posted at least generate a message if there is a problem?
It seems to me it will. Perhaps an error could cause an otherwise
invisible command button to appear, and the mvps refresh links code could
be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local table
is present, and the Connect string is valid, then the table is already
linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it must
be able to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is anyhow.
Somebody who was working with me a while ago came up with the following
code to relink back end files. I don't know if it is adapted from the
code at the mvps web site, or what exactly. Tables may be in more than
one BE database. In particular, the Employee table is used by a number
of applications. I don't want to present the user with any options, as
most users wouldn't know what to do if presented with something like the
Linked Table Manager, or for that matter if asked whether they want to
relink the tables. Rather, I want the code to run invisibly on startup.
The BE files will not move, and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at
the data. Debug.Print lists the tables and the paths correctly. I
wonder, though, if I am leaving out something, as other code I have seen
for relinking the BE files seems to be rather more complex than this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[BruceM]

Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before that
happens the user is presented with two message boxes, both with the safe
option (don't undo) highlighted. Several users have clicked through the
non-default choice on both message boxes without looking at either one, then
complained bitterly to me about how my database destroyed their work. Some
of these folks are brilliant in their fields, and make sound choices on
matters affecting the lives and safety of others, but I would not be
confident in their ability to relink a table. They need to call me or
somebody else in the know, if I can locate and train such a person.

I have discovered a version of the mvps code that a helper (computer science
major) devised a while ago. It uses fewer message boxes (for instance, it
doesn't ask if they want to refresh the table links, since presumably anyone
opening the database means to use the data). I think I could also either
present the users with more information about how to relink, or direct them
to further documentation on the network. With a split database they're not
likely to do any harm by relinking incorrectly, so maybe I worry more than I
need to.

Anyhow, thanks for the input.

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the
option of doing nothing and asking for support, or attempting to find the
correct backend themselves.

One trap to watch for is that a user will often browse to the *frontend*
and choose that, instead of the backend. Of course, the frontend does
contain tables with all the correct names, so a link to these tables can
be created, but it will not work because it is circular! So always check,
after a user has selected a backend file, that it is not CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the mvps
web site, to refresh table links, but message boxes asking the user if
they want to refresh the links, etc. are out of the question. The only
thing I want is for a message box to appear if there is a problem. I am
still trying to sort out how to use the code without message boxes, at
least not routine ones that appear every time the application starts.
Users will simply click through such messages. I probably would do so
myself for messages that appear every time.
Will the code I posted at least generate a message if there is a problem?
It seems to me it will. Perhaps an error could cause an otherwise
invisible command button to appear, and the mvps refresh links code could
be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it must
be able to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with the
following code to relink back end files. I don't know if it is adapted
from the code at the mvps web site, or what exactly. Tables may be in
more than one BE database. In particular, the Employee table is used
by a number of applications. I don't want to present the user with any
options, as most users wouldn't know what to do if presented with
something like the Linked Table Manager, or for that matter if asked
whether they want to relink the tables. Rather, I want the code to run
invisibly on startup. The BE files will not move, and I have used UNC
paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at
the data. Debug.Print lists the tables and the paths correctly. I
wonder, though, if I am leaving out something, as other code I have
seen for relinking the BE files seems to be rather more complex than
this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[Graham Mandeno]

FWIW, what I do is store the full path(s) to the backend(s) in an INI file.
when the database opens, it checks that the Connect strings for the linked
tables match the correct backend in the INI file. If not (as happens when a
new version of the frontend has just been installed) it silently relinks all
the tables.

Only if there is a problem with relinking do I display a message. For some
applications I check whether the user is an Admin and, if so, let them
browse for the correct backend to fix the problem. Otherwise the user just
gets a message to seek assistance.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before that
happens the user is presented with two message boxes, both with the safe
option (don't undo) highlighted. Several users have clicked through the
non-default choice on both message boxes without looking at either one,
then complained bitterly to me about how my database destroyed their work.
Some of these folks are brilliant in their fields, and make sound choices
on matters affecting the lives and safety of others, but I would not be
confident in their ability to relink a table. They need to call me or
somebody else in the know, if I can locate and train such a person.

I have discovered a version of the mvps code that a helper (computer
science major) devised a while ago. It uses fewer message boxes (for
instance, it doesn't ask if they want to refresh the table links, since
presumably anyone opening the database means to use the data). I think I
could also either present the users with more information about how to
relink, or direct them to further documentation on the network. With a
split database they're not likely to do any harm by relinking incorrectly,
so maybe I worry more than I need to.

Anyhow, thanks for the input.

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the
option of doing nothing and asking for support, or attempting to find the
correct backend themselves.

One trap to watch for is that a user will often browse to the *frontend*
and choose that, instead of the backend. Of course, the frontend does
contain tables with all the correct names, so a link to these tables can
be created, but it will not work because it is circular! So always
check, after a user has selected a backend file, that it is not
CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the mvps
web site, to refresh table links, but message boxes asking the user if
they want to refresh the links, etc. are out of the question. The only
thing I want is for a message box to appear if there is a problem. I am
still trying to sort out how to use the code without message boxes, at
least not routine ones that appear every time the application starts.
Users will simply click through such messages. I probably would do so
myself for messages that appear every time.
Will the code I posted at least generate a message if there is a
problem? It seems to me it will. Perhaps an error could cause an
otherwise invisible command button to appear, and the mvps refresh links
code could be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it
must be able to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with the
following code to relink back end files. I don't know if it is
adapted from the code at the mvps web site, or what exactly. Tables
may be in more than one BE database. In particular, the Employee
table is used by a number of applications. I don't want to present
the user with any options, as most users wouldn't know what to do if
presented with something like the Linked Table Manager, or for that
matter if asked whether they want to relink the tables. Rather, I
want the code to run invisibly on startup. The BE files will not move,
and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at
the data. Debug.Print lists the tables and the paths correctly. I
wonder, though, if I am leaving out something, as other code I have
seen for relinking the BE files seems to be rather more complex than
this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[BruceM]

Thanks for the feedback, but I have to admit I don't know exactly how to
store the path in an ini file. Actually, I may be able to figure that out,
but the problem for me is how to reference what was stored there, and how to
silently relinkd the tables if the Connect string does not match the ini
file. I suppose I would go back to the mvps code for relinking.

FWIW, what I do is store the full path(s) to the backend(s) in an INI
file. when the database opens, it checks that the Connect strings for the
linked tables match the correct backend in the INI file. If not (as
happens when a new version of the frontend has just been installed) it
silently relinks all the tables.

Only if there is a problem with relinking do I display a message. For
some applications I check whether the user is an Admin and, if so, let
them browse for the correct backend to fix the problem. Otherwise the
user just gets a message to seek assistance.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before
that happens the user is presented with two message boxes, both with the
safe option (don't undo) highlighted. Several users have clicked through
the non-default choice on both message boxes without looking at either
one, then complained bitterly to me about how my database destroyed their
work. Some of these folks are brilliant in their fields, and make sound
choices on matters affecting the lives and safety of others, but I would
not be confident in their ability to relink a table. They need to call
me or somebody else in the know, if I can locate and train such a person.

I have discovered a version of the mvps code that a helper (computer
science major) devised a while ago. It uses fewer message boxes (for
instance, it doesn't ask if they want to refresh the table links, since
presumably anyone opening the database means to use the data). I think I
could also either present the users with more information about how to
relink, or direct them to further documentation on the network. With a
split database they're not likely to do any harm by relinking
incorrectly, so maybe I worry more than I need to.

Anyhow, thanks for the input.

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is not
already correctly linked. I suppose you could then give the user the
option of doing nothing and asking for support, or attempting to find
the correct backend themselves.

One trap to watch for is that a user will often browse to the *frontend*
and choose that, instead of the backend. Of course, the frontend does
contain tables with all the correct names, so a link to these tables can
be created, but it will not work because it is circular! So always
check, after a user has selected a backend file, that it is not
CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the mvps
web site, to refresh table links, but message boxes asking the user if
they want to refresh the links, etc. are out of the question. The only
thing I want is for a message box to appear if there is a problem. I
am still trying to sort out how to use the code without message boxes,
at least not routine ones that appear every time the application
starts. Users will simply click through such messages. I probably
would do so myself for messages that appear every time.
Will the code I posted at least generate a message if there is a
problem? It seems to me it will. Perhaps an error could cause an
otherwise invisible command button to appear, and the mvps refresh
links code could be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it
must be able to:
1. read (from an INI file or somewhere) the correct location of the
backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct
backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with the
following code to relink back end files. I don't know if it is
adapted from the code at the mvps web site, or what exactly. Tables
may be in more than one BE database. In particular, the Employee
table is used by a number of applications. I don't want to present
the user with any options, as most users wouldn't know what to do if
presented with something like the Linked Table Manager, or for that
matter if asked whether they want to relink the tables. Rather, I
want the code to run invisibly on startup. The BE files will not
move, and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get at
the data. Debug.Print lists the tables and the paths correctly. I
wonder, though, if I am leaving out something, as other code I have
seen for relinking the BE files seems to be rather more complex than
this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[Graham Mandeno]

Hi Bruce

OK, here's a brief description of my method:

I have two tables in my frontend:

USysDataSources
=============
DataSourceID Numeric (primary key)
DataSourceName Text
DataSourceType Text
ConnectionParams Text
UserID Text
Password Text

This contains one record for each backend.
Only the first two fields are required for plain Access backends - the
others are for SQL Server, ODBC and other backends.

USysLinkedTables
=============
LocalTable Text (primary key)
DataSource Numeric (foreign key to DataSourceID)
SourceTableName Text (optional - if null then same as LocalTable)
Critical Yes/No (if true [default] then failure to relink causes
application to fail)

This contains one record for each linked table.

The frontend also contains a query named USysCompareLinkedTables:

SELECT D.DataSourceID, D.DataSourceName,
GetDataSource([DataSourceName],"",False,False) AS Filename,
T.LocalTable, T.Critical,
IIf(IsNull([SourceTableName]),[LocalTable],[SourceTableName]) AS
SourceTable,
S.Database, S.ForeignName, S.Type
FROM USysDataSources AS D
INNER JOIN (USysLinkedTables AS T
LEFT JOIN MSysObjects AS S ON T.LocalTable = S.Name)
ON D.DataSourceID = T.DataSource
WHERE (((S.Type) Is Null Or (S.Type)=6));

This lists, for each linked table, the name of the backend and source table
it is linked to, and the name of the backend and source table that it
*ought* to be linked to.

Note the function GetDataSource, which reads the "correct" backend file from
the INI file and checks that it exists, then returns its full path. It also
optionally offers to browse for the backend if it cannot be found, but this
is suppressed by the False arguments.

The startup code for the database opens a recordset with the following SQL:

Select * from USysCompareLinkedTables
where (Database Is Null) OR (Database<>Filename)
OR (ForeignName<>SourceTable);

This lists only the "broken" tables which require relinking. If there are
any, then another function is called to relink ALL the tables.


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the feedback, but I have to admit I don't know exactly how to
store the path in an ini file. Actually, I may be able to figure that
out, but the problem for me is how to reference what was stored there, and
how to silently relinkd the tables if the Connect string does not match
the ini file. I suppose I would go back to the mvps code for relinking.

FWIW, what I do is store the full path(s) to the backend(s) in an INI
file. when the database opens, it checks that the Connect strings for the
linked tables match the correct backend in the INI file. If not (as
happens when a new version of the frontend has just been installed) it
silently relinks all the tables.

Only if there is a problem with relinking do I display a message. For
some applications I check whether the user is an Admin and, if so, let
them browse for the correct backend to fix the problem. Otherwise the
user just gets a message to seek assistance.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before
that happens the user is presented with two message boxes, both with the
safe option (don't undo) highlighted. Several users have clicked
through the non-default choice on both message boxes without looking at
either one, then complained bitterly to me about how my database
destroyed their work. Some of these folks are brilliant in their fields,
and make sound choices on matters affecting the lives and safety of
others, but I would not be confident in their ability to relink a table.
They need to call me or somebody else in the know, if I can locate and
train such a person.

I have discovered a version of the mvps code that a helper (computer
science major) devised a while ago. It uses fewer message boxes (for
instance, it doesn't ask if they want to refresh the table links, since
presumably anyone opening the database means to use the data). I think
I could also either present the users with more information about how to
relink, or direct them to further documentation on the network. With a
split database they're not likely to do any harm by relinking
incorrectly, so maybe I worry more than I need to.

Anyhow, thanks for the input.

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is
not already correctly linked. I suppose you could then give the user
the option of doing nothing and asking for support, or attempting to
find the correct backend themselves.

One trap to watch for is that a user will often browse to the
*frontend* and choose that, instead of the backend. Of course, the
frontend does contain tables with all the correct names, so a link to
these tables can be created, but it will not work because it is
circular! So always check, after a user has selected a backend file,
that it is not CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the mvps
web site, to refresh table links, but message boxes asking the user if
they want to refresh the links, etc. are out of the question. The
only thing I want is for a message box to appear if there is a
problem. I am still trying to sort out how to use the code without
message boxes, at least not routine ones that appear every time the
application starts. Users will simply click through such messages. I
probably would do so myself for messages that appear every time.
Will the code I posted at least generate a message if there is a
problem? It seems to me it will. Perhaps an error could cause an
otherwise invisible command button to appear, and the mvps refresh
links code could be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it
must be able to:
1. read (from an INI file or somewhere) the correct location of
the backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct
backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with
the following code to relink back end files. I don't know if it is
adapted from the code at the mvps web site, or what exactly. Tables
may be in more than one BE database. In particular, the Employee
table is used by a number of applications. I don't want to present
the user with any options, as most users wouldn't know what to do if
presented with something like the Linked Table Manager, or for that
matter if asked whether they want to relink the tables. Rather, I
want the code to run invisibly on startup. The BE files will not
move, and I have used UNC paths to the BE files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get
at the data. Debug.Print lists the tables and the paths correctly.
I wonder, though, if I am leaving out something, as other code I
have seen for relinking the BE files seems to be rather more complex
than this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[BruceM]

Thanks again, Graham.. It will be some time before I can sort this out. In
particular it seems I need to use something like GetPrivateProfileString to
read from the ini file, and I need to get on with some other things for now,
so that investigation will have to wait. I think I'll look for a way to
relink all tables every time and leave it at that for now. When I have the
time I will study your suggestions further and begin to experiment. Reading
from an ini file (or other text file, I suppose) could be a useful thing to
know, so I expect the study will have benefits beyond the immediate
question. Also, I am starting to get some understanding about how the
system tables (MSysObject, in this case) can be put to good use beyond their
behind-the-scenes activity.
Again, thanks for the time and care you have put into your detailed
responses.

Hi Bruce

OK, here's a brief description of my method:

I have two tables in my frontend:

USysDataSources
=============
DataSourceID Numeric (primary key)
DataSourceName Text
DataSourceType Text
ConnectionParams Text
UserID Text
Password Text

This contains one record for each backend.
Only the first two fields are required for plain Access backends - the
others are for SQL Server, ODBC and other backends.

USysLinkedTables
=============
LocalTable Text (primary key)
DataSource Numeric (foreign key to DataSourceID)
SourceTableName Text (optional - if null then same as LocalTable)
Critical Yes/No (if true [default] then failure to relink
causes application to fail)

This contains one record for each linked table.

The frontend also contains a query named USysCompareLinkedTables:

SELECT D.DataSourceID, D.DataSourceName,
GetDataSource([DataSourceName],"",False,False) AS Filename,
T.LocalTable, T.Critical,
IIf(IsNull([SourceTableName]),[LocalTable],[SourceTableName]) AS
SourceTable,
S.Database, S.ForeignName, S.Type
FROM USysDataSources AS D
INNER JOIN (USysLinkedTables AS T
LEFT JOIN MSysObjects AS S ON T.LocalTable = S.Name)
ON D.DataSourceID = T.DataSource
WHERE (((S.Type) Is Null Or (S.Type)=6));

This lists, for each linked table, the name of the backend and source
table it is linked to, and the name of the backend and source table that
it *ought* to be linked to.

Note the function GetDataSource, which reads the "correct" backend file
from the INI file and checks that it exists, then returns its full path.
It also optionally offers to browse for the backend if it cannot be found,
but this is suppressed by the False arguments.

The startup code for the database opens a recordset with the following
SQL:

Select * from USysCompareLinkedTables
where (Database Is Null) OR (Database<>Filename)
OR (ForeignName<>SourceTable);

This lists only the "broken" tables which require relinking. If there are
any, then another function is called to relink ALL the tables.


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the feedback, but I have to admit I don't know exactly how to
store the path in an ini file. Actually, I may be able to figure that
out, but the problem for me is how to reference what was stored there,
and how to silently relinkd the tables if the Connect string does not
match the ini file. I suppose I would go back to the mvps code for
relinking.

FWIW, what I do is store the full path(s) to the backend(s) in an INI
file. when the database opens, it checks that the Connect strings for
the linked tables match the correct backend in the INI file. If not (as
happens when a new version of the frontend has just been installed) it
silently relinks all the tables.

Only if there is a problem with relinking do I display a message. For
some applications I check whether the user is an Admin and, if so, let
them browse for the correct backend to fix the problem. Otherwise the
user just gets a message to seek assistance.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks again. This is a bit of a digression, but here it is anyhow: I
built a database in which the Before Update event validates that all
required fields are filled in. If not, the record is undone. Before
that happens the user is presented with two message boxes, both with
the safe option (don't undo) highlighted. Several users have clicked
through the non-default choice on both message boxes without looking at
either one, then complained bitterly to me about how my database
destroyed their work. Some of these folks are brilliant in their
fields, and make sound choices on matters affecting the lives and
safety of others, but I would not be confident in their ability to
relink a table. They need to call me or somebody else in the know, if I
can locate and train such a person.

I have discovered a version of the mvps code that a helper (computer
science major) devised a while ago. It uses fewer message boxes (for
instance, it doesn't ask if they want to refresh the table links, since
presumably anyone opening the database means to use the data). I think
I could also either present the users with more information about how
to relink, or direct them to further documentation on the network.
With a split database they're not likely to do any harm by relinking
incorrectly, so maybe I worry more than I need to.

Anyhow, thanks for the input.

Hi Bruce

Yes, the code you posted will raise an error if one of the tables is
not already correctly linked. I suppose you could then give the user
the option of doing nothing and asking for support, or attempting to
find the correct backend themselves.

One trap to watch for is that a user will often browse to the
*frontend* and choose that, instead of the backend. Of course, the
frontend does contain tables with all the correct names, so a link to
these tables can be created, but it will not work because it is
circular! So always check, after a user has selected a backend file,
that it is not CurrentDb.Name.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

OK, thanks for the information. I have seen code, such as at the
mvps web site, to refresh table links, but message boxes asking the
user if they want to refresh the links, etc. are out of the question.
The only thing I want is for a message box to appear if there is a
problem. I am still trying to sort out how to use the code without
message boxes, at least not routine ones that appear every time the
application starts. Users will simply click through such messages. I
probably would do so myself for messages that appear every time.
Will the code I posted at least generate a message if there is a
problem? It seems to me it will. Perhaps an error could cause an
otherwise invisible command button to appear, and the mvps refresh
links code could be run from the command button as needed.

Hi Bruce

This code is not really doing anything useful at all. If the local
table is present, and the Connect string is valid, then the table is
already linked.

For relinking code to be useful, it needs to be able to fix problems
where the tables are *not* already correctly linked. This means it
must be able to:
1. read (from an INI file or somewhere) the correct location of
the backend database(s)
2. browse for the backend database(s) if they don't exist
3. check that the local tables are connected to the correct
backends
4. relink them if they are not
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm not sure this is where to post this question, but here it is
anyhow. Somebody who was working with me a while ago came up with
the following code to relink back end files. I don't know if it is
adapted from the code at the mvps web site, or what exactly.
Tables may be in more than one BE database. In particular, the
Employee table is used by a number of applications. I don't want
to present the user with any options, as most users wouldn't know
what to do if presented with something like the Linked Table
Manager, or for that matter if asked whether they want to relink
the tables. Rather, I want the code to run invisibly on startup.
The BE files will not move, and I have used UNC paths to the BE
files.

I have used the code below, which I call in the startup form's Load
event, and have not had a problem with users not being able to get
at the data. Debug.Print lists the tables and the paths correctly.
I wonder, though, if I am leaving out something, as other code I
have seen for relinking the BE files seems to be rather more
complex than this.

Public Function RelinkBE() As Boolean

On Error GoTo ProcError

Dim tdf As TableDef
Dim strMsg As String, strCall As String

strCall = vbCrLf & "Contact tech support."

RelinkBE = True

For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Name & " " & tdf.Connect
tdf.RefreshLink
End If
Next tdf
Set tdf = Nothing

ProcExit:
Exit Function

ProcError:
Select Case Err.Number
Case 3011 'Bad Table Name
strMsg = "Table Not Found." & strCall
Case 3024 'File Name Not Found
strMsg = "Database File Name Not Found." & strCall
Case 3044 'Path Not found
strMsg = "Database Path Not Found." & strCall
Case Else
strMsg = "Error " & Err.Number & " (" & Err.Description & _
") in function ReLinkBE of Module mdlRelink" &
strCall
End Select

MsgBox strMsg, vbExclamation, "Call Support"
RelinkBE = False

Resume ProcExit

End Function

 

[Tony Toews [MVP]]

FWIW, what I do is store the full path(s) to the backend(s) in an INI file.
when the database opens, it checks that the Connect strings for the linked
tables match the correct backend in the INI file. If not (as happens when a
new version of the frontend has just been installed) it silently relinks all
the tables.

Only if there is a problem with relinking do I display a message.

I do the same.

For some
applications I check whether the user is an Admin and, if so, let them
browse for the correct backend to fix the problem. Otherwise the user just
gets a message to seek assistance.

Now that I've never bothered to do. Why can't the user just ask for
instructions over the phone on relinking to the appropriate BE?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

Thanks again, Graham.. It will be some time before I can sort this out. In
particular it seems I need to use something like GetPrivateProfileString to
read from the ini file,

And the equivalent to write to the INI file once you've found the
correct BE.

Or you could use the registry. I initially chose to use INI files
because I figured that it would be easier for the users if they ever
had to manually update the file. But in over ten years I don't think
I've ever had the need.

I think I'll look for a way to
relink all tables every time and leave it at that for now.

Note that for best performance you should do either one of the
following:

1) Open a database variable against the backend
or
2) Once you've relinked the first table open a recordset variable
against that table and continue relinking all the other tables.

This will make a huge difference in performance when someone else is
already in the FE linked to the BE in question.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[BruceM]

Thanks for the reply. When you say I should open a database variable
against the backend, would that be something like this?

Dim db as DAO.Database
Set db = CurrentDatabase()

or for the recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblMain", dbOpenDynaset)
(where tblMain is the first linked table)

If so, in either case would this be in the relinking code, and would I close
the recordset at the end of the relinking code? If not, where have I missed
the boat?

I sort of get what you are saying (although I have to say I don't understand
the "why"), but not enough to implement it with any confidence.

 

[Graham Mandeno]

Hi Tony

Now that I've never bothered to do. Why can't the user just ask for
instructions over the phone on relinking to the appropriate BE?

It depends, Tony. Note I said "For *some* applications".

Some sites have many users, a few of whom can be trusted to make intelligent
decisions on their own about where the correct backend might be, but most of
whom cannot. In these cases the message might say "Ask <your local support
person> to remedy this problem". Other installations might say "Browse and
select the file containing your back-end data for <name of datasource>".
Still others might say "Hands in the air! Back away from the keyboard!
Phone Graham NOW!"

<g>

 

[Graham Mandeno]

Hi Bruce

What Tony means is that when you create a linked table, part of the process
involves the file system creating a reference to the backend database. If
there is not one already from the current process, then the file will need
to be opened, a lengthy process which, for an MDB, also involves creating an
LDB file. Once the table is linked, the reference is dropped. When the
number of file system references to the backend database drops to zero, the
file closes (and the LDB is deleted).

All this happens in a single line of code:
<TableDef>.RefreshLink
or
<Database>.TableDefs.Append <TableDef>

If you are linking many tables in the same backend, then it is much faster
to open the backend database first, so that it already has a reference and
each table link will not cause a file Open/Close with all the other
associated overhead.

So, to clarify, the procedure is as follows:

1. Ascertain the path to the backend
2. Open the backend:
Dim dbBE as Database
Set dbBE = DBEngine(0).OpenDatabase(<path to backend>)
3. Link all the tables in that backend, one by one
4. Close the backend:
dbBE.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Hi Tony (and Bruce)

Or you could use the registry. I initially chose to use INI files...

As we've discussed in another forum, I would definitely NOT use the
Registry. This is because the wonderful world of "virtualisation" in
Windows Vista (aka "smoke and mirrors") screws about with anything you write
to a known shared location (including Windows folders and the Registry)
unless you are running with elevated administrator privileges.

For this reason, neither your frontend nor your INI file should be in any of
these "known shared" folders (or their subfolders). These include:
C:\Windows
C:\Program Files
C:\ProgramData (in XP this was
C:\Documents and Settings\All Users\AppData)

Bruce, here is the code I use to read/write INI files:

=========== start code ==================
Private Declare Function GetPrivateProfileSection _
Lib "kernel32" _
Alias "GetPrivateProfileSectionA" ( _
ByVal lpAppName As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long

Private Declare Function WritePrivateProfileString _
Lib "kernel32" _
Alias "WritePrivateProfileStringA" ( _
ByVal lpSectionName As String, _
ByVal lpKeyName As Any, _
ByVal lpString As Any, _
ByVal lpFileName As String _
) As Long

Public Function GetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
Optional sDefault As String) _
As String
Const cBufSize = 1024
Dim sBuffer As String, lRetLen As Long
sBuffer = String(cBufSize, vbNullChar)
lRetLen = GetPrivateProfileString(sSection, sKey, _
sDefault, sBuffer, cBufSize, sIniFile)
GetIniValue = Left(sBuffer, lRetLen)
End Function

Public Function SetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
sValue As String) _
As Long
SetIniValue = WritePrivateProfileString(sSection, _
sKey, sValue, sIniFile)
End Function
=========== end code ==================


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[BruceM]

I'll give that a try when I have a chance to get back to it, which may not
be tomorrow. Two more questions for now: Is there any need to made the
dbBE variable a DAO.Database? And is the path to the back end a path first
to the Access executable, followed by a switch that includes the path to the
BE (like with decompile, etc.), or is it just a direct path to the BE file?

BTW, I don't think I have a project so far with more than maybe 20 tables in
a single BE database.

Hi Bruce

What Tony means is that when you create a linked table, part of the
process involves the file system creating a reference to the backend
database. If there is not one already from the current process, then the
file will need to be opened, a lengthy process which, for an MDB, also
involves creating an LDB file. Once the table is linked, the reference is
dropped. When the number of file system references to the backend database
drops to zero, the file closes (and the LDB is deleted).

All this happens in a single line of code:
<TableDef>.RefreshLink
or
<Database>.TableDefs.Append <TableDef>

If you are linking many tables in the same backend, then it is much faster
to open the backend database first, so that it already has a reference and
each table link will not cause a file Open/Close with all the other
associated overhead.

So, to clarify, the procedure is as follows:

1. Ascertain the path to the backend
2. Open the backend:
Dim dbBE as Database
Set dbBE = DBEngine(0).OpenDatabase(<path to backend>)
3. Link all the tables in that backend, one by one
4. Close the backend:
dbBE.Close

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for the reply. When you say I should open a database variable
against the backend, would that be something like this?

Dim db as DAO.Database
Set db = CurrentDatabase()

or for the recordset:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblMain", dbOpenDynaset)
(where tblMain is the first linked table)

If so, in either case would this be in the relinking code, and would I
close the recordset at the end of the relinking code? If not, where have
I missed the boat?

I sort of get what you are saying (although I have to say I don't
understand the "why"), but not enough to implement it with any
confidence.

 

[BruceM]

OK, I think that makes it clearer. At least I can begin to understand how
the values get to the GetPrivateProfileSection and WritePrivateProfileString
functions (by using the public functions, which would be called from
somewhere in the code, I expect). I'm still working on understanding ByVal,
but I think I'm starting to get a handle on this stuff. Thanks again. Now
I will study and experiment some more.

Hi Tony (and Bruce)

Or you could use the registry. I initially chose to use INI files...

As we've discussed in another forum, I would definitely NOT use the
Registry. This is because the wonderful world of "virtualisation" in
Windows Vista (aka "smoke and mirrors") screws about with anything you
write to a known shared location (including Windows folders and the
Registry) unless you are running with elevated administrator privileges.

For this reason, neither your frontend nor your INI file should be in any
of these "known shared" folders (or their subfolders). These include:
C:\Windows
C:\Program Files
C:\ProgramData (in XP this was
C:\Documents and Settings\All Users\AppData)

Bruce, here is the code I use to read/write INI files:

=========== start code ==================
Private Declare Function GetPrivateProfileSection _
Lib "kernel32" _
Alias "GetPrivateProfileSectionA" ( _
ByVal lpAppName As String, _
ByVal lpReturnedString As String, _
ByVal nSize As Long, _
ByVal lpFileName As String _
) As Long

Private Declare Function WritePrivateProfileString _
Lib "kernel32" _
Alias "WritePrivateProfileStringA" ( _
ByVal lpSectionName As String, _
ByVal lpKeyName As Any, _
ByVal lpString As Any, _
ByVal lpFileName As String _
) As Long

Public Function GetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
Optional sDefault As String) _
As String
Const cBufSize = 1024
Dim sBuffer As String, lRetLen As Long
sBuffer = String(cBufSize, vbNullChar)
lRetLen = GetPrivateProfileString(sSection, sKey, _
sDefault, sBuffer, cBufSize, sIniFile)
GetIniValue = Left(sBuffer, lRetLen)
End Function

Public Function SetIniValue( _
sIniFile As String, _
sSection As String, _
sKey As String, _
sValue As String) _
As Long
SetIniValue = WritePrivateProfileString(sSection, _
sKey, sValue, sIniFile)
End Function
=========== end code ==================


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

And the equivalent to write to the INI file once you've found the
correct BE.

Or you could use the registry. I initially chose to use INI files
because I figured that it would be easier for the users if they ever
had to manually update the file. But in over ten years I don't think
I've ever had the need.


Note that for best performance you should do either one of the
following:

1) Open a database variable against the backend
or
2) Once you've relinked the first table open a recordset variable
against that table and continue relinking all the other tables.

This will make a huge difference in performance when someone else is
already in the FE linked to the BE in question.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Graham Mandeno]

Hi Bruce

Yes, you should Dim dbBE as DAO.Database. (It could be declared as Object,
but every database should have a reference to DAO anyway, so early binding
is faster and gives better compile-time checking.) In any case, it will
need to be declared.

BTW, EVERY variable you use should be declared. using implicit declaration
is asking for trouble. EVERY module, including Form and Report modules
should have Option Explicit at the top.

The path to the backend is just that - the full file path - for example:
"\\MyServer\SomeShare\My App Folder\Back-end file.mdb"

BTW, there is no law that says an Access database must have the extension
"MDB". All my backends are .ABE files (Access Back-End). It at least
discourages users from double-clicking on them to open them!

 

[BruceM]

I declare every variable. I have chosen Option Explicit as the default
option, so Access won't let me implicitly declare. It has been a great help
with debugging.

I didn't know that about using an extension other that mdb for BE files.
ABE is not a registered file type on my machine, so I suppose that means I
would need to add it. I use user-level security for my split databases
(which is all of my newer ones), so a user who attempts to open the file,
either FE or BE, by double clicking the icon will receive a message about
insufficient permissions. The only way in is through a shortcut that
temporarily joins the user to the secure workgroup file.

Thanks again for the information. This is an area of Access with which I
have had little experience, due in large part to a lack of information. I
have lots of information now, so I can work on gaining experience.

Hi Bruce

Yes, you should Dim dbBE as DAO.Database. (It could be declared as
Object, but every database should have a reference to DAO anyway, so early
binding is faster and gives better compile-time checking.) In any case,
it will need to be declared.

BTW, EVERY variable you use should be declared. using implicit
declaration is asking for trouble. EVERY module, including Form and
Report modules should have Option Explicit at the top.

The path to the backend is just that - the full file path - for example:
"\\MyServer\SomeShare\My App Folder\Back-end file.mdb"

BTW, there is no law that says an Access database must have the extension
"MDB". All my backends are .ABE files (Access Back-End). It at least
discourages users from double-clicking on them to open them!


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'll give that a try when I have a chance to get back to it, which may
not be tomorrow. Two more questions for now: Is there any need to made
the dbBE variable a DAO.Database? And is the path to the back end a path
first to the Access executable, followed by a switch that includes the
path to the BE (like with decompile, etc.), or is it just a direct path
to the BE file?

BTW, I don't think I have a project so far with more than maybe 20 tables
in a single BE database.

 

[Douglas J. Steele]

I believe that Graham's point is that by using ABE which isn't registered,
double-clicking on the back-end file means it won't automatically open:
instead, you'll get a message from Windows that it doesn't know what to do
with the file.

You could set ABE files to automatically open the files in Access, but
there's really no need to.

Once you open Access, you can choose File | Open, change Files of type to
All Files (*.*) and navigate to open the .ABE file that way.