Lock vs Split Database

[HotRodSue]

I've created my first database for a customer. In the process I got stuck a
couple of times and received great help on this forum.

I can "Lock" the Database as an ACCDE file to prevent the user from making
changes. Also, I can "Splitting" the Database, and protect the front-end
database by saving as ACCDE.

The database is single user, and tracks fuel useage of company vehicles.
There are 3 input forms, and reports based off of these forms. However,
there are 2 tables that the customer may need to access. One is a Vehicle ID
table, when the company gets a new vehicle they'd need to add it. The other
is a Category ID table, where they may need to add a new category.

What's the best way to protect the database before handing it over to my
customer? Is it possible and safe for me to instruct them on how to make
changes to the 2 tables listed abover -or- should that be a change made by
me as needed?

Thanks in advance for the help.

 

[Albert D. Kallal]

I've created my first database for a customer. In the process I got stuck
a
couple of times and received great help on this forum.

I can "Lock" the Database as an ACCDE file to prevent the user from making
changes. Also, I can "Splitting" the Database, and protect the front-end
database by saving as ACCDE.

Your subject title hints that the issue of locking up the database and
splitting are opposed to each other or somehow related to each other, and
they're not really. The main reason why you split is to allow you to issue
updates to those many uses of your application, since if the data part and
forms parts are together in one file, then how are you going to issue
updates or even bug fixs to the particular user of the software? So one
great reason why we split is to allow you to develop the next great version
of the software, add new features, or perhaps even make bug fixes etc. Then,
when you've added those new reports, bug fixes, or whatever, then you simply
issue a new front end and their data part is NOT touched by you.

The above issues is really much seperate from that of hiding the tables and
building a nice interface for your user(s).

What's the best way to protect the database before handing it over to my
customer? Is it possible and safe for me to instruct them on how to make
changes to the 2 tables listed abover -or- should that be a change made
by
me as needed?

You'll have to explain what you mean by changes? If you mean additions like
adding records and editing records in those tables, then you simply do what
every other application developer does and provide some forms and menu
options to edit those tables in a form. In other words if those users need
to edit some data in a table, then provide the correct user interface and
forms to allow them to view and edit that data...

As a general rule I do lock out the users ability to bypass my forms and
directly edit the tables and see "behind" the scenes of the application I
delivered to them. This is quite easy to accomplish for your front end, and
you don't need any code to hide all of the access interface and allow your
users to see ONLY what you want them to see in your application.

For the backend what I do is use the put in a small auto exec macro when
launched that simply displays a MsgBox command that says that you do not
have permissions to edit this database, and when they click OK on the
message box the macro simply shuts down the application. This keeps out
about 99% of the casual users out of trying to open up the back end database
and editing it directly.

You can try downloading an access 2007 sample database I have in the
follwoing like (I've just been playing around and testing-- it's in rough
shape but it does show you how to hide the interface without having to write
code code).

Access 2007, hidden interface example.
http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm

 

[HotRodSue]

Thanks for the clarification. Your explanation on the importance of
splitting was a great help. I’ll be sure to perform the split, I’ve got
instruction on that.

What I mean by changes is exactly what you thought, I need to allow the
customer to add records. Forgive my inexperience, I’m still learning. Your
solution to create a form and menu option to edit the table in a form is just
what I need. Would the form require a Query? By menu option do you mean that
I’d add a button to my main menu to open the form?

Here you mention lock out. Do you lock the front-end by saving it as an
ACCDE file?

As a general rule I do lock out the users ability to bypass my forms and
directly edit the tables and see "behind" the scenes of the application I
delivered to them. This is quite easy to accomplish for your front end, and
you don't need any code to hide all of the access interface and allow your
users to see ONLY what you want them to see in your application.

Your idea for the auto exec macro for the back end sounds perfect. I
believe I can handle that. Thanks for the link to your page. I’m checking
that out now.

Again, many thanks for your kind help.

 

[Albert D. Kallal]

What I mean by changes is exactly what you thought, I need to allow the
customer to add records. Forgive my inexperience, I'm still learning.
Your
solution to create a form and menu option to edit the table in a form is
just
what I need. Would the form require a Query? By menu option do you mean
that
I'd add a button to my main menu to open the form?

Yes your right on the money here (all counts).

However there's no significant requirement
or advantage to base a form on a query as opposed to basing that form
directly on the table. This is merely a choice you can make, but it not a
necessity. Perhaps one of the most common reasons as to why we base a form
on a query as opposed to the underlying table table is that in the query we
can set the sort order of the data. So we might order the data in the form
by last name or whatever criteria we feels appropriate for the given
application.

So if you need/want that form to display/edit data in a particular order,
it's probably better to build a query that orders to date the way you want
and then base your form on that query in place of the table. In both cases
you'll be editing the same data, the query is just a view or looking glass
into that table and doesn't affect how data gets updated. So, a query or a
table reflect the same data and same changes to the table. A query is just a
looking glass into that table...

Here you mention lock out. Do you lock the front-end by saving it as an
ACCDE file?

The above is just a starting step. The reason why we create a accDE file is
the end user cannot flip the form into design mode and start messing around
with your great artistic creation that you've just made. So this is just one
step of many that prevents users from being able to modify your designs.
Keep in mind this also means that YOU cannot modify the accDE file either!
This is why you must always be certain that you keep the original copy of
the accDB file. You'll always work on the original file, not the accDE file.
That accDe is strictly for distribution to your end users since once this
file is made it cannot be undone nor modified by you or your users...

 

[HotRodSue]

Your posts have helped me tremendously, I've been working all week fine
tuning my database. Thank you for sharing such detailed info.

Now I'm ready to make a back up, and split the database. Thanks for the
reminder to keep an original copy of the accDB file.

The above is just a starting step. The reason why we create a accDE file is
the end user cannot flip the form into design mode and start messing around
with your great artistic creation that you've just made. So this is just one
step of many that prevents users from being able to modify your designs.

The accDE file is just one step of many that prevents users from modifying
my design. I'm reading about Setting Startup Options, and Password
Protection. What other steps do you take?

Thank you,

Susan