basically the connection strings are:
for Microsoft Access:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=physical path to .mdb file
for SQL Server:
Provider=SQLOLEDB.1;Data Source=path to database on server
and, depending on your security settings, you may have to pass userid= and pwd= parameters as well.
This is another difference, though at the db management level, not really on the Web development side. SQL Server offers more security features, also meaning more settings to mess up if not done right. With SQL db you can restrict users' access just to certain tables, or even columns, or even certain tasks such as selects, inserts, updates, create table, modify table, etc.