Split Database not allowing more than one user

[Chris O'C via AccessMonster.com]

Sorry, I mean Jet replication. What was I thinking?

Chris

 

[Archidrb via AccessMonster.com]

You said:

The front end should not be replicated. You don't want to synch up changes
in objects in the front end amongst many users unless you want db corruption.

Okay, since I am a complete moron who has no value and knows absolutely
nothing, why in over five years (closer to six to count it out) have I not
had a single incident of this? We only converted to the SQL backend at the
very end of 2007 and used single network Access BE's for the three databases
the previous four+ years before. Perhaps its that we have a different
concept of many users.

I will accept that I need to learn a lot more about Jet Replication; I am
stuck in the mud with my understanding of it. But you and others - who claim
to be experts and MVPs - have chosen to tell me how useless I am instead of
taking the teaching opportunity to explain to me (and anyone else reading the
post) what the point of having multiple backends in a multi-user network
solution would be? How did you learn it? Did you spend day after day
reading books and online articles or did someone teach the basics to you?

I get it that remote, non-networking users (I use the generic term travelling
salesman) don't want to be tied down to a network while on the road or at
home. Why would that user need a FE and a BE on their laptop in order to
replicate over the data? And if you are not replicating the FE, how do you
get and keep an up-to-date version of the FE on their machine?

Please tell me that your DB's are perfect and ready for full-time, permanent
use the day they implement. Teach me how to get my end users never to ask
for enhancements or new pieces. Teach me how I get my DB's to be complete
and perfect at implementation, because I don't know how to do that. Then
tell me why you choose to make me (or anyone) feel like my solution is the
complete anti-Christ of networked Access databases that will bring about the
end of the database world as we know it? What's up with that?

I looked at Tony Toewe's piece today and emailed him directly for more
information. I had only looked at products that required licensing in the
past. His tool is for networked pieces, potentially usable in my situation.
No licensing fees is good.

In the mean time, my anti-Christ solution is and has been working since late
November of 2002 and growing significantly, but at some impending moment in
the coming future is will destroy itself and everyone in the building.
Please, compel me to change it, apparently I am too stupid to do it on my own.

 

[David W. Fenton]

Access 2007 supports user level security for all mdb format files.
It doesn't support user level security on accdb format files
because those dbs use the ACE db engine, not Jet. You need Jet
for user level security.

ACE is Jet 5, just with a different name. MS purposely chose to omit
ULS and replication because that fit their agenda for what they want
to do with Jet going forward.

 

[David W. Fenton]

I have some pretty
extensive hands-on experience with using an Access BE and a
repliated FE set.

Then you have extensive experience with completely misusing Jet
replication.

There is no justification for replicating a front end. Front ends
have no data that needs to be synchronized -- they have only
front-end user interface objects, and when those are updated, one
need only replace the old front end with the new one. There a number
of tools for automating that process so the user never needs to
worry about it.

Replicating a front end will eventually corrupt the VBA project and
it may make it impossible to synch. Worse still, if the corruption
gets bad enough, you could lose your entire project.

But since there's nothing gained by replicating a front end, it's
quite obvious there's no need to even take the risk.

Please stop giving all this bad advice about replication. This is
the second thread I've seen in the last couple of days in which
you've horned in and given completely erroneous recommendations for
using replication.

PLEASE STOP.

You don't know what you're talking about.

 

[David W. Fenton]

I could not disagree more.

Then you are an igorant moron who really doesn't understand enough
about Jet replication to be using it, let along posting advice for
other people.

Everything Chris wrote was spot-on correct.

When using a FE/BE setup, there is no need to
replicate data because the data is sent from any FE to the BE
directly. In Access replication

There is no such thing as "Access replication" -- there is only JET
replication. The reason you can replicate a front-end MDB is because
it's a Jet data store, and the front-end objects are stored in Jet
data tables.

But it's completely inadvisable to do so, because the Access project
(i.e., all the code-bearing objects, forms/reports/modules) is
stored in a single BLOB field in a single record in one of the
system tables. The internal structure of that BLOB field is
COMPLETELY UNKNOWN to Jet, as it entirely Access-controlled.

In a front end, your user is not making changes to the front end
objects, so there is no need to actually synchronize anything from
the user back to the developer. Front end changes are ONE WAY, so
there is no utility in using replication, *except* if you haven't
split your app. In that case, you've fracked things up already by
not splitting, and rather than going the replication route for the
unsplit MDB, you should split and then you won't need replication at
all for distributing updates to the front-end application.

is for making "clones" of the master FE.

YOU ARE WRONG.

When structural
changes are processed in the master FE the only way to get those
changes into the replica is to synchronize - or to have every user
replace their cloned (replicated) FE with a new one.

Yes, exactly. They replace the UNREPLICATED front end with a new
copy of the front end.

NO REPLICATION NEEDED.

I have spent many years working in replicated Access databases and
I am not wrong.

YOU ARE COMPLETELY 100% WRONG.

I know that synchronization can be used to copy data from one FE
to
another FE, but replication is a way to clone the FE's.

This is a completely brain-dead stupid statement, with no basis in
fact.

It may be that you are confusing SQL with Access, an all too
common problem in Access boards. As a SQL Server certified DBA I
know that you are using the SQL definition of replication. Why
Microsoft used the same terms in different programs that do
different things is a question for MS.

You are a world-class moron, quite obviously.

An explanation of Jet replication:

http://dfenton.com/DFA/Replication/index.php?title=What_is_Jet_Replic
ation%3F

You obviously have read nothing about the actual definitions of
database replication, or you would know how completely wrong
everything you've written about Access and Jet replication is.

I have not explored much into ADP files and perhaps in a
distributed ADP synchronization is used to move data back and
forth between FE's. When using replicated MDB files, you go to
Tools>Synchronization>Create Replica to make a clone of the FE.
Then to pick up changes to the structure in the front end a
synchronization is run to the replica(s).

You will only do this IF YOU ARE COMPLETELY STUPID.

Or, I guess, a novice.

As for replicating data back and forth from one FE to another

There is no need for this. There is nothing in a front end that
needs to be synchronized.

without a BE
would be performed using the synchronization in Access, but users
must be careful to process the changes to a table in only one
place. In my current db's I have local tables that contain static
data that only occassionally needs additions or deletions. In
those cases, I change the data and run the synchronization to copy
the data into the replicas. Tools>Replication>Create Replica
creates a FE.

You are a very, very confused person, and what you've written above
is completely gibberish. It shows that you really know nothing at
all about Jet replication and don't even have any real understanding
of Access development.

[]

So rather than being insulting, I will accept that you appear to
be confusing multiple processes in Access with SQL terminology.

You are a complete moron.

I suspect that you might be a sock puppet of Aaron's, given that you
don't exist in Google's archives until the last few days.

If you are Aaron and we are able to detect it, that will be reported
to your probation officer and you'll be in really deep shit.

If you're not Aaron, then you will be landing in killfiles
everywhere along with Aaron, and will be ignored just like he is.

 

[David W. Fenton]

You [i.e., Chris O'C] said:

The front end should not be replicated. You don't want to synch
up changes in objects in the front end amongst many users unless
you want db corruption.

Okay, since I am a complete moron

If you're not a complete moron, why are you posting such completely
moronic advice?

who has no value and knows absolutely
nothing, why in over five years (closer to six to count it out)
have I not had a single incident of this?

My guess is because you are completely lying about what you're
doing.

Another alternative is that nobody actually uses the app that you
claim has a replicated front end.

We only converted to the SQL backend at the
very end of 2007 and used single network Access BE's for the three
databases the previous four+ years before. Perhaps its that we
have a different concept of many users.

This subject has nothing to do with replicating an Access front end,
so I'm not sure why you throw it into the mix.

I will accept that I need to learn a lot more about Jet
Replication; I am stuck in the mud with my understanding of it.
But you and others - who claim to be experts and MVPs - have
chosen to tell me how useless I am instead of taking the teaching
opportunity to explain to me (and anyone else reading the post)
what the point of having multiple backends in a multi-user network
solution would be?

Where has anyone suggested this? No one has done so.

How did you learn it? Did you spend day after day
reading books and online articles or did someone teach the basics
to you?

I've been creating replicated Access applications since 1997. I'm
the creator of the Jet Replication Wiki:

http://dfenton.com/DFA/Replication/

I do know a bit about the proper use of Jet replication and how it
works internally. I certainly don't know as much as people like
Michael Kaplan, but I have learned many things over the years, and
one of the things I've learned is that the things you recommend are
simply WRONG.

I get it that remote, non-networking users (I use the generic term
travelling salesman) don't want to be tied down to a network while
on the road or at home. Why would that user need a FE and a BE on
their laptop in order to replicate over the data? And if you are
not replicating the FE, how do you get and keep an up-to-date
version of the FE on their machine?

This is not a replication question.

Even so, if they can synchronize their front end, then they are
obviously connected to a network from which they could also download
a new front end.

In other words, the environment you assume for distributing
front-end changes via replication of necessity also provides the
appropriate environment for simply updating the front end when
needed.

Please tell me that your DB's are perfect and ready for full-time,
permanent use the day they implement. Teach me how to get my end
users never to ask for enhancements or new pieces. Teach me how I
get my DB's to be complete and perfect at implementation, because
I don't know how to do that. Then tell me why you choose to make
me (or anyone) feel like my solution is the complete anti-Christ
of networked Access databases that will bring about the end of the
database world as we know it? What's up with that?


I looked at Tony Toewe's piece today and emailed him directly for
more information. I had only looked at products that required
licensing in the past. His tool is for networked pieces,
potentially usable in my situation. No licensing fees is good.

Tony's utility has been around for many, many years. And there are
others much like it (though I've not found any as full-featured, or
so well-cared-for). You can do it with batch files, ferchrissakes.

In the mean time, my anti-Christ solution is and has been working
since late November of 2002 and growing significantly, but at some
impending moment in the coming future is will destroy itself and
everyone in the building.

It *will* corrupt. That it hasn't yet is just luck. If you would
take the time to understand how Jet works, and how the Access
project is stored in your front end, you'd understand that the
reason you can't use replication for Access objects is the same
reason you can't share a front end -- it's because of the structure
of the way Access stores its front-end object definitions.

Please, compel me to change it, apparently I am too stupid to do
it on my own.

Stop posting advice until you learn something about Jet replication.
Right now, you're posting damaging and erroneous information that
could lead others to make huge mistakes.

Just stop.

 

[Chris O'C via AccessMonster.com]

"Okay, since I am a complete moron who has no value and knows absolutely
nothing"

You're overreacting and exaggerating. Chill out.

"why in over five years (closer to six to count it out) have I not had a
single incident of this?"

You've been lucky, you have a stable network and apparently not too many
complex objects or object synchs during that period. But like a volcano,
when they blow, they blow big time; you just never know when.

"But you and others - who claim to be experts and MVPs - have chosen to tell
me how useless I am"

You're exaggerating. Nobody, including me, told you you were useless. You
gave bad advice because you don't understand Jet replication and we said as
much.

"instead of taking the teaching opportunity to explain to me (and anyone else
reading the post) what the point of having multiple backends in a multi-user
network solution would be?"

You ignored David Fenton's advice and were so busy telling us how confused
Access users were, does it come as any surprise nobody took it as a teaching
opportunity for you? Sounded like you assumed you were the teacher - not the
student - with that SQL Server cert and all.

A typical case for replication is where some of the users will be off the
network periodically or users work at a separate office building but they
need to use the same data as everybody else and don't have a continuous
network connection. When they get back to the main office or connect to the
main office from the other office over the Internet, they synch only the data
through Jet replication. Everybody has their own front end copy (with linked
tables) so their Access sessions are separate from the other users'. There's
no copying Joyce's or Bill's special ad-hoc queries or forms to everybody
else's front end "in case they need it". Only the designated Access
developer builds and distributes the db app to the users to maintain a
carefully designed, normalized db which enforces data integrity.

"How did you learn it? Did you spend day after day reading books and online
articles or did someone teach the basics to you?"

Nobody had the skills or time to teach me. I bought a big 10 lbs. Access
book, read it at night and weekends, and took it to work with me every day.
That taught me the basics, but not enough to do the jobs I needed to do.
After work every day I read online articles and Access posts at Google Groups
for at least 5 months, learning everything I could about developing Access
dbs. I read Allen Browne, Michael Kaplan, John Vinson, John Spencer, David
Fenton, Lyle Fairfield, Tom Wickerath, Brendan Reynolds, 69Camaro, Doug
Steele, Van T. Dinh, Joan Wild, Vanderghast and countless others - and
learned about half what I know about Access from them. I helped merge and
upgrade thousands of dbs at 3 Fortune 100 companies after company mergers so
I learned many tricks from the developers of those dbs. Had to stabilize a
lot of them first. The worst were the replicated Jet dbs, they were often
corrupted because the developers were office clerks, not software developers,
who set up the dbs but didn't research how first. I did a bunch of
experimentation on my own time to see if I could come up with better ways to
do things.

That's how I learned Access. Anybody else could too - except for maybe the
thousands of dbs that needed merging and upgrading at 3 Fortune 100 companies.
Not everybody gets that kind of opportunity. But the most valuable stuff I
learned online in Google Groups and through experimentation and anybody can
do that on their own if they have a notion to.

"Why would that user need a FE and a BE on their laptop in order to replicate
over the data?"

That remote user needs both the FE and BE on their laptop so when they're
away from the office, they can work with data in the app. They'll enter new
data, maybe change some existing data when corrections need to be made at a
client's office, maybe delete some unneeded records. He/she gets back to the
home office, and that valuable data needs to be shared with the other users.
The data in the laptop's back end is synched with the data in the home office
db and now everybody has the same set of updated data. That's the plan at
least. You may have some conflicts to resolve when you synch.

"And if you are not replicating the FE, how do you get and keep an up-to-date
version of the FE on their machine?"

Many developers use Tony Toews's autofe app to automate that. It's free.
Some developers rolled their own versions of Tony's app. Some developers
email their updated front ends to users because there's only a few and
updates are rare.

"Please tell me that your DB's are perfect and ready for full-time, permanent
use the day they implement."

You're upset and using hyperbole to stroke your ego, but your question
doesn't address the topic of Jet replication, does it? But I'll answer
anyway.

Don't design it that way, you'll fail. Take a multi-phase approach. Get a
list of requirements and specifications you and the users agree to. You must
also agree on whether they can ask for changes to the requirements, because
expanding scope during development is the most common reason for a software
project's failure to meet deadlines and overspend the budget. Give the users
the most important features in the first phase. Get feedback on what works
the way they want, what doesn't. Users will come up with new ideas once they
see what's possible and change their minds on some of the original
requirements too. Give the users an upgrade to the first phase to fix the
problems and add some additional important features. Get feedback on what
works, what doesn't. Rinse and repeat until the requirements are met and the
users are satisfied. Try to do this within budget and deadlines.

"Teach me how to get my end users never to ask for enhancements or new pieces.
"

You *WANT* the users asking for enhancements and new pieces. You want them
to ask this from *YOU*, not some other developer. You want to keep working
and paying the bills don't you?

"Then tell me why you choose to make me (or anyone) feel like my solution is
the complete anti-Christ of networked Access databases that will bring about
the end of the database world as we know it? What's up with that?"

You're exaggerating again. You gave very bad advice and you didn't listen to
other posters who tried to correct you in the past 2 years. You think we'd
be silent? No. We just got your attention, we didn't throw you under the
bus or nail you to a crucifix. If you have tire treads on your clothes or
nail holes in your wrists, you didn't get that from here.

"Please, compel me to change it, apparently I am too stupid to do it on my
own."

I think you'll keep it just the way it is. You're a certified SQL Server DBA
and you think that gives you more than enough knowledge about how Jet and
Access work. It doesn't, but you won't believe me in a million years. (Ok
that's exaggeration, but you started it.)

Chris

 

[David W. Fenton]

I suspect that you might be a sock puppet of Aaron's

I retract this, I guess. I did not know that this person had been
around before until Chris O'C posted some earlier instances of *me*
taking him down for offering bad information before.

 

[Archidrb via AccessMonster.com]

Thank you. Actually my plan is not to continue in Access front end
development. The SQL certificate is brand new; I've not even updated my
resume it's so new. I've not done a lot of "work-related" work in SQL so far,
and replication, based only on what I learned in class, still seems different
in SQL. As I develop my SQL skils I plan to change directions to more
management then FE development. If I return to development in FE's, I want
to start learning .NET or other web-based interfaces.

You did not say that I have; I've never claimed to be an Access DBA. My job
title is a BA II. Gathering requirements is what I do all day. I got stuck
with the development end when I made a tool for myself to gather and deliver
reporting to management.

I understand the un-connected user process. It just never made sense to me
why you would split a BE on the end users machine, it seemed to me a front
end was sufficient, then synch up.

If you know, why would Microsoft built the ability to do what I am doing if
its not even supposed to be used? Shouldn't they have changed this ability?
Because this has worked so effectively for me that I have chosen to share
with users. It's for this reason and I've never been any issues with it that
I wasn't understanding the resistance to it on these boards. Based on the
sizes of DB's that you've worked on, I am certain now that our scale for
"many" users is very different. I don't think any one of my three databases
has 30 users.

I might also speculate that because I use so little VBA code (haven't really
learned a lot of VBA coding and SQL is different enough) that I may not have
run into trouble. I use only three generic modules in each database. In the
past I used the wizard to create functionality, but don't much anymore. I
spent some time converting macros to modules to learn coding in a development
database, but my "backup" was so inexperienced and I needed her to know what
was going on, that gave up on that and kept things simple.

One of the three DB's has been submitted for a major overhaul. I've been
back-burnering it for over six months, I just don't want to do it. I guess
this would be a perfect opportunity to change to the distributed method using
a tool like Toews FE Updater. With the SQL backend, it should be a
relatively easy change. It will require a mind-set change by the end users.

 

[Chris O'C via AccessMonster.com]

"I've not done a lot of "work-related" work in SQL so far, and replication,
based only on what I learned in class, still seems different in SQL."

What you had to learn for your cert only touches the surface of the mountain
of knowledge you need to know to be a successful dba.

"If I return to development in FE's, I want to start learning .NET or other
web-based interfaces."

Let's be real here. Learning .net (or other web languages) is way harder
than learning vba. To learn those languages, you need to be a coder. You'll
have to put in at least 50 times more effort than you put into Access vba if
you want to use .net without struggling every time you use it. I'm not
trying to discourage you, I just want to advise you what's ahead. People who
struggle with a task avoid it and put it off as much as possible. Does
anything sitting on the back burner for 6 months come to mind? If you become
a SQL Server administrator and later go back to developing front ends it's
because they'll be dragging you, kicking and screaming.

"If you know, why would Microsoft built the ability to do what I am doing if
its not even supposed to be used? Shouldn't they have changed this ability?"

It's because Access objects are saved as records in tables, just like your
data inputs. Can't separate them out during replication. Best thing to do
is put the Access objects in a separate file from the data and only replicate
the data file if replication is needed.

"I might also speculate that because I use so little VBA code (haven't really
learned a lot of VBA coding and SQL is different enough) that I may not have
run into trouble."

You're right, dbs which are mostly (or all) macros, properties and wizard
generated code aren't the complex dbs that get corrupted the most often. But
even simple dbs aren't immune from corruption.

"I guess this would be a perfect opportunity to change to the distributed
method using a tool like Toews FE Updater. With the SQL backend, it should
be a relatively easy change. It will require a mind-set change by the end
users."

If the users are opening the db front end by a shortcut on their pcs, it
shouldn't be that much different. Click on the shortcut and the front end
opens or copies the latest version to the user's pc and opens that.

Chris

 

[Thorson]

How do change my current .accdb database to .mdb? What if I am using
features only available in 2007 how will those be affected?

 

[Tom van Stiphout]

On Thu, 19 Mar 2009 09:31:01 -0700, Thorson

Office Key > Save As

They won't work.

-Tom.
Microsoft Access MVP

 

[Thorson]

So if I am understanding everything correctly the only way to allow multiple
users to open a "Microsoft Access 2007 Front End" is to allow all users full
permissions (read/write/modify), and if I want to limit certain users'
permissions I must convert the database from .accdb to .mdb and loose the
features of the database that are only available in 2007?

Is there any other way? I would like multiple users to be able to open the
Front End of the database while some users have full permissions and some
users have read only permissions and still maintain all the features of
Microsoft Access 2007.

 

[Chris O'C via AccessMonster.com]

For user level security, the db must be in mdb format. What features are you
using that you want the accdb format for? Sharepoint server? It's still
available for Access 2007 mdbs, but not the advanced features.

And by saying "multiple users opening the front end" you mean each user
opening his/her own copy of the front end, right?

Chris

 

[Thorson]

Yes, each user is opening his or her own copy, I am using an auto-updater
program (http://www.granite.ab.ca/access/autofe.htm).

I had previously understood that I could set up user level security by
setting permissions for the shared folder the back end is in, however this is
preventing multiple users from opening the front end at the same time.

I am not using the Sharepoint Server. The features I would like to keep are
things such as when a animal health diagnosis is entered in a form it
automatically pops up the options for the drug in another field; and if a
drug is entered in that is not in the list a message box allows the users to
select to edit the list.

I am sure these features can be written using code in mdb format, but I have
very little experience with Access and I currently have the database close to
distribution, the ability to share the database is the only problem I am
having. I do not want to have to go back and spend days re-writing code and
re-designing the database.

 

[Tom van Stiphout]

On Tue, 24 Mar 2009 09:13:03 -0700, Thorson

What is your question?

Your previous understanding is incorrect.

-Tom.
Microsoft Access MVP

 

[Thorson]

I have a 2007 Access Database ready to distribute. The back end is in a
shared folder which currently has restrictions on the permissions for
different users.

Before distributing it I wanted to do some checking to make sure things
were working. So while I sent a copy of the front end to another user (who
had read-only permissions). I had that user download the front end to their
desktop. The database works fine for both my self (full permissions) and the
user, however we cannot both open the Front end at the same time. If the
other user has it open and I try to open the front end it gives me read-only
permissions, if I have the front end open and the user tries to open it
Access states that the file is already in use.

I thought the point of splitting the database was to allow multiple users to
access the front end of the database at one time, however it is not working
out that way. I need to know how to fix this problem.
Thanks,

 

[Douglas J. Steele]

The point of splitting the database is to allow multiple users to access the
back end (i.e.: the data) at the same time.

Every user requires a minimum of Read, Write and Create on the folder in
which the back-end database exists (even if they don't have Write permission
on the database itself). Without the ability to update the locking (.LDB)
file, you get the symptoms you're experiencing.

 

[David W. Fenton]

The point of splitting the database is to allow multiple users to
access the back end (i.e.: the data) at the same time.

And each user should have an individual copy of the front end.

 

[Thorson]

So everyone needs read/write/create permissions to the folder the BE is in?
I can't give someone read-only?

In the 2007 database from my understanding I can not create individual users
permissions. Is that correct? So how do I limit the users' permissions?

I'm not sure what the locking (.LDB) file is. I am new to this especially
the lingo.

Thank you for your help!