Converting a SQL Server Web Application to Access?
Over a year ago I wrote an article, Commerce Starter Kit For Access 2000, where I converted the Microsoft ASP.NET Commerce Starter Kit from SQL Server to an Access database. Since then I have recieved a lot questions on converting SQL Server web applications, including other Microsoft Starter Kits, to Access. When you consider just one difference between SQL Server and Access you start to get an idea of how difficult this can be. SQL Server stored procedures can accomplish multiple tasks and Access stored procedures (queries) can only accomplish one. This means that to convert some SQL Server stored procedures to Access will require multiple queries in the Access database and multiple calls to that database to accomplish the same thing. This is not desirable in most cases. I chose to convert the Commerce Starter Kit because the database structure and stored procedures were not as complex as the other Starter Kits.
However, I realize that some developers are only able to use Access. That is why I felt the need to write the article. For those wanting to convert a SQL Server Web Application to Access the best advice I have is don't. Instead write an application built for Access. If you still feel the need to convert, try to move some of the tasks in the stored procedures to ASP.NET code to limit the database activity.
If you need some Jet SQL help try these links:
http://devguru.com/Technologies/jetsql/quickref/jet_sql_list.html
http://msdn.microsoft.com/library/en-us/dnacc2k/html/acfundsql.asp