MS Access 2003 problem

[Marcin Zmyslowski]

Hello all!

I have the following problem with MS Access 2003 permissions.

I have two users. One is admin and the second one is user who has full
permissions to enter modify and read data.

I have one table and one queries which is based on this table.

This query is good working for these two users.

Now, please be patient.

When I sort the column Name on this table the second user will get the
following error during running it:

You don't have the necessary permissions to use the <kwerenda> object.
Have
your system administrator or the person who created this object
establish the
appropriate permissions for you

This query always works OK for the first user (admin) and he has no
problem with it.

Could you help me? Because I don`t know what it is going on and I have
been trying to fix this problem for 3 days and without result.

Thank you for your help
Any help apprieciated
Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***

[Keith W]

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:oj*************@news.uswest.net...

When I sort the column Name on this table the second user will get the
following error during running it:

You don't have the necessary permissions to use the <kwerenda> object.
Have
your system administrator or the person who created this object
establish the
appropriate permissions for you

This query always works OK for the first user (admin) and he has no
problem with it.

Are you both using the same mdb file? Have you split the db into front and
back ends? For the multi-user environment to function correctly you must do
this and each user must have their own copy of the front end.

Search the help for "split database" for more info.

Regards,
Keith.
www.keithwilby.com

[salad]

Keith W wrote:

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:oj*************@news.uswest.net...

When I sort the column Name on this table the second user will get the
following error during running it:

You don't have the necessary permissions to use the <kwerenda> object.
Have
your system administrator or the person who created this object
establish the
appropriate permissions for you

This query always works OK for the first user (admin) and he has no
problem with it.
Are you both using the same mdb file? Have you split the db into front and
back ends? For the multi-user environment to function correctly you must do
this and each user must have their own copy of the front end.

This is incorrect information.
Search the help for "split database" for more info.

Regards,
Keith.
www.keithwilby.com

[Keith W]

"salad" <oi*@vinegar.com> wrote in message
news:BM***************@newsread3.news.pas.earthlin k.net...

Keith W wrote:

Are you both using the same mdb file? Have you split the db into front
and back ends? For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end.

This is incorrect information.

How so?

[Dean]

Sorting a table is actually modifing the indexes to that table. That
is why Admin has no problem. The user should modify a query.

[salad]

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:BM***************@newsread3.news.pas.earthlin k.net...

Keith W wrote:

Are you both using the same mdb file? Have you split the db into front
and back ends? For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end.

This is incorrect information.
You state "For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end."

I don't have to "must do" it. That is completely incorrect and
certainly not true.

If you can find a cite from MS that dispels my statements I'd like to
see it. I'll then go and tell my users that my apps don't work.

For the original poster, I'd check the security settings again. Perhaps
open the query in design mode, right click in the tables section, click
properties, and change the permissions from User to Owner.

How so?

[Some Bloke]

salad wrote:

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:BM***************@newsread3.news.pas.earthlin k.net...

Keith W wrote:
Are you both using the same mdb file? Have you split the db into
front and back ends? For the multi-user environment to function
correctly you must do this and each user must have their own copy of
the front end.
This is incorrect information.

You state "For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end."

I don't have to "must do" it. That is completely incorrect and
certainly not true.

If you can find a cite from MS that dispels my statements I'd like to
see it. I'll then go and tell my users that my apps don't work.

It's generally accepted that to provide discrete front ends to users
will minimise the possibility of corruption. You can get away with not
doing so but why bother when it's no trouble at all to provide each user
with their own copy? The pros and cons have been discussed at length on
here but I would always err on the side of caution.

[salad]

Some Bloke wrote:

salad wrote:

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:BM***************@newsread3.news.pas.earthlin k.net...

Keith W wrote:
> Are you both using the same mdb file? Have you split the db into
> front and back ends? For the multi-user environment to function
> correctly you must do this and each user must have their own copy
> of the front end.

This is incorrect information.
You state "For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end."

I don't have to "must do" it. That is completely incorrect and
certainly not true.

If you can find a cite from MS that dispels my statements I'd like to
see it. I'll then go and tell my users that my apps don't work.

It's generally accepted that

has nothing in common with "must do". I doubt splitting databases has
anything at all to do with the OPs question and permissions problems.

to provide discrete front ends to users will minimise the possibility of corruption. You can get away
"Get away with"? That's backing off from "must do". But you imply it.
Which is false.

with not doing so but why bother when it's no trouble at all to provide each user
with their own copy?
Irrelevent to topic at hand, but what is your distribution process for
updates?

The pros and cons have been discussed at length on here but I would always err on the side of caution.

Caution of what?

And show me a cite where MS said you "must do" a database split for
multiple users.

[Some Bloke]

salad wrote:

Some Bloke wrote:

salad wrote:

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:BM***************@newsread3.news.pas.earthlin k.net...

> Keith W wrote:
>
>
>> Are you both using the same mdb file? Have you split the db into
>> front and back ends? For the multi-user environment to function
>> correctly you must do this and each user must have their own copy
>> of the front end.
>
>
>
>
> This is incorrect information.


You state "For the multi-user environment to function correctly you
must do this and each user must have their own copy of the front end."

I don't have to "must do" it. That is completely incorrect and
certainly not true.

If you can find a cite from MS that dispels my statements I'd like to
see it. I'll then go and tell my users that my apps don't work.

It's generally accepted that

has nothing in common with "must do". I doubt splitting databases has
anything at all to do with the OPs question and permissions problems.

to provide discrete front ends to users

will minimise the possibility of corruption. You can get away

"Get away with"? That's backing off from "must do". But you imply it.
Which is false.

with not

doing so but why bother when it's no trouble at all to provide each
user with their own copy?

Irrelevent to topic at hand, but what is your distribution process for
updates?

The pros and cons have been discussed at length on

here but I would always err on the side of caution.

Caution of what?

And show me a cite where MS said you "must do" a database split for
multiple users.

Sorry Salad, I really can't be bothered with your pettiness.

[Arno R]

Hi Keith,
Please contact me.

Arno R

[Lyle Fairfield]

1. Do you have a translation for "kwerenda" for us? (I used to know a
blonde Slovakian girl named Kwerenda and she wouldn't give me
permission, either; but I doubt if this is the same type of thing.)

2. "sort the column Name on this table" could mean many things to me.
Please, describe clearly what is being done.

[John Marshall, MVP]

It's Polish for "search query"

John... Visio MVP

"Lyle Fairfield" <ly***********@aim.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

1. Do you have a translation for "kwerenda" for us? (I used to know a
blonde Slovakian girl named Kwerenda and she wouldn't give me
permission, either; but I doubt if this is the same type of thing.)

2. "sort the column Name on this table" could mean many things to me.
Please, describe clearly what is being done.

[salad]

Some Bloke wrote:

Sorry Salad, I really can't be bothered with your pettiness.

I apologise. I asked you to back up your opinion, which I said was
false, and you couldn't.

[PleaseStopAdvertising]

"Marcin Zmyslowski" <ma*****@kapy.bydg.pl> wrote in message
news:oj*************@news.uswest.net...

Hello all!

I have the following problem with MS Access 2003 permissions.

I have two users. One is admin and the second one is user who has full
permissions to enter modify and read data.

I have one table and one queries which is based on this table.

This query is good working for these two users.

Now, please be patient.

When I sort the column Name on this table the second user will get the
following error during running it:

You don't have the necessary permissions to use the <kwerenda> object.
Have
your system administrator or the person who created this object
establish the
appropriate permissions for you

This query always works OK for the first user (admin) and he has no
problem with it.

Could you help me? Because I don`t know what it is going on and I have
been trying to fix this problem for 3 days and without result.

Thank you for your help
Any help apprieciated
Marcin from Poland

*** Sent via Developersdex http://www.developersdex.com ***

Greetings Marcin,

I see that you have received several responses to your question. I think,
perhaps, you might still be looking for a solution.

Records in a table can not be sorted. You can view them in sorted order by
using a query. When you view records in table datasheet view and apply a
sort, a query is created for you to sort by the selected column. When you
close the table it asks if you wish to save the query. If you save that
query it will be used then next time that you open the table datasheet.

From the symptom you describe above, it sounds as though you are saving the
query as the admin user but your non admin user cannot view the table
datasheet because he does not have permission for that query.

In my opinion you should not have the users directly view or work with the
table datasheet. They should always use a form or a saved query for users to
access the data. You can manage permissions on the query or form.

I hope this makes sense and is of some help to you.
Randy Harris

[Keith W]

"salad" <oi*@vinegar.com> wrote in message
news:Bi****************@newsread3.news.pas.earthli nk.net...

Some Bloke wrote:

Sorry Salad, I really can't be bothered with your pettiness.

I apologise. I asked you to back up your opinion, which I said was false,
and you couldn't.

Couldn't or chose not to? How can an opinion be false? I post information
to help others on here, not get into petty arguments. I try provide help
here based on my experience and anyone with a modicum of common sense will
realise that it is not gospel and that they can take it or leave it. In my
experience it is a "must" to provide each user with their own FE as I have
experienced corruptions with shared FEs which have not recurred with
discrete ones. In the client-server environment in which I work is it just
as easy to provide discrete FEs as shared ones.

I have also read that shared FEs are a bad thing from many developers far
more experience than I on many other groups. There is also evidence of MS's
awareness of problems with sharing here:

http://support.microsoft.com/default...b;en-us;300216

"Microsoft Jet is a file-sharing database system. A file-sharing database is
one in which all the processing of the file takes place at the client. When
a file-sharing database, such as Microsoft Jet, is used in a multiuser
environment, multiple client processes are using file read, write, and
locking operations on the same shared file across a network. If, for any
reason, a process cannot be completed, the file can be left in an incomplete
or a corrupted state. Two examples of when a process may not be completed is
when a client is terminated unexpectedly or when a network connection to a
server is dropped."

If you have any evidence to suggest that shared FEs are a good thing then
I'd be interested to hear it.

[salad]

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:Bi****************@newsread3.news.pas.earthli nk.net...

Some Bloke wrote:

Sorry Salad, I really can't be bothered with your pettiness.
I apologise. I asked you to back up your opinion, which I said was false,
and you couldn't.

Couldn't or chose not to? How can an opinion be false?

I reacted to the "must do" reference. Split mdbs did not appear to have
any relevence to the OPs problem, nor do MDBs have to be split in order
to work...or work correctly.

I post information to help others on here, not get into petty arguments. I try provide help
here based on my experience and anyone with a modicum of common sense will
realise that it is not gospel and that they can take it or leave it.
I've been corrected in the past for posts I've made. If I did not
provide correct information, and the info provided does not have an
"aircode" reference, I certainly don't mind standing corrected.

In my experience it is a "must" to provide each user with their own FE as I have
experienced corruptions with shared FEs which have not recurred with
discrete ones. In the client-server environment in which I work is it just
as easy to provide discrete FEs as shared ones.
I'll assume your apps are small and don't get updated much if it is easy
to provide discrete FEs as shared ones.
I have also read that shared FEs are a bad thing from many developers far
more experience than I on many other groups. There is also evidence of MS's
awareness of problems with sharing here:
Bad thing? I wouldn't know. Knock on wood so far I guess. The
network I run on is rock solid, always the newest updates/patches.

http://support.microsoft.com/default...b;en-us;300216

"Microsoft Jet is a file-sharing database system. A file-sharing database is
one in which all the processing of the file takes place at the client. When
a file-sharing database, such as Microsoft Jet, is used in a multiuser
environment, multiple client processes are using file read, write, and
locking operations on the same shared file across a network. If, for any
reason, a process cannot be completed, the file can be left in an incomplete
or a corrupted state. Two examples of when a process may not be completed is
when a client is terminated unexpectedly or when a network connection to a
server is dropped."
I don't see much in this regarding split mdbs.

If you have any evidence to suggest that shared FEs are a good thing then
I'd be interested to hear it.

Six years of a non-split database I inherited. Still has instantaneous
response for 15-20 users and no corruption. Again, knock on wood. Had
power outages but then we have a backup unit for power so no probs there.

I split the thing up about 3 years ago, gave everyone their own FE, and
the app slowed to a crawl. Everyone was up to date on OS/Office
updates. I posted a question as to why the FE would cause the app to
crawl in this newsgroup, got no response, went back and made the FE/BE
the same, and it sped right up and works. I haven't bothered to
research the problem since. Someday I will but the priority to do so is
so far down the ladder it will be awhile.

[TC]

salad wrote:

(snip)

I split the thing up about 3 years ago, gave everyone their own FE, and
the app slowed to a crawl.

Normally this just means that you are constantly opening & closing the
BE database. Eg. you have an unbound main menu that calls bound forms.

It is easily fixed by opening a constant connection to the BE for the
duration of the front-end session.

dim db as database
'on FE start: set db = dbengine.opendatabase("path to
back-end")
'on FE end: set db = nothing

Just make sure the variable does not go out of scope while the FE is
running. If it does, this closes that connection & the benefit of this
is lost. Eg. make the variable a global in a standard module.

It is not necessary to open a /recordset/ in the BE to hold it open.
The code above is quite enough.

HTH,
TC

[Keith W]

"salad" <oi*@vinegar.com> wrote in message
news:bU****************@newsread3.news.pas.earthli nk.net...

Keith W wrote:

"salad" <oi*@vinegar.com> wrote in message
news:Bi****************@newsread3.news.pas.earthli nk.net...

Couldn't or chose not to? How can an opinion be false?
I reacted to the "must do" reference. Split mdbs did not appear to have
any relevence to the OPs problem, nor do MDBs have to be split in order to
work...or work correctly.

It was a bit of extra information which I thought would be useful to the OP
which they could have chosen to disregard. The "must" is my opinion based
upon 15 years experience.

I post information

to help others on here, not get into petty arguments. I try provide help
here based on my experience and anyone with a modicum of common sense
will realise that it is not gospel and that they can take it or leave it.
I've been corrected in the past for posts I've made. If I did not provide
correct information, and the info provided does not have an "aircode"
reference, I certainly don't mind standing corrected.

Nor do I mind being corrected, and am thankful for corrective information
but I believe that I am correct on this issue, again based upon my
experience and opinion.

In my

experience it is a "must" to provide each user with their own FE as I
have experienced corruptions with shared FEs which have not recurred with
discrete ones. In the client-server environment in which I work is it
just as easy to provide discrete FEs as shared ones.
I'll assume your apps are small and don't get updated much if it is easy
to provide discrete FEs as shared ones.

Some are huge/complex beasts and updated on a weekly basis. The lastest
version of the FE is placed in a "public" folder and the users' shortcut
executes a batch file which copies the "public" file to their C drive and
then opens it. It's that simple. If users are sharing a FE then you have
the extra pain of having them all log out whilst you deliver an update.

I have also read that shared FEs are a bad thing from many developers far
more experience than I on many other groups. There is also evidence of
MS's awareness of problems with sharing here:
Bad thing? I wouldn't know. Knock on wood so far I guess. The network
I run on is rock solid, always the newest updates/patches.

http://support.microsoft.com/default...b;en-us;300216

"Microsoft Jet is a file-sharing database system. A file-sharing database
is one in which all the processing of the file takes place at the client.
When a file-sharing database, such as Microsoft Jet, is used in a
multiuser environment, multiple client processes are using file read,
write, and locking operations on the same shared file across a network.
If, for any reason, a process cannot be completed, the file can be left
in an incomplete or a corrupted state. Two examples of when a process may
not be completed is when a client is terminated unexpectedly or when a
network connection to a server is dropped."

I don't see much in this regarding split mdbs.

It is implied by the suggestion that lost network connections can cause
corruption. To me this effect would be amplified if several users lost
their connection to the same FE. This is my interpretation, bearing in mind
that it is no hardship for me to supply discrete FEs and that users are
unlikely to lose a connection to a FE on their C drive.

If you have any evidence to suggest that shared FEs are a good thing then
I'd be interested to hear it.
Six years of a non-split database I inherited. Still has instantaneous
response for 15-20 users and no corruption. Again, knock on wood. Had
power outages but then we have a backup unit for power so no probs there.

Well in my experience I think you have been lucky.

I split the thing up about 3 years ago, gave everyone their own FE, and
the app slowed to a crawl. Everyone was up to date on OS/Office updates.
I posted a question as to why the FE would cause the app to crawl in this
newsgroup, got no response, went back and made the FE/BE the same, and it
sped right up and works. I haven't bothered to research the problem
since. Someday I will but the priority to do so is so far down the ladder
it will be awhile.

If it slowed to a crawl then I suspect another coincidental issue at work.
My most recently split apps have about the same user loading as you describe
here and performance isn't significantly affected if at all. For this
reason it doesn't surprise me that you didn't get any responses to your
question, which I vaguely remember reading myself.

I think we'll have to agree to disagree on this issue but I believe that to
say that I posted incorrect information is in itself incorrect.

[salad]

TC wrote:

salad wrote:

(snip)

I split the thing up about 3 years ago, gave everyone their own FE, and
the app slowed to a crawl.

Normally this just means that you are constantly opening & closing the
BE database. Eg. you have an unbound main menu that calls bound forms.

It is easily fixed by opening a constant connection to the BE for the
duration of the front-end session.

dim db as database
'on FE start: set db = dbengine.opendatabase("path to
back-end")
'on FE end: set db = nothing

Just make sure the variable does not go out of scope while the FE is
running. If it does, this closes that connection & the benefit of this
is lost. Eg. make the variable a global in a standard module.

It is not necessary to open a /recordset/ in the BE to hold it open.
The code above is quite enough.

HTH,
TC

Interesting. It describes my situation back then. A person sometimes
would have to hit a command button 2-3 times before it'd fire. I'll
check your advice out sometime.

[Tim Marshall]

salad wrote:

<snip fuss>

For what it's worth, 8)

I must say, I've been blinking at this one. I'm glad your shared FEs
work well. However, it's been my understanding for as long as I've been
around cdma (from about 1998) that sharing FEs can lead to corruption
and is therefore not adviseable, though as you say, there's nothing
stopping one from doing it! Further, with the sort of stuff I do, I'm
not sure a shared FE would work.

For example, consider a form in which there are numerous controls (with
and without controlsources and/or rowsources based on the BE, it
wouldn't matter). The purpose of one or more of the form's buttons is
to run forms/reports. However, first the button action constructs an
SQL statement based on selections the user has made on various controls
on the main form. What would happen in a shared front end if, for
example, two or more users are on at the same time and all have entered
completely different date ranges? Which would be used?

This was originally why I went the separate FE route - and the info I
received here on potential corruption with sharing FE made me even more
glad I did it that way.

Certainly, maintaining the FE and ensuring users are all using the same
version can certainly be an unholy PITA. I usually do version control
where the BE (in my case, usually Oracle) has a table indicating the
current version - if the FE version number, stored as a constant in the
FE, doesn't match, the FE app quits.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Gord]

Hey, Tim.

While my opinions may not be as strong as salad's on this issue, my
experiences have been similar. I have built dozens of split db's and in
cases where the FE has ended up being shared I have seen no consistent
pattern of that FE becoming corrupted. I suggest to the client that a
local copy of the FE is the preferred approach, especially if the FE is
inherently large or prone to bloat, but clients (or their local IT
staff) don't always follow that advice.

For example, consider a form in which there are numerous controls (with
and without controlsources and/or rowsources based on the BE, it
wouldn't matter). The purpose of one or more of the form's buttons is
to run forms/reports. However, first the button action constructs an
SQL statement based on selections the user has made on various controls
on the main form. What would happen in a shared front end if, for
example, two or more users are on at the same time and all have entered
completely different date ranges? Which would be used?

Barring any obvious multi-user obstacles like a shared local table in
the FE, each user would be operating on their *instance* of the form.
Just because they grab their copy of an object from a shared FE doesn't
mean that they are operating on the same instance of that object.

[Lyle Fairfield]

Where doess the shared FE file reside?

When the front end is shared what happens if I change a property of the
application (or database) in my instance of the front end? Does the
property change affect everyone who uses the shared FE? I have seen
many situations where developers save a local user's preference or
whatever as a property; next time the user opens the db, presto, his
preference is retrieved and determines some characteristic of the
application.

When the front end is shared what happens when we build and save a new
form or report in code? Can we do this? Suppose no one else but me has
an instance of the front end open and I create a new object, perhaps
through code as a normal part of the application, perhaps interactively
just to be obtuse. Then when another person opens his/her instance of
the FE, does he inherit my changes?

What happens to temporary queries that have been optimized and then
saved for User1? Can we assume the optimization plan will be suitable
for User2? Will Access/Jet be smart enough not to use the old saved
temporary query?

Well, as I have never experimented with a shared FE I don't know the
answers and perhaps these situations would cause no difficulty at all.
I shall never know for sure.

[Tim Marshall]

Gord wrote:

What would happen in a shared front end if, for
example, two or more users are on at the same time and all have entered
completely different date ranges? Which would be used?

Barring any obvious multi-user obstacles like a shared local table in
the FE, each user would be operating on their *instance* of the form.

Thanks for the reply.

OK, I understand that, but what about the selections write the SQL to a
querydef object in the shared FE that is the basis for a report or
another form?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[TC]

My bet is that if user A changed queydef Z, all other concurrent users
of that shared FE would see that change - barring refresh issues etc.
The reason being, that there is only one querydef named Z, permanently
stored in that FE. Jet does not nativey allow for the permanent storage
of a seperate copy of Z for each user. So even if Jet did hide each
user's change, from all the other users, while they were running, it
would have no way to /save/ those changes as each user departed from
the shared FE.

Why don't you try it & tell us?

TC

[Tim Marshall]

TC wrote:

My bet is that if user A changed queydef Z, all other concurrent users
of that shared FE would see that change - barring refresh issues etc.
<snip>

Thanks for the reply: That's my understanding as well. 8) Which is why
I don't share front ends.
Why don't you try it & tell us?

Well, I could, but the above is what I was counselled to do here many
moons ago and I've made a practice ever since. So I don't really have
an interest in whether it behaves this way or not... 8) I as just
commenting on Salad's discussion. As I said I was surprised to see this
point of view as it's been my understanding for years that separate
copies of the FE are a "pillar of belief", so to speak. 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David W. Fenton]

"TC" <aa**********@yahoo.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

My bet is that if user A changed queydef Z, all other concurrent
users of that shared FE would see that change - barring refresh
issues etc. The reason being, that there is only one querydef
named Z, permanently stored in that FE. Jet does not nativey allow
for the permanent storage of a seperate copy of Z for each user.
So even if Jet did hide each user's change, from all the other
users, while they were running, it would have no way to /save/
those changes as each user departed from the shared FE.

Why don't you try it & tell us?

I'd be surprised if you *can* save a QueryDef in an MDB that is
opened by more than one user. Perhaps I'm misunderstanding the way
the monolithic save model works, but surely a QueryDef would be
subject to the same multi-user editing limitations (i.e., you
*can't*) as forms/report, etc.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"TC" <aa**********@yahoo.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

My bet is that if user A changed queydef Z, all other concurrent
users of that shared FE would see that change - barring refresh
issues etc. The reason being, that there is only one querydef
named Z, permanently stored in that FE. Jet does not nativey allow
for the permanent storage of a seperate copy of Z for each user.
So even if Jet did hide each user's change, from all the other
users, while they were running, it would have no way to /save/
those changes as each user departed from the shared FE.

Why don't you try it & tell us?

And, BTW< if it *did* work, I think each user would have to refresh
the QueryDefs collection before running any form/report that's based
on the changed QueryDef in order to get the version updated by a
different user.

Basically, sharing a front end is just a bloody awful idea and you
just shouldn't do it. Ever.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[salad]

Tim Marshall wrote:

salad wrote:

<snip fuss>

For what it's worth, 8)

I must say, I've been blinking at this one. I'm glad your shared FEs
work well. However, it's been my understanding for as long as I've been
around cdma (from about 1998) that sharing FEs can lead to corruption
and is therefore not adviseable, though as you say, there's nothing
stopping one from doing it! Further, with the sort of stuff I do, I'm
not sure a shared FE would work.
Hi Tim, thanks for your response. Yes, nothing is stopping one from
doing it. And I didn't think it was the solution to the OPs problem. I
agree for the most part in splitting mdbs, it simply isn't a requirement.

The features I have in my current database is miles beyond any database
app I've worked on in 20 years. It's so cool. They way I describe it
is that I'm a composer and I write the "notes" to the song, but the
users play the song. I install the notes and come back a couple months
later to see the concert. I'm constantly blown away by the ways the
users take those individual notes and make them a symphony. And it
works fine as a shared app. Now if I were to sell it, I'd definitely
want it split. I see you mention Oracle. A good reason to have it split.

Sidebar
I have to say, I enjoy helping out users here at CDMA. Yesterday I was
thinking about all the people I respect here and wanted to acknowledge
them. I've learned so much from this group (the other experts) that at
this time I'd like to thank them all...sorry if I misspell names...and
if I forget to mention someone, they aren't forgotten for their
help...anyway...those names include you Tim Marshall, Allen Brown, Terry
Kreft, Albert Kallal, Trevor Best, Tony Toews, David Fenton, Michka, MG
Foster, basCostBuddy, Bob Alston, Bob Quintall, Danny Lesandri, Chuck
Grimbsy, Dev Ashish, David Schofield, Larry Linson, Lyle Fairfield,
Dimitri Furman, Douglas Steele, Pedro, Jim Fortune, Datasheet, John
Winterbottom, Keith Willby, Lauren Quantrell, MacDermet, Pete Cresswell,
Piet Lindon, Rick Brandt, Stephan Lebans, Steve Jorgensen, Tom von
Stiphout, Wayne Morgan, and Wayne Gillespie.

It's kinda funny. I my safety deposit box I have a paper with your
names and email addresses that in case I die my client has some names of
other great programmers I think very highly of and he can contact so to
keep the application quality high.
End Sidebar
For example, consider a form in which there are numerous controls (with
and without controlsources and/or rowsources based on the BE, it
wouldn't matter). The purpose of one or more of the form's buttons is
to run forms/reports. However, first the button action constructs an
SQL statement based on selections the user has made on various controls
on the main form. What would happen in a shared front end if, for
example, two or more users are on at the same time and all have entered
completely different date ranges? Which would be used?
The different ranges. I have a pretty slick method of filtering log
forms and filters work fine. Same with forms that generate filters when
calling reports...the reports work fine.
This was originally why I went the separate FE route - and the info I
received here on potential corruption with sharing FE made me even more
glad I did it that way.
The app I am working on is 6-7 years old. I inherited it 5 years ago.
Never had any corruption (knock on wood) and the app flies. I had a
problem splitting it...it slowed the system to a crawl, and TC gave some
advice I'll check out later on.
Certainly, maintaining the FE and ensuring users are all using the same
version can certainly be an unholy PITA. I usually do version control
where the BE (in my case, usually Oracle) has a table indicating the
current version - if the FE version number, stored as a constant in the
FE, doesn't match, the FE app quits.

If you do a search on Google, Danny Lesandri has an article on
distributing FEs that may be worth a gander.

[salad]

David W. Fenton wrote:

"TC" <aa**********@yahoo.com> wrote in
news:11*********************@g47g2000cwa.googlegro ups.com:

My bet is that if user A changed queydef Z, all other concurrent
users of that shared FE would see that change - barring refresh
issues etc. The reason being, that there is only one querydef
named Z, permanently stored in that FE. Jet does not nativey allow
for the permanent storage of a seperate copy of Z for each user.
So even if Jet did hide each user's change, from all the other
users, while they were running, it would have no way to /save/
those changes as each user departed from the shared FE.

Why don't you try it & tell us?

I'd be surprised if you *can* save a QueryDef in an MDB that is
opened by more than one user. Perhaps I'm misunderstanding the way
the monolithic save model works, but surely a QueryDef would be
subject to the same multi-user editing limitations (i.e., you
*can't*) as forms/report, etc.

Depends on the version. With A97. You can change the query while
others are in the system. However, the users that aren't the user
making the changes should exit out/come back in when mods are made to
forms, reports, modules, queries. As far as tables go, it might be best
to have everyone out of the app.

[Gord]

Tim wrote:

but what about the selections write the SQL to a querydef object in the
shared FE that is the basis for a report or another form?

I suppose that would be another situation in which a shared FE was
contraindicated. I wasn't suggesting that they were a "good thing", I
was merely trying to point out that:

1. They are able to support basic multi-user scenarios, and

2. They are _not_ guaranteed to result in corruption.
In the end, you must do what you think is right.

[Dean]

Records in a table can not be sorted. You can view them in sorted order by
using a query. When you view records in table datasheet view and apply a
sort, a query is created for you to sort by the selected column. When you
close the table it asks if you wish to save the query. If you save that
query it will be used then next time that you open the table datasheet.

Tables can be sorted. This occurs when the user just clicks on the
column (not as described above). When you close the table, MS-Access
will ask if you want to save changes. It will then add a non-unique
index to that sorted field. You shouldn't allow users to do this.
They can write queries sort records.

[David W. Fenton]

"Gord" <gd*@kingston.net> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com:

Tim wrote:

but what about the selections write the SQL to a querydef object
in the shared FE that is the basis for a report or another form?
I suppose that would be another situation in which a shared FE was
contraindicated. . . .

A shared front end is contraindicated IN ALL CASES for any
multi-user application.

Period.
. . . I wasn't suggesting that they were a "good
thing", I was merely trying to point out that:

1. They are able to support basic multi-user scenarios, and

2. They are _not_ guaranteed to result in corruption.

In the end, you must do what you think is right.

I could walk across the freeway at any point in the day and:

1. I would be able to get across the freeway in many cases, and

2. there's no guarantee that I would be hit by a car.

But that doesn't mean it's OK to recommend that people every cross
the freeway on foot.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Dean" <de**@coveyaccounting.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Tables can be sorted. This occurs when the user just clicks on
the column (not as described above). When you close the table,
MS-Access will ask if you want to save changes. It will then add
a non-unique index to that sorted field. You shouldn't allow
users to do this. They can write queries sort records.

This is not sorting the table. It's roting the datasheet.

If you sort your "table" in front end #1 it won't change the order
of records in front end #2 (or in the back end).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Randy Harris]

"Dean" <de**@coveyaccounting.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Records in a table can not be sorted. You can view them in sorted order by using a query. When you view records in table datasheet view and apply a
sort, a query is created for you to sort by the selected column. When you close the table it asks if you wish to save the query. If you save that
query it will be used then next time that you open the table datasheet.

Tables can be sorted. This occurs when the user just clicks on the
column (not as described above). When you close the table, MS-Access
will ask if you want to save changes. It will then add a non-unique
index to that sorted field. You shouldn't allow users to do this.
They can write queries sort records.

This may well be the single most misunderstood concept by Access users.
Tables are not sorted, though indexes are. As David Fenton stated, when you
view a table datasheet and request a sorted order it does not change the
order of the data in a table, only the view in the datasheet.

You are, of course, correct about not having users access tables directly.
Users should access data through forms.

[TC]

No, IMO the issues are different.

Queries are native Jet objects. Jet knows exactly what they are, and
how to manage them. It is Jet, not Access, that does all the query
management & processing. I can't see any problem, in principle, with
one user modifying a query in a shared FE, then another user modifying
it again, later (after the first user has stopped using it). The only
issue that I can see, would be if they tried to do that simultaneously.

In contrast, forms & reports are not native Jet objects. They're just
blobs of binary data that Access has asked Jet to store, on its behalf,
in the mdb file. Even the relevant containers do not exist in a Jet
database, until Access has asked Jet to create them. Jet literally has
no idea what forms & reports are, or how to manage them.

So it is inevitable that some of these issues will work differently for
queries & tables, as compared to forms & reports.

Cheers,
TC

[TC]

David W. Fenton wrote:

Basically, sharing a front end is just a bloody awful idea and you
just shouldn't do it. Ever.

I agree.

Apart fom anything else, imagine the network traffic as the FE code was
blasted around to all the end-users! The response time would be
terrible, surely.

TC

[salad]

TC wrote:

David W. Fenton wrote:

Basically, sharing a front end is just a bloody awful idea and you
just shouldn't do it. Ever.
I agree.

Apart fom anything else, imagine the network traffic as the FE code was
blasted around to all the end-users! The response time would be
terrible, surely.

Well, my users certainly have no complaints. Now I admit there are some
people that would like to be StarTrek Binaries...and they may notice a
nanosecond here or there. My users aren't at that level, instantaneous
is about as instanteneous as it gets.
TC

[David W. Fenton]

"TC" <aa**********@yahoo.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

No, IMO the issues are different.

Queries are native Jet objects. Jet knows exactly what they are,
and how to manage them. It is Jet, not Access, that does all the
query management & processing. I can't see any problem, in
principle, with one user modifying a query in a shared FE, then
another user modifying it again, later (after the first user has
stopped using it). The only issue that I can see, would be if they tried to do that simultaneously.
Well, as I said, it all depends on what is stored in Access project
in the single record. If only the Access objects are stored there,
then Jet objects would be editable when the MDB is open by multiple
objects, since Jet objects would still be stored as one record per
object.

One mistake you make above, though, is in ignoring the fact that
Access object definitions are stored in Jet tables, just like Jet
objects, so there really is no difference, except if there's a
difference in how they are saved.

It would appear that Jet objects are not stored in the Access
project, as I was just able to create and edit a saved query while
the MDB was open in two instances of Access. I was prohibited from
doing the same thing for Forms.

However, the new query and the changes to it showed up immediately
in the other instance of Access, so that means that the
contemplated
use would *not* work, since different users would see the other's
saved QueryDefs.

Of course, there's also the danger of collisions on this, two users
trying to alter the QueryDef at the same time.
In contrast, forms & reports are not native Jet objects. They're
just blobs of binary data that Access has asked Jet to store, on
its behalf, in the mdb file. Even the relevant containers do not
exist in a Jet database, until Access has asked Jet to create
them. Jet literally has no idea what forms & reports are, or how
to manage them.

So it is inevitable that some of these issues will work
differently for queries & tables, as compared to forms & reports.

I think it's just bloody idiotic to try to justify sharing a front
end under any circumstances. While you could describe a very
specific situation in which it would seem to work reliably (at
least
for a while), those conditions could very easily change and get
outside the realm of what works.

And beside that, there are too many properties that get saved to
forms, for instance, for me to be comfortable with having multiple
users in the same MDB. Sooner or later all those multiple rates to
a
single record (the Access project) are going to hose that record
and
corrupt your MDB. It's simply asking for trouble.

You'd never design data interaction to work that way, so I don't
know why anyone in their right mind would set up the front end that
way.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"TC" <aa**********@yahoo.com> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

David W. Fenton wrote:

Basically, sharing a front end is just a bloody awful idea and
you just shouldn't do it. Ever.

I agree.

Apart fom anything else, imagine the network traffic as the FE
code was blasted around to all the end-users! The response time
would be terrible, surely.

Actually, that's not relevant, either. Jet is smart enough to
pre-fetch what it needs, and it's really not a significant amount of
data on a 100BaseT netework, except if you're storing huge graphics
in your front end.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[TC]

Sorry, I'm not interested in arguing the point.

TC