Front End/Back End

[Rick Brandt]

Alright so lets say each computer has its own MS Access, with a fully
functional .MDE Front-End.
How does one (the developer/me) get to the back-end?

You apparently don't understand what the "back end" is. In a split app the
back end is the file that contains the tables that is shared on the network.
That's it. There should be nothing in the back end except for the tables
that the front end files are linked to.

If you need to make design changes to the back end (add new tables, modify
existing tables, etc.), then you would need to kick all users out of their
respective front end files so you can open the back end exclusively and
apply the changes. Then you would give the users a new copy of their front
end files that includes changes that recognize the revisions you just made
to the back end.

Do I modify the menu bars and then compile the .MDE? Or do I create
the .MDE after finished the database, and then modify the menu bars
while in the .MDE? If it is the latter, how would the developer get
to the back-end again?

You keep a copy of the MDB that the MDEs are created from and that is what
you work on. When you are ready to distribute a new version of the front
end to the users you use your MDB to create new MDEs that you then
distribute.

Also, how would all these link so they are working off of the same
database? I realize I can use the tools that Tony has to keep
"refreshing" it once a link is created, right?

The front end contains "links" to the tables in the back end (or data file)
that is shared on the network. The links are like pointers. While the
front end will "act like" it has tables it will really be modifying the
tables in the back end file whenever a user edits or inserts records.

Tony's tool is a way to automatically distribute new front end files. It has
nothing to do with the link between the front end files and the back end
file.

 

[Garret]

Ok, I made a .MDE file from my original database. Perhaps I didn't
understand you, but I edited some records in the .MDB, and the .MDE did
not change. Vice Versa, I edited some records in the .MDE and the .MDE
did not change. I still don't see how they would "link" then...

I did notice a feature Tools > Database Utilities > Database Splitter
Is this what I have to use?

 

[Garret]

I also noticed that the .MDE file cannot edit Forms and Records (which
is good), but they can create and edit Tables and Queries (not so
good). I realize that this problem can be taken care of with Security
and by hiding these through menu bars, but it seems like other problems
could potentially stem from this?

 

[Rick Brandt]

Ok, I made a .MDE file from my original database. Perhaps I didn't
understand you, but I edited some records in the .MDB, and the .MDE
did not change. Vice Versa, I edited some records in the .MDE and
the .MDE did not change. I still don't see how they would "link"
then...

Linking does NOT involve the MDB and MDE. Linking is between the
application file (front end) and the data file (back end) and is a
completely different concept.

I did notice a feature Tools > Database Utilities > Database Splitter
Is this what I have to use?

Yes. Delete the MDE made earlier and use the splitter on your MDB. You
will end up with two files (both MDBs), one with just tables in it. Place
that data file in the desired network share location. Then open the front
end and use the Linked Table Manager to refresh the links so that they are
looking at the new location of the data file. You will need to check all of
the tables as well as the "prompt for new location" option.

When you click [OK] you will get a browse dialog that you use to browse to
the location of your data file on the network. Navigate to this location by
going through Network Neighborhood - Entire Network rather than by using a
network shortcut or mapped drive letter on your PC. That will produce a UNC
path that will work for all users on your network.

Now you should find that when you edit data in your application MDB that you
will see those edits reflected in the tables on the back end file on the
network. If you then create an MDE from your MDB it will have the same
links to that data file on the network. You can then make as many copies of
that MDE as you need for your users.

 

[Garret]

Update: I figured out how to link the tables using File > Get External
Data > Link Tables. However, one more thing:

1) In the backend (which is where I did the File > Get External Data >
Link Tables) there are now copies of all my tables...("Gage
Calibration", "Tools", "Inspection",..."Gage Calibration1", "Tools1",
"Inspection1"...ect), which I assume takes up loads more room, and the
original tables are not even going to be accessed now if the ones with
the "1" suffix are the linked ones. I also tried this with the
frontend, and it made copies of all the tables in the frontend instead.
Hmmm?

 

[Garret]

Hang on Rick, going to try your last message, I didn't see it before I
posted my last message.

 

[Garret]

That worked great! I thank you so much for all your help. My company
isn't ready to put the Database up yet, so I will check back and follow
those directions further at a later time. I might have questions
later, so don't be surprised to see this topic resurface =P. I think
for now though, I'm all set.
You are the man.

 

[Garret]

I'm curious now; why would someone use Runtime if they could easily
split the database and make .MDEs like the way I am going to do it?

 

[Garret]

I'm curious now; why would someone use Runtime if they could easily
split the database and make .MDEs like the way I am going to do it?

Also, lets say after the database was split and front ends were
distrubuted. Months go by, and boss decides that a new form needs to
be added, and everyone should use that form now. He also decides he
would like to change a report's design around, as well as create a few
new reports. Where would he do all this?

 

[Rick Brandt]

I'm curious now; why would someone use Runtime if they could easily
split the database and make .MDEs like the way I am going to do it?

The two have nothing to do with each other.

Splitting is more flexible for the developer because he can work on his copy
and then distribute when finished by the simple act of replacing a file. It
also improves performance and reduces corruptions in multi-user situations.

Using an MDE ensures that the code can never get uncompiled and provides
security for code-based objects.

The Runtime is for situations where the target user doesn't own a copy of
Access. Neither splitting nor making an MDE resolves the problem of the
user not owning Access.

 

[Garret]

That makes good sense. I'm pretty sure I can get Access to all my
Users so I can just split the database, as that seems the easier and
better way to do it anyway.

What about the issue of creating/updating more forms/reports as I
mentioned before?

 

[Garret]

Also, lets say after the database was split and front ends were
distrubuted. Months go by, and boss decides that a new form needs to
be added, and everyone should use that form now. He also decides he
would like to change a report's design around, as well as create a few
new reports. Where would he do all this?

 

[Lynn Trapp]

I'm curious now; why would someone use Runtime if they could easily

split the database and make .MDEs like the way I am going to do it?

If a large company has many Access users who don't need to do ANY kind of
development (my company is an example of this), then the company can save a
pretty big bundle of money by not having to by an Access license for all of
those users.

Also, lets say after the database was split and front ends were
distrubuted. Months go by, and boss decides that a new form needs to
be added, and everyone should use that form now. He also decides he
would like to change a report's design around, as well as create a few
new reports. Where would he do all this?

Hopefully, the boss won't do all of that, but will call the development
team to do it for him. If you create an MDE, then you need to work from the
MDB and, after the changes are made, you redistribute a new MDE with the new
forms and reports.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Garret]

Let me see if I have this straight -
1) After my database is complete (all tables/forms/reports/ect) are
completed, I split the database.
2) Then I use the Front-End one to customize menu bars and whatnot to
create the User Interface I am looking for. The back end one goes in a
network location.
3) I now use this completely modified front-end to make a MDE, which
then is distributed to all my users.
4) What I am left with now is a backend, and .MDEs for all the users,
with a .MBD that sits in a folder somewhere (this is all customized
too, as it was just used to build the .MDEs).

So now where would I go to create/update more forms/reports? I can't
use this .MBD because it is already modified to be in User Interface.

 

[Rick Brandt]

Let me see if I have this straight -
1) After my database is complete (all tables/forms/reports/ect) are
completed, I split the database.
2) Then I use the Front-End one to customize menu bars and whatnot to
create the User Interface I am looking for. The back end one goes in
a network location.
3) I now use this completely modified front-end to make a MDE, which
then is distributed to all my users.
4) What I am left with now is a backend, and .MDEs for all the users,
with a .MBD that sits in a folder somewhere (this is all customized
too, as it was just used to build the .MDEs).

So now where would I go to create/update more forms/reports? I can't
use this .MBD because it is already modified to be in User Interface.

Once more...

You take your existing unsplit file and you split it. You put the data file
on the network and you leave the MDB on your computer. The MDB is a front
end with links to the tables in the back end. It is IDENTICAL to the MDE
files you will give your users except that it has not been converted to an
MDE.

You make ALL design changes to the MDB. This means the creation of queries,
forms, reports, menus, code modules, etc.. When the MDB works exaclty like
you want it to you make MDEs from it and give them to your users.

When additional changes are required you open the MDB, make those changes
and test them. When you are satisfied with the changes you make new MDEs
and you distribute them to your users who replace their old MDEs with the
new ones. I am usually working on changes in my copy of the MDB almost
immediately after distributing new MDEs because my main app is always being
modified.

I don't understand when you say "I can't use this .MBD because it is already
modified to be in User Interface.". The MDB/MDE _is_ the user interface.
If you mean because once you have set the startup properties and disabled
the bypass key, etc. that the MDB is now not usable for further design work
that is simply incorrect.

You either just re-enable the bypass key on the MDB or simply never disable
it on the MDB copy of the file. You can easily create your MDE, set the
startup properties on it, disable the bypass key, and then make more copies
of it to give your users. You would thus never do those final steps to the
MDB copy. Personally I set the startup properties and just use the Shift
key on my development file and I often don't bother with the bypass key
setting. If I do I run that code in my startup routine so holding shift
still prevents it on my development file and if I accidentally let it run it
is easily enough undone with one line of code.

 

[Garret]

You take your existing unsplit file and you split it. You put the data file
on the network and you leave the MDB on your computer. The MDB is a front
end with links to the tables in the back end. It is IDENTICAL to the MDE
files you will give your users except that it has not been converted to an
MDE.

You make ALL design changes to the MDB. This means the creation of queries,
forms, reports, menus, code modules, etc.. When the MDB works exaclty like
you want it to you make MDEs from it and give them to your users.

Yes I understand this.

When additional changes are required you open the MDB, make those changes
and test them. When you are satisfied with the changes you make new MDEs
and you distribute them to your users who replace their old MDEs with the
new ones. I am usually working on changes in my copy of the MDB almost
immediately after distributing new MDEs because my main app is always being
modified.
I don't understand when you say "I can't use this .MBD because it is already
modified to be in User Interface.". The MDB/MDE _is_ the user interface.
If you mean because once you have set the startup properties and disabled
the bypass key, etc. that the MDB is now not usable for further design work
that is simply incorrect.

No, what I'm saying is that after I design the .MDB to be in User
Interface and make the .MDEs, I essentially have two copies of the same
database (visual wise), except with limited capabilities on the .MDE.
But both appear to be the same from the outside. I want to know how
(since I now have a .MDB that is the user interface) to be able to
design new and old forms and reports. The User Interface that I design
will have no place where forms and reports can be modified or created,
because theres no reason that regular users would need to do this.

Is the only way to do this to: go to the MDB afterward, turn the
database window back on, do the modifications that need to be done,
turn the database window off, readjust any of the menubars as to set
back to User Interface mode, and then rebuild and distribute MDEs?

You either just re-enable the bypass key on the MDB or simply never disable
it on the MDB copy of the file. You can easily create your MDE, set the
startup properties on it, disable the bypass key, and then make more copies
of it to give your users. You would thus never do those final steps to the
MDB copy. Personally I set the startup properties and just use the Shift
key on my development file and I often don't bother with the bypass key
setting. If I do I run that code in my startup routine so holding shift
still prevents it on my development file and if I accidentally let it run it
is easily enough undone with one line of code.

Maybe I'm just having a memory lapse, but I don't know what a bypass
key is? Is this another term for something else that we've discussed?

 

[Rick Brandt]

No, what I'm saying is that after I design the .MDB to be in User
Interface

This is where you lose me. All of the settings in startup that hide the
normal Access interface from the user are completely bypassable as well as
reversable. When you create custom menus and toolbars "for the user" those
are stored in the file. The toolbars that are built into Access are just
that, built into Access. They are not part of the file so there is no way
to remove them from the file permanently.

Lets start with just the stuff in the Startup settings.

You open your MDB and you set all of those properties so that the normal
Access interface is hidden when you open the file. This is the interface
your users will see when they open their MDE file. Now if you open that
file while holding the shift key you get the "normal" Access interface and
that is what you use when doing development on your MDB.

So each time you need to make design changes you open the form while holding
shift and do whatever you need to do. If you want to test it "as a user"
you close the file and reopen it without holding shift and then you will see
what the user will see. Once you are finished you can make an initial MDE
and on that file run the code that disables the shift key. You make copies
of this file for your users, but YOUR MDB file still has the use of the
Shift key so you can continue to use that for opening the file for future
development.

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.
Lastly, how would one go about disabling the shift key?

 

[Garret]

Bingo. The shift key thing was exactly what I was looking for. What I
meant before was that it was impossible to make changes since the MDB
looks exactly like the MDE, therefore having no options to create or
design any forms or reports. I thought I would have had to build the
interface all the way back to normal, do the changes, then build it all
the way back to User Interface, but the shift key saves loads of time.
Thank you so much, you have no idea how much I've learned from you.
Lastly, how would one go about disabling the shift key?