Clarification about databases please.

[Susan]

There is MySQL and then MS SQL? There is also Access. What is the MS SQL?
Then how does Access fit in? Is Access an online database too but also has
reporting features?

If you strictly used MS products. You would create Webpage using FrontPage,
ASP which is similar to PHP? There is ASP.Net which I am not sure about
what it is but must work with ASP? Then for the database you would use what?
Access? What is MS SQL? For reporting you would use Access?

Thank you for information. Wish I had person to talk to here but don't know
anyone.

 

[molsonexpert]

MySQL, Microsoft Access and Microsoft SQL Server are all relational database
systems. MySQL is open source (i.e. it's free - for the most part), Access
is a desktop db solution, either combined with Office suite, or on its own,
and SQL server is a corporate or enterprise db solution. In general, Access
is the only of the three that includes a front-end/end-user development
portion, the others, you would need another product for forms and report
dev.

ASP and PHP are server side scripting technologies, used for creating
dynamic web pages, for example. ASP.NET is as well.

For more thorough info, I suggest hitting Microsft's website, and/or
googling the above terms.

hth.

 

[Susan]

Access is a desktop db solution? Do you mean that it is not used for online
database? Is online database converted to desktop Access database and then
printed reports generated?

I know a little about MySql. Yes, it is free but it appears that the main
problem is generating report information from MySQL. Yes, you can do queries
and see results online but to actually generate printed reports, there does
not appear to be any direct way. You can print query results. I posted
question about generating printed reports in MySql forum and my question was
moved. I asked if there was other software that was used to generate
printed reports. Unfortunately, no one replied.

 

[molsonexpert]

desktop may have been the wrong term. I used it because it is included in
Office.

By online, I assume you mean web-based. I believe you can create a web-based
layout directly with Access, but most web authouring tools (FrontPage,
Dreamweaver) allow you to connect to Access databases, and this would
probably be the preferred choice.

Again, with MySQL, you would need another tool to create your front end app,
and that includes forms and reports. In fact, there is an Access driver for
MySQL, so you could develop a front-end in Access that works with data in a
MySQL backend. The same goes for SQL Server.

 

[Pat Hartman \(MVP\)]

Access is an all-in-one tool whereas as molsonexpert has said, both MySQL
and MS SQL require other tools to develop forms and reports. They ONLY
provide data access. Access includes Jet which is what causes all the
confusion. Jet is a database engine and serves a similar purpose to MySQL
and MS SQL although it is more limited. Access, the development
environment, can be used to create forms and reports for ANY database for
which an ODBC driver is available and that includes MySQL and MS SQL and Jet
among others. When an Access application uses Jet tables, it can be
self-contained and that earns it the name of "desktop database". With MySQL
and MS SQL you would usually need a database server (unless you were just
running a copy for yourself on your local PC).

Jet can be used as the back end database for a web application created with
ASP or FrontPage, etc. but I don't recommend it unless you know for a fact
that your database access is very limited.

 

[Armen Stein]

On Wed, 26 Sep 2007 14:02:45 -0400, "Pat Hartman \(MVP\)" <please no

Access is an all-in-one tool whereas as molsonexpert has said, both MySQL
and MS SQL require other tools to develop forms and reports. They ONLY
provide data access. Access includes Jet which is what causes all the
confusion. Jet is a database engine and serves a similar purpose to MySQL
and MS SQL although it is more limited. Access, the development
environment, can be used to create forms and reports for ANY database for
which an ODBC driver is available and that includes MySQL and MS SQL and Jet
among others. When an Access application uses Jet tables, it can be
self-contained and that earns it the name of "desktop database". With MySQL
and MS SQL you would usually need a database server (unless you were just
running a copy for yourself on your local PC).

Jet can be used as the back end database for a web application created with
ASP or FrontPage, etc. but I don't recommend it unless you know for a fact
that your database access is very limited.

All great information from both Pat and molsonexpert.

In addition:

- MS SQL Server now includes SQL Server Reporting Services as a free
add-on, so it now has some built-in interface capability. Designing
reports with Reporting Services takes some learning, but is very
robust and capable. We've used it in several projects.

- MySQL is free, but so is the Express version of MS SQL Server. It
is a limited-capacity version of SQL Server but uses the same code. In
most scenarious the Express version is is much more capable than
Access/Jet. I think it's one of the best deals out there right now.
For many small projects it would be a great way to start with SQL
Server Express and upgrade to a paid version later if necessary.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Pat Hartman \(MVP\)]

Hi Armen,
What do you recommend as a management tool for the Express version of SQL
Server? or does it come with SQL Management Studio?
Pat

 

[Susan]

I have a MySQL database, I can do queries, etc on the table I have created.
But in order to generate a printed report, I will need to export the table
(CSV format), convert it to Access database and run reports. Are you saying
if the server had MySQL, MS Access, ODBC, then MS ACCESS could be used to
run reports accessing data from MySQL database and the reports could
generated online? It would not matter what person had loaded locally on
their PC but if were set up for access on the website, they could run and
print a report?

Thank you everyone for your responses.

 

[Albert D. Kallal]

I have a MySQL database, I can do queries, etc on the table I have
created. But in order to generate a printed report, I will need to export
the table (CSV format), convert it to Access database and run reports.

You can do the above, but normally, you would have ms-access *connect* to
that database, and then you build he report.

ms-access is able to connect to Sql server, My sol, or the default database
engine called "JET" (that the one your using by "default" in access).

You can go into direct sql view in ms-access, and use it like my-sql to
build queries. However, why go back a 100 years to a old command style
system.


So, you can use ms sql server, or mysql as the database part with ms-access.
Ms-access is NOT the database, but a tool hat lets you build forms, reports,
etc. (you can't build forms and reports with JET, or ms sql server, or
Mysql..they are the data only engine part).

Are you saying if the server had MySQL, MS Access, ODBC, then MS ACCESS
could be used to run reports accessing data from MySQL database

Yes correct.

and the reports could generated online?

On line? what you do mean? ms-access has nothing to do with the web, no more
then word, or excel, or other parts of office do.

If you mean by "on-line" that many users could connect to the sql server,
and use ms-access forms to edit and update data, and also run reprots, then
yes, reports and data can be shared by all. However, each user is still
going to have a copy of your application (ms-access) on their machines. The
sql server (which ever one you use) can be anywhere in the world, but you
have to have the person setting up the server provide secure access to that
server (unles you want everyone on the planet to be able to steal the data).

so, if you mean "on line" that your sharing data to different people in
different locations, then yes. If you mean on-line like a web site...no,
that has no relation to ms-access. Your users will need ms-access to edit
data, and view the reports you build. But,t he data can most certainly
reside in sql server.

As mentioned, if you want a system that has web based reporting and a
reporting designer similar to ms-access, you can use Sql reporting services
(assuming your using MS Sql server....you seem to hint that you using other
then that..and thus you in the wrong newsgroup...you have to ask for help in
the appropriate group for your particular system your using...

 

[Susan]

Thank you for your information. I am still trying to understand Access as
it relates to a website package. For instance

One package has this:

WINDOWS SERVER 2003 0.75Gb Space / 15Gb Traffic 3 Domains + Sub Domains
ASP.NET, PHP, PERL MySQL, MS Access, ODBC UNLIMITED Email Boxes H-Sphere
Control Panel $5.00/mo or $50.00/yr

The other package has the following:

UNIX LINUX 0.75Gb Space / 15Gb Traffic 3 Domains + Sub Domains PHP, PERL,
CGI, SSI MySQL Database UNLIMITED Email Boxes H-Sphere Control Panel
$5.00/mo or $50.00/yr

What is the MS Access when it is on a server? Does it mean with the other
package you could not use MS Access to connect to the MySQL database?

 

[Armen Stein]

On Wed, 26 Sep 2007 14:49:18 -0400, "Pat Hartman \(MVP\)" <please no

Hi Armen,
What do you recommend as a management tool for the Express version of SQL
Server? or does it come with SQL Management Studio?
Pat

There is an Express version of SQL Server Management Studio. It does
all the basic things you would need it to do with SQL Server Express.

It's free.

http://www.microsoft.com/downloads/...AE-4BD1-4E3D-94B8-5A0F62BF7796&DisplayLang=en


Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Thank you for your information. I am still trying to understand Access as
it relates to a website package. For instance

One package has this:

WINDOWS SERVER 2003 0.75Gb Space / 15Gb Traffic 3 Domains + Sub Domains
ASP.NET, PHP, PERL MySQL, MS Access, ODBC UNLIMITED Email Boxes H-Sphere
Control Panel $5.00/mo or $50.00/yr

The other package has the following:

UNIX LINUX 0.75Gb Space / 15Gb Traffic 3 Domains + Sub Domains PHP, PERL,
CGI, SSI MySQL Database UNLIMITED Email Boxes H-Sphere Control Panel
$5.00/mo or $50.00/yr

What is the MS Access when it is on a server? Does it mean with the other
package you could not use MS Access to connect to the MySQL database?

Typically it means that you will be allowed to place an Access MDB
file on the server, and use an ODBC driver to link to it for data. It
may also include a DSN to help web pages to link to the MDB. It does
NOT include running the Microsoft Access application on the server.

I know, it's confusing. "Access" is commonly known as both:

1) a Windows application that runs on your PC and comes with Office
Pro. It can be used to build forms, reports, etc. with data from any
modern database. It also includes a database engine called Jet right
in the box.

2) a file that contains data and other Access objects. This file
usually has an MDB extension. It can be used by web pages and other
programs without the Microsoft Access application.

The server package above is using the 2nd definition.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Susan]

Thank you very much. I think this will help me now.
http://www.ucl.ac.uk/is/mysql/access/
I have Office 2007 which does have the Access like you said.

 

[David W. Fenton]

I know, it's confusing. "Access" is commonly known as both:

1) a Windows application that runs on your PC and comes with
Office Pro. It can be used to build forms, reports, etc. with
data from any modern database. It also includes a database engine
called Jet right in the box.

2) a file that contains data and other Access objects. This file
usually has an MDB extension. It can be used by web pages and
other programs without the Microsoft Access application.

The server package above is using the 2nd definition.

That is, what they mean is:

You can use a Jet MDB to store your data.

Access is simply not involved in any way, shape or form on the
server.

 

[Susan]

Thank you very much! I downloaded the mysql-connector-odbc-3.51.20 driver.
Set up the DSN and connected to the MySQL database on the website. I was
able to open MS Access and import a table and now have the table in Access.
I did have problems with an Access is denied error #1044 but it cleared up
after I downloaded the driver from a different source and repeated the
steps. This opens a whole new world for working with data. Now to learn
about forms, and reports.

Yes I have problems with terminology since words have multiple definitions.
Access with mentioned in website package and forms (which after working with
<form> tags) made me think of website. I believe "form" in Access must mean
form like a paper form. I will learn but am so grateful to newsgroups and
the internet for obtaining answers. Now to buy a book on Access!

Thanks,
Susan