Being a Microsoft program, it makes sense to choose to work with a compatible Microsoft SQL server when creating databases in Access. The two are streamlined very well, although Access will work with a variety of other programs and formats.
On SQL servers, the difference is that in Access you would be using a client/server application, much like you would use the file server Circles application. Access has the ability to connect directly to databases (whether local or not), and will connect and integrate quite happily from a remote location. A common myth is that you need to use Access in a dramatically different way than you would when creating a database. This is not so, and the process is more or less the same. If you know Access well, you can apply your knowledge to SQL with little or no difficulty. If you don’t know much about SQL databases, it would be best to work the other way around (learning SQL before Access), in order to have the most success on databases and how they work.
Presuming that you are connecting remotely to an SQL database, the functions available to you are the same as they would be if you were programming an Access database locally. You can modify your tables, use your saved functions and also use additional features such as the SQL text editor while connected. Using SQL features have their own help wizards in tandem with the usual database ones.
Access usually works on an SQL database by using data access pages – that is, a special kind of webpage for manipulating data online that is saved within Access. If you know how to use forms in Access, you can pick up using data access pages quite fast, as the two are very similar.