Access and MSSQL

[Chris Holmes]

Hi,

I'd like to do some brain-picking. I was tasked with updating an
existing Access database. I'm to move the data to MSSQL, keep forms
and reports in Access, and expose some of the functionality through
ASP. My background is almost exclusively in MSSQL w/front end in
VB/ASP/C# - I've only worked with Access by tweaking existing projects
(data always stored in MDB, never outside).

Since I'll have two front ends, my first instinct is to keep
funcionality away from GUI. In this case I thought about writing
stored procedures, and have them handle both data issues and security.

Question 1: How would I go about creating a form in Access based on
stored procedures? The same way it's done in VB for example - writing
code in event handlers and use ADO or DAO to reach the database? Or
can forms be somehow bound to SPs?

Question 2: Can Access forms be bound to views? As you can see I'm
trying to not to have to grant users right directly to tables.

Question 3: What are opinions on how to get from Access to MSSQL?
Using forms bound to linked tables, SPs, views, combination of those?
This project is relatively small, so I'm not too concerned about
performance; I'd rather be able to do it quickly and have an easy way
of moving UI completely to ASP in the future, if the need be.

Sorry if this has been discussed in the past, pointers to such
discussion will be appreciated.

TIA,
Chris

 

[Norman Yuan]

See comments inline.

Hi,

I'd like to do some brain-picking. I was tasked with updating an
existing Access database. I'm to move the data to MSSQL, keep forms
and reports in Access, and expose some of the functionality through
ASP. My background is almost exclusively in MSSQL w/front end in
VB/ASP/C# - I've only worked with Access by tweaking existing projects
(data always stored in MDB, never outside).

Since I'll have two front ends, my first instinct is to keep
funcionality away from GUI. In this case I thought about writing
stored procedures, and have them handle both data issues and security.

Good decision.

Question 1: How would I go about creating a form in Access based on
stored procedures? The same way it's done in VB for example - writing
code in event handlers and use ADO or DAO to reach the database? Or
can forms be somehow bound to SPs?

Depending on what type of Access front-end you are going to use, Access with
linked tables via ODBC,(*.mdb) or Access project via ADO (*.adp), there
could be some difference. I cannot say on linked table to SQL Server' cause
I never worked that way. Access project, though, is specific designed as SQL
Server front-end environment since Access2K. If you use SQL Server2K, you'd
better use Access2002/2003, there is some issues on Access2K working with
SQL Server2000, since Access 2K was released before SQL Server2K. With
Access project, you can use SP as data source for forms, reports.

Another thing to consider is the Access DB you inheritted. If there is
already lot of code in the Access front-end (*.mdb), you may want to reuse
it with minimum changes, then you may stay with linked tables, instead of
Accesss project. Some code changes may be necessary.

Access has lot of built-in data-bound controls and there is lot of "tricks"
to a VB programmer, who intends to write lot of code behind Access forms
(the VB way). In Access, you can often do the same thing as you do in VB
without single line of code or a lot less code.

Question 2: Can Access forms be bound to views? As you can see I'm
trying to not to have to grant users right directly to tables.

Yes, you can. You do it the same way as usual: give users permission to SPs
or Views. I tend to use SP exclusively.

Question 3: What are opinions on how to get from Access to MSSQL?
Using forms bound to linked tables, SPs, views, combination of those?
This project is relatively small, so I'm not too concerned about
performance; I'd rather be able to do it quickly and have an easy way
of moving UI completely to ASP in the future, if the need be.

UI in Access (*.mdb or *.adp) is useless in ASP.