Can a Microsoft Access Forms application be switched to work with a SQL back end?

Question

Hi,

I was asked to provide an estimation to change a relatively small app designed with MS Access to use a SQL database.

First, is this even possible? I never worked enough with Access, so I'd like to know. If it's possible can you please point me to some websites with tutorials regarding this?

Second: if it's possible, is it recommended? Is it a big performance hit? Any specific do/donts that you can and want to share?

Thanks in advance for your help.

Answer 1

This tells you how to convert Access to SQL Server:

http://support.microsoft.com/kb/237980

Now, depending on how the application was written (language, etc.), the rest of it may be a quite a bit more work. However, it's really difficult to throw a ballpark on that one because the number of variables in this are large. In any case, it is possible, so that's not an issue.

In terms of performance, if the database is tiny, then Access may actually be faster. If the database is large, you'll gain from SQL server. Aside from that, you can have distributed clients and one data store centrally located, that's a plus.

By the way, if this is written using the .NET framework, it probably just got a lot easier.

Answer 2

This would be fairly easy to do (hopefully). In Access, you can create linked tables that are just links through to tables in other database (like SQL Server), so you could take your existing apps and replace all the Access tables with same-named links to tables in your SQL Server database.

I think doing this could be the opposite of a performance hit, depending on the circumstances. Access is very performant, but only with a small number of concurrent users (say, less than 30). So if the application has a large number of users, you would benefit from switching over to SQL Server. If, however, the SQL Server database is hosted on a different machine, than you lose performance by connecting to a database on a remote server (as opposed to connecting to a local Access database).

I wouldn't recommend doing this, personally.

Answer 3

For the specific task of converting a Microsoft Access database to use SQL Server as the back-end data store while keeping the user interface from the Microsoft Access application, Access itself supplies an "Upsizing Wizard" (Tools | Database Utilities | Upsizing Wizard).

This wizard will step you through the process of connecting to an instance of SQL Server, creating an SQL Server database, and moving, to the greatest extent possible, your tables and views (called Queries in Access) to the new database.

The process can be relatively painless, or fraught with difficulty depending on numerous factors including how well you've validated your data in the Access database, whether you've made use of VBA functions in your queries, and whether you've used any query constructs unique to Access.

Answer 4

SQL Server upsizing is relatively easy to do although thorough testing is required. I'd estimate the effort at 5% to 10% of the initial system development. SQL Server upsizing is recommended in specific circumstances such as greater than 25 to 50 users or 24x7 access or difficulty/impossibility in rekeying data in the event of a corruption.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page

There is a tool from the SQL Server group SQL Server Migration Assistant for Access (SSMA Access) which is a lot better than the upsizing wizard.

Answer 5

to upsize or not to upsize - that is the question...(or was for us)

kind of a big deal to step up to sqlserver....we kind of dodged it by going to a WAN replication service from AccessTables.com and stayed with Access....at least for the time being.....the upsizing was just one issue...dealing with a web front end or terminal services was part of it too...