Access DB with SQL Server Back End

Question

I have an old Access application that has a lot of code in forms and reports. The database is getting too large and I am thinking of moving the back end to SQL Server.

My requirements are as follows:

1. The DB needs to be multiuser and the users (3-5) will need to log in over the web
2. I would prefer not to re-write the forms and reports in ASP or some other web front end.

When I think about my choices, I see them as:

1. Have an Access ADP front end and allows remote log-in to the server where it is stored. Not sure if it is possible for 2 users to simultaneously log in

2. Distribute an ADP front end to the users, but I am not sure if it is possible to connect to a SQL Server back end over the internet, and the network traffic may be an issue.

3. Any other solution?

I appreciate all help.

• u

Answer 1

The only secure way to expose a sql server back end to and access front end over the internet is over a vpn. Unless you rewrite queries and other sql in the access code to execute queries on the sql server, sql server is probably going to transfer entire tables to the acccess front end forprocessing and filtering which will be slow over a vpn. If you really don't want to have to recode i think rdp acess is going to work best.

Answer 2

I work on a clients MDB FE on a VPN/ADSL connection to their server. It's slightly more sluggish in some areas than working in their office. But it does work well. So I see no need to convert the app to ADP format.

Note that they've done a lot of work creating views and stored procedures to greatly improve performance.

There is a tool from the SQL Server group which is better than the built in Upsizing Wizard. SQL Server Migration Assistant for Access (SSMA Access)

Also see my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page.

Answer 3

I would recommend against rewriting as ADP (you do realize, I hope, that you can't convert an MDB to ADP?). ADP has been deprecated by MS for about the last 5 years, and has received no development attention in the last two versions of Access (A2007 and A2010). It may get some attention in the next version of Access, or it may be dropped (as was the case with DAPs after two versions of no changes).

The easiest way to roll out multi-site access to an Access application is with Windows Terminal Server. This is extremely easy to implement and not all that expensive and requires no alterations to your Access application (I assume you'd upsize the back end regardless, of course).

You also might want to familiarize yourself with the fabulous new features of Access 2010 that integrate with Sharepoint Server 2010 and its new Access Services to allow the development of an Access app that runs almost identically in the Access client and in a web browser (via Sharepoint). This would require a major rewrite, of course, but it's also quite scalable.

It's also the future of Access, so far as I can see, and will be getting lots of attention from Microsoft over the next few years.