Access & SQL Server Express

[Guest]

Hi,

I have seen some posts in regards to a higher level of security if the
access database is migrated to an application such as SQL Server Express.I
have an existing access database(VER.2000) which is about a year old,
contains three tables and 10megs of data.This database is shared with ten
users on a FE/BE design but the IT dept can not provide folder security.Can
access be migrated to a more secure program such as SQL Server Express?

 

[Albert D.Kallal]

You are right on in all counts here.

You should note that there is a free edition of sql server on the office cd
for use with ms-access. In fact, the last 3 versions of access have included
this free database engine ( 2000, 2002, 2003 all shipped with this free
version of sql server). The problem is that you have to use a access ADP
project to manage that free sql server (many of us do not want to use a ADP
project). Or, better is to grab a copy of the enterprise tools for the "big"
corporate version of sql server, and use them to manage that free database
system on the office cd. This has been my approach, and the "enterprise
tools" are REALLY nice and work with that desktop edition of sql server
(MSDE). You can get ALL of the great sql server tools (enterprise manager)
for free, and those tools work with this free database engine. Even better,
is you now get sql server experience using the standard industry tools. So,
this kind of setup works very well with a standard mdb file for the front
end, and using linked tables to sql server for the back end). Do note that
this desktop edition of sql server (MSDE) is performance throttled to about
5 "jobs" at the same time. So, 5-10 users does work .

However, you can't beat the feature set of the new sql server express, as
that is NOT performance limited.

I JUST I got a copy of sql server express running. I had not time to play
with it, and see how well it works with ms-access. However, I since this
database engine does is not performance throttled...it sounds like the best
choice.....

 

[Guest]

Yes, i did see the free download and i also went to a web site for a software
called SSW UPSIZING PRO which is also a free download and claims to migrate
access with one click as long as i have small amount of users and tables.In
your experience,will the use of this software (SQL SERVER EXPRESS) let me
have total control of the folder in which the BE resides so i can prevent
unwanted users from viewing tables while still have the multiple users i want
to continue working with no problems?

Thanks

 

[Albert D.Kallal]

.In your experience,will the use of this software (SQL SERVER EXPRESS) let

me

have total control of the folder in which the BE resides so i can prevent
unwanted users from viewing tables while still have the multiple users i
want
to continue working with no problems?

Yes, you can remove 100% of the permissions to that folder, and uses cannot
even browse to that folder.

Remember, with sql server, it is the SERVER that opens the files!! For
example, when you purchase a book on Amazon.com, can you grab other
customers files? If you hit the re-set button on your computer, are files on
www.amazon.com going to be damaged? So, use a true client to sever means
that the SERVER system does all of the file open, loading, and reading of
the data (you can bet that the server on www.amazon.com has to open those
files in a directory somewhere...but you NEVER get to do that). The server
then SENDS that data to the client. So, your users can not copy those files
from the directory, no more so then you trying to go and copy customer files
from www.amazon.com. Using a server based system means you are NOT OPENING a
file across a network connection.

So, the "server" certainly needs permission to open those files in that
directory, but all communication occurs through the server system..and NOT
the file system.

so i can prevent

unwanted users from viewing tables while still have the multiple users i
want
to continue working with no problems?

The above does has absolute nothing to do with hiding of the tables. If your
ms-access application does not hide the tables now, then moving things to
sql server will do absolute nothing to "hide" the tables. If a user clicks
on a table to view it, the table will display. This works if you use JET,
Oracle, or sql server expression.

Moving to sql server will NOT CHANGE your application. What it will change
is that users can't open (or copy) the files sitting in the directory on the
server. However, the operation, and use of your application will NOT change.
If you are being lazy and displaying the tables in your application, or are
not hading the database window etc, then moving to sql server will NOT
change this fact.

A developer will simply as a mater of course of action hide all of the
ms-access interface. In fact, you can hide all of the interface, and not use
any code, or even use security for the database. All of the settings you
need are in the tools->startup.

If you want to see an example of "quick sample" database that hides all of
the ms-access stuff, then try downloading the 3 file here:
http://www.members.shaw.ca/AlbertKallal/msaccess/DownLoad.htm
(grab the one "access interface hidden).

Try running the above one. And, then take a look at the tools->startup
options to see how this is done.

Note that this sample does use some menus, and I think you should consider
using menus. I talk about menus, and have some great screen shots of
ms-access menus here:

http://www.members.shaw.ca/AlbertKallal/Articles/UseAbility/UserFriendly.htm

So, if you are just trying to hide the tables....you can set this up with a
few mouse clicks, and you don't need sql server for that!!

However, if you need to secure the data, and not allow the files to be
copied, then sql server is the ONLY way to go. So, don't confuse hiding of
the interface with that of security, or sql server. Even when you browse
www.amazon.com, you can cut and paste information you see. If a user can
view a table in your application, and then select it, they then can paste it
into excel or whatever.

Security, and that of simply setting up a proper interface in ms-access are
very much different issues. For sure, if you don't allow a table view, then
users can't cut and paste from that table, but just like shopping for books,
what you display can be copied, and useally by cut and paste.

So, you have to keep in mind what it is you are tyring to secure here.
However, no code, or even ms-access scirity, or migration to sql server is
needed to keep the users out of the table views.

For example, to keep people out of my Back ends, I simply have a startup
form that runs and says you don't have permissions. They click ok, and I
issue a "quit" command. This usually fools them enough...and keeps them
out...