Access DB with SQL Server Back End

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 othe开发者_Go百科r 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

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.

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.

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.

ADP is definitely faster over a WAN than linked tables. Linked tables are the least efficient thing in the world. Jet didn't get any new features for 10 years.. Access Data Projects get new features through every release including Access 2000, 2002, 2003, 2007 and 2010. ADP has also gotten new features in 2005 and 2008 with the release of SQL Server. ADP has gotten new features with every release, it is NOT depecrated, it is fully supported. There are specific hotfixes released for ADP just like there are for Jet.