Need a spatial database that can be used by QGIS and R

Aside from Spatialite, you might also want to consider PostGIS. Think of it as Spatialite's big brother ;-) It's just another data source for QGIS while you can connect to it using the RODBC package in R.

Not sure if you will be able to work with MS Access in QGIS (plus I haven't heard about any respectable GIS-project that would use MS databases); MSSQL - is supported (but never tried it myself and never will ;-) ).

Consider possibility to use Spatialite (spatial extension of SQLite). It will be quite suitable to operate Spatialite DB in QGIS and you can easily access Spatialite via R packages: SQLiteMap; RSQlite. This is the recipe I would use for myself.

EDIT: As was mentioned in comments (and I implied it), Spatialite require no server configuration, users administration, etc. And it is easily transportable to another computer by just coping database file.