Attaching an Access "front end" containing queries, forms, and reports to data tables stored in a SQL Server database is very straightforward. There is a tool called the "Microsoft SQL Server Migration Assistant for Access" that transfers an existing Access database to SQL Server.
It's very questionable though whether you would benefit from doing this. A "small" database with less than 5 simultaneous users should not have any performance or reliability problems running in any version of Access back to Access 97. I personally have over 200 installations of a very complex multiuser Access system, many running for more than 10 years, each installation with as many as 25 simultaneous users, with no particular problems.
Having said that, SQL Server is certainly more bullet proof than the Access engine, but it shouldn't be necessary for the kind of small application you describe. The only real issue with Access reliability is network reliability. Dropped or inconsistent connections to the network can cause database corruption but that corruption is almost always easily fixed just by opening the data file in Access and allowing it to automatically repair itself.
Performance of SQL Server is definitely going to be better with hundreds of simultaneous users but with 5 users most functions will be just as fast or even faster in Access. Keep in mind that even though 5 people may have the application open, the only time they are actually transacting with the database is when they load data into a form or report (running a query) or save changes to the data. Observing their work you'll almost certainly find that even having two actual simultaneous actions on the database isn't that common.
Almost all performance problems with Access applications are due to incorrect design, starting with the data structures and continuing with bad query and form design, use of macros (don't ever), and/or bad VBA code. Most new Access users are unaware of the need to split a multiuser application into separate front end and back end databases. This article explains why this is necessary and exactly how to do it. It's fairly easy - there is even a wizard in Access to help you do it.
If you can't explain or resolve specific performance issues with an Access application, you should probably seek help here or on other forums by describing the exact nature of the problem. There are also some fine books available - particularly the Access Developer's Handbook by Ken Getz, et. al. While quite old (2002) this is the "bible" for Access design and still 99% applicable to newer versions.
Good luck!
