Access vs SQL

I an trying to create a policy for when to use Access as to using SQL. I
feel that in the proper environment it is adequate to use Access as to using
SQL. I may be wrong, I just have a problem with creating a 10mb SQL
database that will not be used that much. I have looked at the comparison
chart and understand the system differences.
TIA
CDRemember that they are fundamentally different. Access is
like dBASE or some other file-system level database. SQL
Server is a relational database, and is a bit more
robust. If Access works for you, great, but wouldn't it
be easier, say, to get a version of SQL Server, and create
DBs (they can be as big or small as you want), give the
right access to the right people, and have one central
point of management? That's consolidation 101.
With Access, you'll have to deal with multiple install
points, etc.
If it was me, I'd go SQL, give people connectivity, and
then let them do what they want.|||Access is a wonderful product and is useful in many cases. Do you have a
specific question? Also, have you considered using the MSDE option within
Access?
One large reason to consider SQL over Access even in cases that Access may
be appropriate is upgrades. Will a reasonable percentage of the applications
ever become large enough to move to SQL Server? It's MUCH easier to move an
Access app based on MSDE (rather than Jet) to SQL Server.
I find that most companies in a mixed Access/SQL environment often end up
needing to move some of the Access DB's to SQL over time...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>|||I love Access its a great tool, we use it my company.
However anything that requires more than a couple of users
connecting to it we use SQL Server as Access gets rather
slow with multiple connects.
My bottom line...
For packages that do not require any REALLY complex
processing or multiple users, use Access, otherwise use
SQL Server.
Peter
MCDBA
>--Original Message--
>I an trying to create a policy for when to use Access as
to using SQL. I
>feel that in the proper environment it is adequate to use
Access as to using
>SQL. I may be wrong, I just have a problem with creating
a 10mb SQL
>database that will not be used that much. I have looked
at the comparison
>chart and understand the system differences.
>TIA
>CD
>
>.
>|||CD,
If it is a single-user database to be used by someone on their own
machine, then recommend Access. If it is a multi-user database that
could potentially grow over time, then use SQL Server. A database server
set up for all the little databases in your organisation should be easy
for you to administer. Administering several Access files is just a huge
headache to me.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CD wrote:
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>|||and..
Access (JET) can't be backed up whilst other people are using it, SQL can.
So if you need to take reliable backups without kicking users off (eg a web
app) Access isn't the right tool for the job.
Access (JET) also doesn't have any write-ahead logging, so corruption is far
more likely than in SQL.
Access with MSDE (instead of JET) is a great idea as you get the best of
both worlds.
Regards,
Greg Linwood
SQL Server MVP
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>

Access vs SQL

I an trying to create a policy for when to use Access as to using SQL. I
feel that in the proper environment it is adequate to use Access as to using
SQL. I may be wrong, I just have a problem with creating a 10mb SQL
database that will not be used that much. I have looked at the comparison
chart and understand the system differences.
TIA
CD
Access is a wonderful product and is useful in many cases. Do you have a
specific question? Also, have you considered using the MSDE option within
Access?
One large reason to consider SQL over Access even in cases that Access may
be appropriate is upgrades. Will a reasonable percentage of the applications
ever become large enough to move to SQL Server? It's MUCH easier to move an
Access app based on MSDE (rather than Jet) to SQL Server.
I find that most companies in a mixed Access/SQL environment often end up
needing to move some of the Access DB's to SQL over time...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>
|||CD,
If it is a single-user database to be used by someone on their own
machine, then recommend Access. If it is a multi-user database that
could potentially grow over time, then use SQL Server. A database server
set up for all the little databases in your organisation should be easy
for you to administer. Administering several Access files is just a huge
headache to me.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CD wrote:
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>
|||and..
Access (JET) can't be backed up whilst other people are using it, SQL can.
So if you need to take reliable backups without kicking users off (eg a web
app) Access isn't the right tool for the job.
Access (JET) also doesn't have any write-ahead logging, so corruption is far
more likely than in SQL.
Access with MSDE (instead of JET) is a great idea as you get the best of
both worlds.
Regards,
Greg Linwood
SQL Server MVP
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>

Access vs SQL

I an trying to create a policy for when to use Access as to using SQL. I
feel that in the proper environment it is adequate to use Access as to using
SQL. I may be wrong, I just have a problem with creating a 10mb SQL
database that will not be used that much. I have looked at the comparison
chart and understand the system differences.
TIA
CDAccess is a wonderful product and is useful in many cases. Do you have a
specific question? Also, have you considered using the MSDE option within
Access?
One large reason to consider SQL over Access even in cases that Access may
be appropriate is upgrades. Will a reasonable percentage of the applications
ever become large enough to move to SQL Server? It's MUCH easier to move an
Access app based on MSDE (rather than Jet) to SQL Server.
I find that most companies in a mixed Access/SQL environment often end up
needing to move some of the Access DB's to SQL over time...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>|||CD,
If it is a single-user database to be used by someone on their own
machine, then recommend Access. If it is a multi-user database that
could potentially grow over time, then use SQL Server. A database server
set up for all the little databases in your organisation should be easy
for you to administer. Administering several Access files is just a huge
headache to me.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
CD wrote:
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to usi
ng
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>|||and..
Access (JET) can't be backed up whilst other people are using it, SQL can.
So if you need to take reliable backups without kicking users off (eg a web
app) Access isn't the right tool for the job.
Access (JET) also doesn't have any write-ahead logging, so corruption is far
more likely than in SQL.
Access with MSDE (instead of JET) is a great idea as you get the best of
both worlds.
Regards,
Greg Linwood
SQL Server MVP
"CD" <mcdye1@.hotmail.nospam.com> wrote in message
news:%23y5edRKdEHA.3380@.TK2MSFTNGP12.phx.gbl...
> I an trying to create a policy for when to use Access as to using SQL. I
> feel that in the proper environment it is adequate to use Access as to
using
> SQL. I may be wrong, I just have a problem with creating a 10mb SQL
> database that will not be used that much. I have looked at the comparison
> chart and understand the system differences.
> TIA
> CD
>

Access SubQuery Help Needed.....................

Don't know if this is the proper newsgroup to post to. Sorry if not.
Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).
I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.
From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, .... I will need a few more
columns from each of the tables for the final report.
I've tried many variations of the Select statement without success.
Such as:
Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber
Looking for a solution,
HexmanFROM using subquery not a table(s) is not supported in SQL92.
<code lang="SQL" type="AirCode">
SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
FROM IMast i INNER JOIN THist p ON i.part = p.wopart
WHERE i.pcode = "CAST"
AND ((thisdate - wodate) < 91)
</code>
Regards John
"Hexman" <Hexman@.binary.com> wrote in message
news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
4ax.com...
> Don't know if this is the proper newsgroup to post to. Sorry if not.
> Can't figure this one out. Using Access 2003. I have 2 tables, IMast
> (table of part info) and THist (part transaction history).
> I need to select all parts that have a product code = "CAST" and list
> the last 10 history transaction if they are within 90 days of the run
> date.
> From the IMast table I need the columns: part, descr, pcode. From the
> THist I need: wonumber, wodate, wocust, .... I will need a few more
> columns from each of the tables for the final report.
> I've tried many variations of the Select statement without success.
> Such as:
> Select part, desc, pcode
> from (Select top 10 wonumber, wodate, wocust
> from THist
> where (pcode = "CAST") and (part=wopart) and ((thisdate -
> wodate) < 91)
> order by part, wodate, wonumber
> Looking for a solution,
> Hexman
>|||John,
An Order By clause is also needed, so the Top 10 will return the appropriate
data.
Kerry Moorman
"John Griffiths" wrote:

> FROM using subquery not a table(s) is not supported in SQL92.
> <code lang="SQL" type="AirCode">
> SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
> FROM IMast i INNER JOIN THist p ON i.part = p.wopart
> WHERE i.pcode = "CAST"
> AND ((thisdate - wodate) < 91)
> </code>
> Regards John
> "Hexman" <Hexman@.binary.com> wrote in message
> news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
4ax.com...
>
>

Access SubQuery Help Needed.....................

Don't know if this is the proper newsgroup to post to. Sorry if not.
Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).
I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.
From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, .... I will need a few more
columns from each of the tables for the final report.
I've tried many variations of the Select statement without success.
Such as:
Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber
Looking for a solution,
HexmanHexman,
What are the common columns for the 2 tables? If you are trying to get join
the 2 tables, you have a use a join statement such as this. Then put your
condiction in the where column.
SELECT [Orders].[OrderID], [Orders].[CustomerID], [Orders].[EmployeeID] FROM
Employees INNER JOIN Orders ON [Employees].[EmployeeID]
=[Orders].[EmployeeID];
Sam
"Hexman" wrote:

> Don't know if this is the proper newsgroup to post to. Sorry if not.
> Can't figure this one out. Using Access 2003. I have 2 tables, IMast
> (table of part info) and THist (part transaction history).
> I need to select all parts that have a product code = "CAST" and list
> the last 10 history transaction if they are within 90 days of the run
> date.
> From the IMast table I need the columns: part, descr, pcode. From the
> THist I need: wonumber, wodate, wocust, .... I will need a few more
> columns from each of the tables for the final report.
> I've tried many variations of the Select statement without success.
> Such as:
> Select part, desc, pcode
> from (Select top 10 wonumber, wodate, wocust
> from THist
> where (pcode = "CAST") and (part=wopart) and ((thisdate -
> wodate) < 91)
> order by part, wodate, wonumber
> Looking for a solution,
> Hexman
>