How to get parameter passed to SQL query from access

[bmeyynospamnospam]

I am new to sql and very familiar with access. I am using a very
large database(130M records) in ms sql2000 and think I need to front
end it with access for reports and forms, etc..

I have some questions:
1) Is there a way in SQL to prompt a user for input at the running of
a query like the [what is your name] construct in an access query?

2) I can't seem to create calculated fields in a view in sql. In
access I put "total:=hours*rate" and a new column would be created
which would contain the total.

3) I normally would create a form in access from which the access
queries would pull their variables and then insert those variables in
the queries or reports, etc. How would this be accomplished using
sql.

4) Lastly and probably most basic. How does a "real" sql developer
create reports and forms to interact with the enduser? I am using
access as a front end, but am open to suggestions.

Thanks,
Brad

[Simon Hayes]

1. No - MSSQL is a pure server, and it has no concept of prompts, GUIs,
forms or other client-side interfaces. So you need to provide your own
front end, in Access, ASP, .NET or whatever other toolset you prefer.

2. Like this:

create view dbo.MyView
as
select hours, rate, hours * rate as 'total'
from dbo.MyTable

3. As above, you have to provide your own front end, so you would get
the values in the front end, and pass them to the back end. The best
way is usually to encapsulate your SQL code in stored procedures, and
pass the values as parameters to the procedure.

4. It probably depends on the application domain, the tools and
processes you already have etc. If your main requirement is reporting,
you might want to consider using a reporting tool, rather than building
your own functionality. SQL Server Reporting Services are free if you
have an MSSQL licence. But using Access as a front end certainly seems
to be a fairly common solution.

Simon