why access, not my sql or oracle?

[bml337 via AccessMonster.com]

why access, not my sql or oracle?

I work for a small consulting firm (2ppl) which handles a burdensome amount
of documents. We are at the point of needing to manage all of our client
files automatically. I was toying with the idea of hiring a db developer to
create a custom db. However, a friend of mine gave me a crash course in
access and i picked it up pretty quickly. I am confident enough, and think
i can develop what i need with the aid of groups such as this, and help from
my friend. Now i have to ask... since i am still green to db development why
access... why not my sql, ms sql or oracle? from what i have read there are
security and speed issues.

few specs on my db.

* Two trusted people will have access to the db
* this db will not be accessible to clients
* will manage upwards of 100 reports
* also client billing
* client status

so i guess what i am asking is should i bother to build it in access? If not,
is access similar to my sql or oracle or are they a totally different animal.

If access is really not that secure... then whats the purpose?



thanks for any feedback.

 

[John Vinson]

so i guess what i am asking is should i bother to build it in access? If not,
is access similar to my sql or oracle or are they a totally different animal.

Access is really an application development environment. It includes
not one but *two* database engines: JET, the database engine in .mdb
files, and (with the newer versions) MSDE, which is simply SQL/Server.

An Access application can link to any ODBC compatible database server
- Access/JET databases, SQL/Server, MySQL, Oracle, DB/2, Visual
FoxPro, dBase, take your pick (they all have their own advantages and
disadvantages). SQL/Server, MySQL, and Oracle are all very capable
database engines - but, by themselves, they don't have tools for
building user interfaces (forms and reports). You can use Visual
Basic, C++, etc. to do so, or pay for expensive tools to frontend
them- but Access is inexpensive and has a very capable form and report
design feature.

If access is really not that secure... then whats the purpose?

Is security the one and only criterion by which you evaluate
databases? Does develoment effort, user friendliness, portability,
etc. etc. play any role? If security is the one and only important
criterion then, fine; use SQL/Server, lock the security down tight,
and pay a good programmer big bucks to write a customized executable
interface. Your call!

John W. Vinson[MVP]

 

[bml337 via AccessMonster.com]

thanks for the info!

security is not the end all be all of my database. However, it does play a
part and i would just like to know the shortcoming are of access. from what
i read, i believe this is the application i should use to develop. If, in
the future i feel that i need a more robust db i can migrate over to sql or
oracle, correct?

thanks for your input... very greatly appreciated.

Brandon

 

[John Vinson]

thanks for the info!

security is not the end all be all of my database. However, it does play a
part and i would just like to know the shortcoming are of access. from what
i read, i believe this is the application i should use to develop. If, in
the future i feel that i need a more robust db i can migrate over to sql or
oracle, correct?

If you develop the application carefully, with an eye to moving it to
client/server in the future, then yes, Access makes an excellent
prototyping platform. Check out

http://www.granite.ab.ca/access/sqlserverupsizing.htm
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html
http://www.mvps.org/access/resources/index.html

for some possibly useful references.

John W. Vinson[MVP]

 

['69 Camaro]

Hi, Brandon.

since i am still green to db development why
access... why not my sql, ms sql or oracle?

Microsoft Access is an "all-in-one" tool, whereas the other database engines
require additional, specialized skills to make the "complete" package. Access
provides easy access to the database engine (Jet for Access 2003 and earlier,
ACE for Access 2007) for data storage and data integrity. Access also provides
fairly easy connectivity to other database engines for data storage, data
integrity and security. Access provides automated tools (wizards) to build the
presentation layer, or "front end" (data entry forms, reports for data displays,
custom coding in modules, et cetera). An expert in Access can be expected to
know how to use (and can teach you how to use) all of these tools in Access.

The other database engines may or may not provide similar tools besides the data
storage feature. MySQL provides only data storage. It doesn't have tools to
build the front end. Oracle provides many additional reporting tools and
presentation tools in addition to data storage, but these are so highly
specialized that it usually takes a minimum of two or three Oracle professionals
to build an application and manage the data. Microsoft SQL Server now has
additional reporting tools and custom coding (T-SQL, CLR, and GUI managers)
available, but again, these are so highly specialized that it usually takes a
minimum of two or three SQL Server professionals to build an application and
manage the data.

Microsoft Access is great for beginners, because one doesn't have to be a
sophistocated computer user to learn how to build a reliable, effective database
application. Access can handle additional complexity as your skills grow, and
then allow you to migrate the back end to an even more complex database engine
without having to start over from scratch when your organization's needs
increase and your workforce expands.

Managing the other database engines compared to managing Jet is more complex by
an order of magnitude. Oracle is even more complex than the others, so increase
the complexity by another order of magnitude. Unless you plan to get formal
training, don't expect to "jump in" and just "wing it" with Oracle. With the
other database engines you can "jump in" without formal training, but expect to
make so many mistakes that you shouldn't plan to use real data until you have at
least six months to two years of experience, so formal training is also a good
idea.

If access is really not that secure... then whats the purpose?

If you need to secure the data, store it in a client/server database engine, not
Jet. You can still use Access to quickly and easily build the front end to the
data for the presentation layer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[bml337 via AccessMonster.com]

very insightful... thanks so much!

Hi, Brandon.

since i am still green to db development why
access... why not my sql, ms sql or oracle?

Microsoft Access is an "all-in-one" tool, whereas the other database engines
require additional, specialized skills to make the "complete" package. Access
provides easy access to the database engine (Jet for Access 2003 and earlier,
ACE for Access 2007) for data storage and data integrity. Access also provides
fairly easy connectivity to other database engines for data storage, data
integrity and security. Access provides automated tools (wizards) to build the
presentation layer, or "front end" (data entry forms, reports for data displays,
custom coding in modules, et cetera). An expert in Access can be expected to
know how to use (and can teach you how to use) all of these tools in Access.

The other database engines may or may not provide similar tools besides the data
storage feature. MySQL provides only data storage. It doesn't have tools to
build the front end. Oracle provides many additional reporting tools and
presentation tools in addition to data storage, but these are so highly
specialized that it usually takes a minimum of two or three Oracle professionals
to build an application and manage the data. Microsoft SQL Server now has
additional reporting tools and custom coding (T-SQL, CLR, and GUI managers)
available, but again, these are so highly specialized that it usually takes a
minimum of two or three SQL Server professionals to build an application and
manage the data.

Microsoft Access is great for beginners, because one doesn't have to be a
sophistocated computer user to learn how to build a reliable, effective database
application. Access can handle additional complexity as your skills grow, and
then allow you to migrate the back end to an even more complex database engine
without having to start over from scratch when your organization's needs
increase and your workforce expands.

Managing the other database engines compared to managing Jet is more complex by
an order of magnitude. Oracle is even more complex than the others, so increase
the complexity by another order of magnitude. Unless you plan to get formal
training, don't expect to "jump in" and just "wing it" with Oracle. With the
other database engines you can "jump in" without formal training, but expect to
make so many mistakes that you shouldn't plan to use real data until you have at
least six months to two years of experience, so formal training is also a good
idea.

If access is really not that secure... then whats the purpose?

If you need to secure the data, store it in a client/server database engine, not
Jet. You can still use Access to quickly and easily build the front end to the
data for the presentation layer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

why access, not my sql or oracle?

[quoted text clipped - 22 lines]

thanks for any feedback.

 

['69 Camaro]

You're very welcome. Good luck.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

very insightful... thanks so much!

Hi, Brandon.

since i am still green to db development why
access... why not my sql, ms sql or oracle?

Microsoft Access is an "all-in-one" tool, whereas the other database engines
require additional, specialized skills to make the "complete" package. Access
provides easy access to the database engine (Jet for Access 2003 and earlier,
ACE for Access 2007) for data storage and data integrity. Access also
provides
fairly easy connectivity to other database engines for data storage, data
integrity and security. Access provides automated tools (wizards) to build
the
presentation layer, or "front end" (data entry forms, reports for data
displays,
custom coding in modules, et cetera). An expert in Access can be expected to
know how to use (and can teach you how to use) all of these tools in Access.

The other database engines may or may not provide similar tools besides the
data
storage feature. MySQL provides only data storage. It doesn't have tools to
build the front end. Oracle provides many additional reporting tools and
presentation tools in addition to data storage, but these are so highly
specialized that it usually takes a minimum of two or three Oracle
professionals
to build an application and manage the data. Microsoft SQL Server now has
additional reporting tools and custom coding (T-SQL, CLR, and GUI managers)
available, but again, these are so highly specialized that it usually takes a
minimum of two or three SQL Server professionals to build an application and
manage the data.

Microsoft Access is great for beginners, because one doesn't have to be a
sophistocated computer user to learn how to build a reliable, effective
database
application. Access can handle additional complexity as your skills grow, and
then allow you to migrate the back end to an even more complex database engine
without having to start over from scratch when your organization's needs
increase and your workforce expands.

Managing the other database engines compared to managing Jet is more complex
by
an order of magnitude. Oracle is even more complex than the others, so
increase
the complexity by another order of magnitude. Unless you plan to get formal
training, don't expect to "jump in" and just "wing it" with Oracle. With the
other database engines you can "jump in" without formal training, but expect
to
make so many mistakes that you shouldn't plan to use real data until you have
at
least six months to two years of experience, so formal training is also a good
idea.

If access is really not that secure... then whats the purpose?

If you need to secure the data, store it in a client/server database engine,
not
Jet. You can still use Access to quickly and easily build the front end to
the
data for the presentation layer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

why access, not my sql or oracle?

[quoted text clipped - 22 lines]

thanks for any feedback.

 

[Klatuu]

FYI,
MSDE is no longer available. It is now known as SQL Server 2005 Express.

 

[David W. Fenton]

security is not the end all be all of my database. However, it
does play a part and i would just like to know the shortcoming are
of access.

Do you have secure Word documents on your network? If so, how do you
manage security on those? Likely you put them in a location on your
network that has access limited only to those who should be able to
open the documents.

Access/Jet security works exactly the same way.

If it's insufficient, you can keep your Access front end and store
the actual data tables in SQL Server.

You have far, far more options if you develop your application in
Access.

 

['69 Camaro]

Hi, Jerry.

Depending on the version, bells, and whistles, Oracle is about $40,000 for
one license. So if you don't already have Oracle, there's a steep cost there.

The price tag doesn't need to be that high. It can be $0 if one doesn't need
the Enterprise Edition and all the bells and whistles.

---------------------------------------------
Free:
---------------------------------------------

-- For your database engine:

Oracle Database 10g Express Edition:

http://www.oracle.com/technology/products/database/xe/index.html

-- For database administration (and specifically the DBA track):

Oracle SQL Developer:

http://www.oracle.com/technology/software/products/sql/index.html

Quest's TOAD (free trial):

http://www.quest.com/toad_for_oracle

Quest's TOAD (freeware -- limited features and expires every 60 days):

http://www.toadsoft.com/lic_agree.html

List of the differences between the TOAD freeware and the commercial
version:

http://www.quest.com/events/listdetails.aspx?ContentID=2229

-- For database application development (and specifically the Oracle developer
track):

Oracle JDeveloper 10g:

http://www.oracle.com/tools/jdev_home.html

Oracle BPEL Process Manager:

http://www.oracle.com/technology/products/ias/bpel/index.html

---------------------------------------------
Free for developers:
---------------------------------------------

-- For database application development (and specifically the Oracle developer
track):

Oracle Developer Suite (including JDeveloper, Oracle Designer,
Oracle Forms Developer, Oracle Software Configuration Manager,
Oracle Reports Developer, Oracle Discoverer, Oracle Warehouse
Builder, and Oracle Business Intelligence Beans):

http://www.oracle.com/tools/tools_editions.html

Oracle TopLink and Application Development Framework:

http://www.oracle.com/tools/toplink_adf.html

Oracle .NET Development Tools:

http://www.oracle.com/technology/tech/dotnet/index.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.