Relink question

[BruceM]

OK, I think I get it. The only program that needs to "see" the BE files is
the FE, which will be able to get at the data no matter the file extension.
Thanks.

 

[Graham Mandeno]

Precisely!

--
Cheers
Graham M

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.

 

[Graham Mandeno]

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.

 

[BruceM]

I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users group
has no permissions anywhere. I think that means some variant of the code in
14.1 of the FAQ would work (which seems to be the same general idea as the
code at the mvps web site, I think).

I have to say I marvel anew at the Security FAQ each time I see it. It is
an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.

Since I do not have RWOP queries I think I can use RefreshLink. The FAQ
states:
"The best way to implement this [it is not clear in context to what "this"
refers; maybe it means restricting access to the data, or maybe RWOP] is to
remove ALL permissions from the underlying table(s) and use only queries to
get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to the
tables in the destination database. It would help if I knew whether the
destination database is the link's destination (i.e. the BE) or if
"destination" means something else.

Anyhow, I have not found a need for RWOP queries, so I think I'm OK with
RefreshLink. If I have a problem that could be solved through the use of
RWOP queries I have not been able to identify that a RWOP query is what I
need. I think Joan Wild has some more information about this on her web
site. Yet another thing to sort out.

I do appreciate your pointing this out. I have to say, though, that it will
take a lot of development time I do not have right now to sort out how to
read the correct path from an ini file or somewhere, check the existing link
against that, and refresh the link if need be. For now the users will get
an error message, and I will fix it. I wish there was another way, but a
better solution will have to wait.

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.

 

[Graham Mandeno]

Hi Bruce

The method I use (and would recommend) is the one in 14.3, because it
*always* works, no matter what the permissions on the backend tables.

Note that allowing all permissions on the linked tables in the frontend does
not grant permissions to the *data*. It just allows the user to delete and
relink the tables.

Yes, you're right, user-level security can seem like an "impenetrable
thicket", but if properly implemented it works well and is sufficiently
secure for most applications. I am one among many who are very disappointed
that it has been canned in A2007.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users group
has no permissions anywhere. I think that means some variant of the code
in 14.1 of the FAQ would work (which seems to be the same general idea as
the code at the mvps web site, I think).

I have to say I marvel anew at the Security FAQ each time I see it. It is
an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.

Since I do not have RWOP queries I think I can use RefreshLink. The FAQ
states:
"The best way to implement this [it is not clear in context to what "this"
refers; maybe it means restricting access to the data, or maybe RWOP] is
to remove ALL permissions from the underlying table(s) and use only
queries to get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to the
tables in the destination database. It would help if I knew whether the
destination database is the link's destination (i.e. the BE) or if
"destination" means something else.

Anyhow, I have not found a need for RWOP queries, so I think I'm OK with
RefreshLink. If I have a problem that could be solved through the use of
RWOP queries I have not been able to identify that a RWOP query is what I
need. I think Joan Wild has some more information about this on her web
site. Yet another thing to sort out.

I do appreciate your pointing this out. I have to say, though, that it
will take a lot of development time I do not have right now to sort out
how to read the correct path from an ini file or somewhere, check the
existing link against that, and refresh the link if need be. For now the
users will get an error message, and I will fix it. I wish there was
another way, but a better solution will have to wait.

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.

 

[Tony Toews [MVP]]

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)

Indeed. I'm now installing the FEs and related files to the users
%App Data% along with the demo backend MDB and related jpg files. The
empty BE goes installed to the users My Documents folder. Actually
in both cases a sub folder named Granite Fleet Manager as appropriate
for my app.

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]]

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!"

<chuckle> Fair enough.

One of these years I want to write a TCP/IP multi cast host module.
It's only job would be to respond to client apps, such as my FE and
tell the FE the network path to use. Thus the IT person could run
this host module and tell the user to install the FE. (The FE
installs without requiring administrator privileges so long as a
version of Access is already on the system.) Then when the user
runs the FE it first does a TCP/IP multi-cast to see if a host exists.
if it does it then interrogates the host looking for the path to the
backend on the network server. Then host responds and the client FE
relinks automatically. Thus the only thing the user needs to do is to
click on a file or URL to install the FE.

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]

It's not ULS that's the impenetrable thicket, it's the documentation. ULS
is a bit difficult to follow at first (Jack MacDonald's paper of the subject
was the one that finally led me to understand what is going on), but the FAQ
is helpful only after the subject has been learned elsewhere. I still think
they could have updated the FAQ at least to include Access 2003 in the
title.

I think I have found enough clues in the documentation to deduce that the
destination database is the FE and the source database is the BE. Section
14.3 states that the Connect property can be used as long as there are "full
permissions in the destination (FE) database and Open/Run permissions on the
source database (BE) - no permissions at all are necessary on the source
tables." Assuming this literally referes to database permissions rather
permissions to any tables, queries, etc., it either says that there are no
security implications to granting all users full permissions to the FE
database, or that I would need to use the code in the faq_SetPermissions
function to change permissions temporarily. However, the documentation for
that function "assumes that a member of the Admins group will be executing
the code". I'm not sure why it assumes that, since the group name
apparentlly is passed to the function, but assuming it is necessary to pass
the Admins group name, either the code can be run only by members of the
Admins group or everybody needs to be a member of the group. If membership
in the Admins group is necessary to run the code either everybody needs to
be a member of the Admins group (which pretty much wipes out security), or
only a few users can relink tables. Or maybe the faq_SetPermissions
function is not necessary. In any case, it seems that using the Connect
property is possible

I could be mistaken about the meaning of "source" and "destination". The
source could be the origin of data (FE) that flows to the destination (BE),
or the source could be the collection of data (BE) that flows to the
destination where it will be seen and used (the FE). Figurative language
should be defined. Maybe I'm supposed to know the meaning of the two terms,
but I have not found a way to frame the search question. If I had to bet I
would say the source is the BE.

For now users will get an error message if the table link is not
successfully refreshed, and I will fix it. This has not happened, so maybe
I will continue to be lucky for a while longer. Beyond that maybe I can use
the code at the mvps web site (RefreshLink), modified to remove some or all
of the prompts. Maybe I can combine it with an ini file that lists the
path.

Beyond that I don't have a prayer, at least not until I have a few more
years experience behind me. I am extremely frustrated by my inability to
sort this out, but at least I am old enough that I know when to admit I am
beaten. Thanks for trying to help. I really do appreciate the time you
have put into your responses.

Hi Bruce

The method I use (and would recommend) is the one in 14.3, because it
*always* works, no matter what the permissions on the backend tables.

Note that allowing all permissions on the linked tables in the frontend
does not grant permissions to the *data*. It just allows the user to
delete and relink the tables.

Yes, you're right, user-level security can seem like an "impenetrable
thicket", but if properly implemented it works well and is sufficiently
secure for most applications. I am one among many who are very
disappointed that it has been canned in A2007.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users group
has no permissions anywhere. I think that means some variant of the code
in 14.1 of the FAQ would work (which seems to be the same general idea as
the code at the mvps web site, I think).

I have to say I marvel anew at the Security FAQ each time I see it. It
is an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.

Since I do not have RWOP queries I think I can use RefreshLink. The FAQ
states:
"The best way to implement this [it is not clear in context to what
"this" refers; maybe it means restricting access to the data, or maybe
RWOP] is to remove ALL permissions from the underlying table(s) and use
only queries to get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to
the tables in the destination database. It would help if I knew whether
the destination database is the link's destination (i.e. the BE) or if
"destination" means something else.

Anyhow, I have not found a need for RWOP queries, so I think I'm OK with
RefreshLink. If I have a problem that could be solved through the use of
RWOP queries I have not been able to identify that a RWOP query is what I
need. I think Joan Wild has some more information about this on her web
site. Yet another thing to sort out.

I do appreciate your pointing this out. I have to say, though, that it
will take a lot of development time I do not have right now to sort out
how to read the correct path from an ini file or somewhere, check the
existing link against that, and refresh the link if need be. For now the
users will get an error message, and I will fix it. I wish there was
another way, but a better solution will have to wait.

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.

 

[Graham Mandeno]

Hi Bruce

You're correct about "source" and "destination". Source is where you are
linking *from* (backend) and destination is where you are linking *to*
(frontend).

You're also correct about the FAQ being a difficult document to grasp. In
its defence, it covers a lot of information about a very complex area, but
it was also written by quite a number of people over a number of years and
versions, and it would definitely benefit from a rewrite. For a start, I
doubt anyone is interested in the Access 2.0 bits any more! Unfortunately,
since ULS is the baby that MS has thrown out with the bathwater, putting
resources into a rewrite is unlikely :-(

Basically, any user who may relink tables (and for me this means any user
who may open the frontend) should have all permissions to <New
tables/queries> in the frontend (see section 16.3). Then, provided they
have Open/Run permissions on the backend (which they would need to read the
data anyway), they can link tables using the delete-and-recreate-TableDef
method (section 14.3).

Sorry if you already told me, but how did you get on with reading/writing
the INI file?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

It's not ULS that's the impenetrable thicket, it's the documentation. ULS
is a bit difficult to follow at first (Jack MacDonald's paper of the
subject was the one that finally led me to understand what is going on),
but the FAQ is helpful only after the subject has been learned elsewhere.
I still think they could have updated the FAQ at least to include Access
2003 in the title.

I think I have found enough clues in the documentation to deduce that the
destination database is the FE and the source database is the BE. Section
14.3 states that the Connect property can be used as long as there are
"full permissions in the destination (FE) database and Open/Run
permissions on the source database (BE) - no permissions at all are
necessary on the source tables." Assuming this literally referes to
database permissions rather permissions to any tables, queries, etc., it
either says that there are no security implications to granting all users
full permissions to the FE database, or that I would need to use the code
in the faq_SetPermissions function to change permissions temporarily.
However, the documentation for that function "assumes that a member of the
Admins group will be executing the code". I'm not sure why it assumes
that, since the group name apparentlly is passed to the function, but
assuming it is necessary to pass the Admins group name, either the code
can be run only by members of the Admins group or everybody needs to be a
member of the group. If membership in the Admins group is necessary to
run the code either everybody needs to be a member of the Admins group
(which pretty much wipes out security), or only a few users can relink
tables. Or maybe the faq_SetPermissions function is not necessary. In any
case, it seems that using the Connect property is possible

I could be mistaken about the meaning of "source" and "destination". The
source could be the origin of data (FE) that flows to the destination
(BE), or the source could be the collection of data (BE) that flows to the
destination where it will be seen and used (the FE). Figurative language
should be defined. Maybe I'm supposed to know the meaning of the two
terms, but I have not found a way to frame the search question. If I had
to bet I would say the source is the BE.

For now users will get an error message if the table link is not
successfully refreshed, and I will fix it. This has not happened, so
maybe I will continue to be lucky for a while longer. Beyond that maybe I
can use the code at the mvps web site (RefreshLink), modified to remove
some or all of the prompts. Maybe I can combine it with an ini file that
lists the path.

Beyond that I don't have a prayer, at least not until I have a few more
years experience behind me. I am extremely frustrated by my inability to
sort this out, but at least I am old enough that I know when to admit I am
beaten. Thanks for trying to help. I really do appreciate the time you
have put into your responses.

Hi Bruce

The method I use (and would recommend) is the one in 14.3, because it
*always* works, no matter what the permissions on the backend tables.

Note that allowing all permissions on the linked tables in the frontend
does not grant permissions to the *data*. It just allows the user to
delete and relink the tables.

Yes, you're right, user-level security can seem like an "impenetrable
thicket", but if properly implemented it works well and is sufficiently
secure for most applications. I am one among many who are very
disappointed that it has been canned in A2007.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users group
has no permissions anywhere. I think that means some variant of the code
in 14.1 of the FAQ would work (which seems to be the same general idea as
the code at the mvps web site, I think).

I have to say I marvel anew at the Security FAQ each time I see it. It
is an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.

Since I do not have RWOP queries I think I can use RefreshLink. The FAQ
states:
"The best way to implement this [it is not clear in context to what
"this" refers; maybe it means restricting access to the data, or maybe
RWOP] is to remove ALL permissions from the underlying table(s) and use
only queries to get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to
the tables in the destination database. It would help if I knew whether
the destination database is the link's destination (i.e. the BE) or if
"destination" means something else.

Anyhow, I have not found a need for RWOP queries, so I think I'm OK with
RefreshLink. If I have a problem that could be solved through the use
of RWOP queries I have not been able to identify that a RWOP query is
what I need. I think Joan Wild has some more information about this on
her web site. Yet another thing to sort out.

I do appreciate your pointing this out. I have to say, though, that it
will take a lot of development time I do not have right now to sort out
how to read the correct path from an ini file or somewhere, check the
existing link against that, and refresh the link if need be. For now
the users will get an error message, and I will fix it. I wish there
was another way, but a better solution will have to wait.

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.

 

[BruceM]

Thanks again for your interest and your reply. OK, so the full permissions
are for New tables/queries in the FE. I have been assigning full
permissions to all FE tables for all users (except the Users group), and
restricting permissions in the BE. It took me a while to sort out that the
most restrictive permissions apply in this case, but the least restrictive
group permissions apply if a user is a member of several groups. Anyhow, it
sounds as if I have already addressed the permissions issue that is needed
to apply the Connect property as described in 14.3. That was the part I
couldn't sort out.

I have not had much of a chance to look at Access 2007 security. I have
Office 2007 installed at home, but am still trying to sort out the ribbons
and whatnot. I hope it is still possible to administer permissions and
logins in such a way that Access "knows" who is working on the database. I
have several projects in which the user "signs" a record by clicking a
button that inserts the CurrentUser. I have set it up so that each person's
Windows User Name is also their ULS name. The security logon shows this
name, and the user supplies the password (which is chosen separately from
the Windows password; the user can select the same password, but the Windows
one has to change from time to time). I hope this general technique can
still be used in Access 2007. There's not much chance that my company is
going to upgrade to Office 2007 any time soon, but eventually I will need to
address these issues. I think ULS is quite adequate for security on
databases used locally (excepting any that include personal and financial
information, etc., but these are purchased software packages, not homegrown
projects), so like you I am a bit unhappy to see it discontinued.

As for the ini file reading/writing, I have fallen a bit behind in my
day-to-day tasks as a result of developing my latest project (which I'm glad
to say is up and running, and working just as I had hoped), plus I need to
get a jump on some year-end stuff, so I can't devote much more time now to
sorting out the ini file techniques. However, I have saved the post in my
personal collection of Help documents, and hope to return to it in the next
few weeks. I think it will be very useful, both for relinking and in
general, so I most definitely want to study it further.

Hi Bruce

You're correct about "source" and "destination". Source is where you are
linking *from* (backend) and destination is where you are linking *to*
(frontend).

You're also correct about the FAQ being a difficult document to grasp. In
its defence, it covers a lot of information about a very complex area, but
it was also written by quite a number of people over a number of years and
versions, and it would definitely benefit from a rewrite. For a start, I
doubt anyone is interested in the Access 2.0 bits any more!
Unfortunately, since ULS is the baby that MS has thrown out with the
bathwater, putting resources into a rewrite is unlikely :-(

Basically, any user who may relink tables (and for me this means any user
who may open the frontend) should have all permissions to <New
tables/queries> in the frontend (see section 16.3). Then, provided they
have Open/Run permissions on the backend (which they would need to read
the data anyway), they can link tables using the
delete-and-recreate-TableDef method (section 14.3).

Sorry if you already told me, but how did you get on with reading/writing
the INI file?

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

It's not ULS that's the impenetrable thicket, it's the documentation.
ULS is a bit difficult to follow at first (Jack MacDonald's paper of the
subject was the one that finally led me to understand what is going on),
but the FAQ is helpful only after the subject has been learned elsewhere.
I still think they could have updated the FAQ at least to include Access
2003 in the title.

I think I have found enough clues in the documentation to deduce that the
destination database is the FE and the source database is the BE.
Section 14.3 states that the Connect property can be used as long as
there are "full permissions in the destination (FE) database and Open/Run
permissions on the source database (BE) - no permissions at all are
necessary on the source tables." Assuming this literally referes to
database permissions rather permissions to any tables, queries, etc., it
either says that there are no security implications to granting all users
full permissions to the FE database, or that I would need to use the code
in the faq_SetPermissions function to change permissions temporarily.
However, the documentation for that function "assumes that a member of
the Admins group will be executing the code". I'm not sure why it
assumes that, since the group name apparentlly is passed to the function,
but assuming it is necessary to pass the Admins group name, either the
code can be run only by members of the Admins group or everybody needs to
be a member of the group. If membership in the Admins group is necessary
to run the code either everybody needs to be a member of the Admins group
(which pretty much wipes out security), or only a few users can relink
tables. Or maybe the faq_SetPermissions function is not necessary. In
any case, it seems that using the Connect property is possible

I could be mistaken about the meaning of "source" and "destination". The
source could be the origin of data (FE) that flows to the destination
(BE), or the source could be the collection of data (BE) that flows to
the destination where it will be seen and used (the FE). Figurative
language should be defined. Maybe I'm supposed to know the meaning of
the two terms, but I have not found a way to frame the search question.
If I had to bet I would say the source is the BE.

For now users will get an error message if the table link is not
successfully refreshed, and I will fix it. This has not happened, so
maybe I will continue to be lucky for a while longer. Beyond that maybe
I can use the code at the mvps web site (RefreshLink), modified to remove
some or all of the prompts. Maybe I can combine it with an ini file that
lists the path.

Beyond that I don't have a prayer, at least not until I have a few more
years experience behind me. I am extremely frustrated by my inability to
sort this out, but at least I am old enough that I know when to admit I
am beaten. Thanks for trying to help. I really do appreciate the time
you have put into your responses.

Hi Bruce

The method I use (and would recommend) is the one in 14.3, because it
*always* works, no matter what the permissions on the backend tables.

Note that allowing all permissions on the linked tables in the frontend
does not grant permissions to the *data*. It just allows the user to
delete and relink the tables.

Yes, you're right, user-level security can seem like an "impenetrable
thicket", but if properly implemented it works well and is sufficiently
secure for most applications. I am one among many who are very
disappointed that it has been canned in A2007.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have full permissions for all users on the BE tables, and table
permissions restricted in the FE depending on the group. The Users
group has no permissions anywhere. I think that means some variant of
the code in 14.1 of the FAQ would work (which seems to be the same
general idea as the code at the mvps web site, I think).

I have to say I marvel anew at the Security FAQ each time I see it. It
is an almost inpenetrable thicket, further impeded by instructions and
information going back to Access 2.0.

Since I do not have RWOP queries I think I can use RefreshLink. The
FAQ states:
"The best way to implement this [it is not clear in context to what
"this" refers; maybe it means restricting access to the data, or maybe
RWOP] is to remove ALL permissions from the underlying table(s) and use
only queries to get at the data you want your users to have."
But later is seems to suggest that full permissions can be assigned to
the tables in the destination database. It would help if I knew
whether the destination database is the link's destination (i.e. the
BE) or if "destination" means something else.

Anyhow, I have not found a need for RWOP queries, so I think I'm OK
with RefreshLink. If I have a problem that could be solved through the
use of RWOP queries I have not been able to identify that a RWOP query
is what I need. I think Joan Wild has some more information about this
on her web site. Yet another thing to sort out.

I do appreciate your pointing this out. I have to say, though, that it
will take a lot of development time I do not have right now to sort out
how to read the correct path from an ini file or somewhere, check the
existing link against that, and refresh the link if need be. For now
the users will get an error message, and I will fix it. I wish there
was another way, but a better solution will have to wait.

Hi Bruce

Finally, if you use user-level security then I recommend you read the
section on linked tables (section 14) in the Access Security FAQ:
http://support.microsoft.com/support/access/content/secfaq.asp

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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.