Web Front End

[AnthonyGerrard]

I have an access FE to a sql db BE. I am looking to moving to a web front end. much functionality is done in SQL, views, sproc, functions SSRS etc.

But my basic data manipulation is done through bound access forms.

Would moving these to unbound forms using sprocs be a good move as way of migrating to web front end eventually. (ie make the eventual migration less painful)

Any advice welcome, particularly on what development tools langauages, methodology I should use.

I post this here as its very much an access FE migration and hopefully theres a bit of experience out there.

Thanks

 

[Rx_]

Kind of a general question, let me ask a few general questions about it.
From the description the appication consist of of Data, Logic (business rules), and Presentation.
The logic is basically embedded in the Forms.
Some logic might reside in the DB (in form of query, Stored Procs, or other functionality).

Typically, we think of a disconnected front-end as calling some form of pure logic or busines rule layer. Of course, this from my experience is rarely achieveable in a very small database (small being under 10 GB of Data).

Moving data from the Access DB to SQL Server and changing over to Linked Tables is the fairly easy part. In fact, the ODBC Drivers (my favorite is SQL Server Native Client 11.0) actually communicate very well by design with Access 2007 and later.

The bigger question is the Web for the FE.
First of all, VBA doesn't work with any web solution short of Citrix.
Citrix is actually the full-blown Access delivered over the most narrow bandwidth by network or web. It is called an ICA Client. This is commonly used for companies with VPN or other network. The product can work on Windows, Apple or any other platform supported by ICA.

Any of the multiple Access Web solutions will require a complete rewrite of all the business rules. There are basically only two events. The MACROs won't begin to cover what VBA can do.

One application soltuion I saw at the last Access seminar had a VBA verbose version for internal users. Then it had the most basic web forms for look-up, simple new record entry, and simple add-new.
It is two applications both tied to the same back-end AZURE SQL Server DB.
Example, the home office fulfills a complex order using Access VBA, SQL Server and all the tools. The Delivery person has the web version, when they make the delivery, they search for the customer, find the order and put a check box in the "Delivered" column. For Access Web, KISS is the rule.

I did see one of the most advanced Access Web applications that had a ton of business rules. The programmer found out that the Macro modules have some limit (It might have been 32K, I forget).
So, they had to organizise a cascade of Macro1, Macro2, ...
They had the backing to make this happen. It must be a logistical nighmare for applications that are based on Compliance, Regulatory, or other types of intense business rules.

 

[AnthonyGerrard]

Kind of a general question, let me ask a few general questions about it.
From the description the appication consist of of Data, Logic (business rules), and Presentation.
The logic is basically embedded in the Forms.
Some logic might reside in the DB (in form of query, Stored Procs, or other functionality).

Typically, we think of a disconnected front-end as calling some form of pure logic or busines rule layer. Of course, this from my experience is rarely achieveable in a very small database (small being under 10 GB of Data).

Moving data from the Access DB to SQL Server and changing over to Linked Tables is the fairly easy part. In fact, the ODBC Drivers (my favorite is SQL Server Native Client 11.0) actually communicate very well by design with Access 2007 and later.

The bigger question is the Web for the FE.
First of all, VBA doesn't work with any web solution short of Citrix.
Citrix is actually the full-blown Access delivered over the most narrow bandwidth by network or web. It is called an ICA Client. This is commonly used for companies with VPN or other network. The product can work on Windows, Apple or any other platform supported by ICA.

Any of the multiple Access Web solutions will require a complete rewrite of all the business rules. There are basically only two events. The MACROs won't begin to cover what VBA can do.

One application soltuion I saw at the last Access seminar had a VBA verbose version for internal users. Then it had the most basic web forms for look-up, simple new record entry, and simple add-new.
It is two applications both tied to the same back-end AZURE SQL Server DB.
Example, the home office fulfills a complex order using Access VBA, SQL Server and all the tools. The Delivery person has the web version, when they make the delivery, they search for the customer, find the order and put a check box in the "Delivered" column. For Access Web, KISS is the rule.

I did see one of the most advanced Access Web applications that had a ton of business rules. The programmer found out that the Macro modules have some limit (It might have been 32K, I forget).
So, they had to organizise a cascade of Macro1, Macro2, ...
They had the backing to make this happen. It must be a logistical nighmare for applications that are based on Compliance, Regulatory, or other types of intense business rules.

Yes - access web FE are way to limited in what we need them to do, so we are looking at ASP.net - in Visual Studio environment.

 

[SQL_Hell]

Hello,

In answer to your question, yes I think making everything in access unbound would be a good place to start. Then when it comes round to actually replacing access with asp.net will be able to concentrate purely on coding the front end.

 

[AnthonyGerrard]

Thanks both.

 

[Rx_]

for ASP.NET, yes, unbound to be sure.
Would also suggest using module functions (business layer) often.
In addition, expand the SQL Server use of Views and Functions.
Anything done in SQL Server will be less to do on the conversion effort.

This means avoid using a query on the Access side to connect to a form.
Instead, create a view on SQL Server and use it as a linked table from the SQL Server side.