I think my first project is done!

[Ed from AZ]

I think my first "real" database project is just about done!! Users
can enter data about a particular item in the main data table and add
new item groups and new item IDs within a new group. They can also
review the data for a particular item for a selected day.

At this point, I need to get it out there for data input. There will
be maybe 12-15 people using this, and I can see 6-10 trying to get
into it at the same time on any given day. We all have Access 2007 on
our computers. I've skimmed a bit about splitting a database to keep
the main tables on the server and giving the users their own copies of
the input forms (if that's not entirely accurate, I did just skim
it). Is this the best way to go?

I know I will need to add a report generator later on, but that won't
be for a couple of months. We do have some "gurus" who were too busy
to work on this (and that would have robbed me of learning how to do
this!), and they do have some gee-whiz-bang reports engine that they
say can be added onto this, so I'm not too worried at this point.

And I'm sure my users will find all the chinks and potholes in this,
so repairs and fixes will be needed.

Where do I need to start reading to learn about deploying this into
the real world?

Ed

 

[Douglas J. Steele]

In my opinion, all applications should be split into a front-end (containing
the queries, forms, reports, macros and modules), linked to a back-end
(containing the tables and relations). For multi-user applications, only the
back-end goes onto the server: each user has his/her own copy of the
front-end, ideally on his/her hard drive.

 

[Jeff Boyce]

Ed

Congratulations!

Yes, "splitting" the application (back-end on a server all can 'see', a copy
of the front-end on each/every user's PC) is a very good idea. And when you
split the data to the back-end, and re-link from a front-end to that data,
consider using the UNC naming convention (\\Servername\folder\...) rather
than named shares (e.g., "P:/") ... if any of your users don't have their
"P:/" share mapped to the same location, their application breaks!

As far as deploying, consider using something like Tony Towes' automatic
front-end refresher to help keep your users' copies in sync with the latest
version. To actually get the app out there, it might be as simple as
copying it to each user's PC.

When I deploy an app to a number of (in-house) users, I always set up a
local folder (C:\SomeFolder\) to hold the front-end. That way, if the beer
truck gets me, the folks who follow will be able to find it, no matter which
user they visit.

I have a word of caution ... in the flush of success of your first app, you
are now facing the most dangerous application you will build ... your
second<g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ed from AZ]

Thanks, Doug and Tony. I appreciate the input.

I have a word of caution ... in the flush of success of your first app, you
are now facing the most dangerous application you will build ... your
second<g>!

When I deploy an app to a number of (in-house) users, I always set up a
local folder (C:\SomeFolder\) to hold the front-end.

So set the front end in a folder accessible to all users, and then
send them the link to where to get it with the instructions to copy it
to their own computer rather than open from that location. Got that.

And when you
split the data to the back-end, and re-link from a front-end to that data,
consider using the UNC naming convention (\\Servername\folder\...) rather
than named shares (e.g., "P:/") ...

I assume the re-link is established during the splitting process, and
so when each user copies the front end over the lnk will know where to
go - yes? So if the user tries to open the front end from their
machine and gets some kind of "I can't find it" error, then it's
likely to be a network error which denies them access to the server
containing the back end - yes?

Okay, then - I will *make a back-up copy!!* of my project and split
it. I'll let y'all know if it goes up in smoke!

Ed

 

[Jeff Boyce]

Ed

I may have given an inaccurate impression.

NO, do not use a single copy of the front-end, shared by all users. I was
describing building a place for a copy of the front-end to live on EACH
user's PC.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks, Doug and Tony. I appreciate the input.

I have a word of caution ... in the flush of success of your first app,
you
are now facing the most dangerous application you will build ... your
second<g>!

When I deploy an app to a number of (in-house) users, I always set up a
local folder (C:\SomeFolder\) to hold the front-end.

So set the front end in a folder accessible to all users, and then
send them the link to where to get it with the instructions to copy it
to their own computer rather than open from that location. Got that.

And when you
split the data to the back-end, and re-link from a front-end to that data,
consider using the UNC naming convention (\\Servername\folder\...) rather
than named shares (e.g., "P:/") ...

I assume the re-link is established during the splitting process, and
so when each user copies the front end over the lnk will know where to
go - yes? So if the user tries to open the front end from their
machine and gets some kind of "I can't find it" error, then it's
likely to be a network error which denies them access to the server
containing the back end - yes?

Okay, then - I will *make a back-up copy!!* of my project and split
it. I'll let y'all know if it goes up in smoke!

Ed

 

[Clif McIrvin]

Thanks, Doug and Tony. I appreciate the input.
<snip>

So set the front end in a folder accessible to all users, and then
send them the link to where to get it with the instructions to copy it
to their own computer rather than open from that location. Got that.

That's the idea ... except if they just start to use the shared copy
with actually copying it you can run into issues somewhere down the
road.

Ask those guru's you mentioned to help you set up a DOS batch file that
will copy the FE onto the local hard drive (and even create the shortcut
for you!)

There are also discussions of this on this forum you can likely find
with a Google search -- I've had fairly decent luck searching here from
Google Groups ... though there have been comments that it doesn't always
work very well.

YMMV

 

[Ed from AZ]

Okay - now I'm feeling very stupid!! I compiled the project,
compacted the database, and ran the Make MDE. This is built in A2007
and saved as A2002-2003 compatible format. So I open the front end -
and it looks exactly like when I opened the file for development! I
expected to see only my main form, not the whole list of all the
forms, modules, queries, tables, etc.

Obviously I have missed a few very important steps in the transition
form a work in progress to a finished product. I went through several
on-line tutorials, but couldn't find what I thought I should be
looking for. Then again, it could have been right there and I just
didn't know what I was reading.

If anyone could spare a drop-kick in the right direction, I would be
very grateful.

Ed

 

[Jeff Boyce]

Ed

When your (un-MDE) version opens, what do you see? The "database" container
(i.e., "behind the curtain")?

If so, that's what your users will see when they launch it. And if you
convert it to an .MDE, that is STILL what you (and your users) will see when
launching it.

If you want a "main menu" screen/form to appear, you have to build one, then
you have to tell Access to display that one on startup.

Have you done these already?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ed from AZ]

Thank you, Jeff, for helping!!

When your (un-MDE) version opens, what do you see?  The "database" container
(i.e., "behind the curtain")?

Yes - the task pane that allows me to open any table, query, form,
etc.

If you want a "main menu" screen/form to appear, you have to build one, then
you have to tell Access to display that one on startup.

Have you done these already?

No, I have not. This would be a switchboard? I looked in Help for
"startup", and all it gave me that I could understand was how to open
a 2000-20003 file in 2007.

Where do I go?

Ed

 

[Jeff Boyce]

Ed

You can use Access' built-in Switchboard Manager ... but you will find you
have greater control and flexibility if you create a new form and put
command buttons on it to do what you want.

Part of the difficulty most folks face in using Access is realizing that it
is NOT like Word or Excel. Most anyone who has hand-written a document or
added columns of numbers understand what (if not how) Word and Excel work.
They are a lot like 'bookcases'.

MS Access, on the other hand, is a relational database. Honestly, now, how
many people understand relational databases in every day life?! MS Access
is much more of a tool, like a power saw. It isn't a bookcase, it gets used
to BUILD bookcases!

That said, YOU are responsible for building the bookcase so that your every
day users only have to pay attention to what they need to do. Otherwise,
you'll be requiring them to become well-trained MS Access users ...
something that few of them need or care to do.

One of my favorite sayings is "Easy ... is HARD!". Creating a user-friendly
(and user-proof) graphical user interface is not easy, but it is the only
alternative to handing your users a power saw and expecting them NOT to cut
off fingers...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you, Jeff, for helping!!

When your (un-MDE) version opens, what do you see? The "database"
container
(i.e., "behind the curtain")?

Yes - the task pane that allows me to open any table, query, form,
etc.

If you want a "main menu" screen/form to appear, you have to build one,
then
you have to tell Access to display that one on startup.

Have you done these already?

No, I have not. This would be a switchboard? I looked in Help for
"startup", and all it gave me that I could understand was how to open
a 2000-20003 file in 2007.

Where do I go?

Ed