Visual Basic and Access were a great combination. They allowed the creation of small powerful apps, specially apps for data collection, or just applications that needed a small database that could run standalone.
A good option when you are upgrading your Access database and you do not need a centralized SQL Server (or that is just too much for your environment) is to migrate to other options like SQLite or SQL Server.
SQL Server has now a lot of versions (SQL Server Compact 3.5, SQL Server Compact 4.0, SQL Server Express and now even SQL Server LocalDB) a good table that compares this databases feature by feature can be located here: http://erikej.blogspot.com/2011/01/comparison-of-sql-server-compact-4-and.html)
If what you need is to support a simple, small (less that 4G) standalone database then using SQL Server Compact edition might be for you.
The main version for SQL Compact as at the moment 3.5 and 4.0.
Moving from Access to SQL Compact is not hard. There are many third party tools some free some not: http://erikej.blogspot.com/2009/04/sql-compact-3rd-party-tools.html
Primeworks tools are very easy to use, but you can also use the Microsoft SQL Server Assistant for Access (good links for this tools are in MSDN http://msdn.microsoft.com/en-us/library/hh313039.aspx and also the SSMA Blog http://blogs.msdn.com/b/ssma/)
SQL Server Compact uses the ADO.NET and OLE DB providers, and in many scenarios is just what you need. So take it as an option if you just need a replacement for MS Access
When I use SQLServer 2008 Express for my development tests, I always forget which things I have to do in order to make my SQL Server 2008 Express instance available to other machines over the networks.
So to not forget that again this are the things that you have to check to be able to acces sql server express from other machines:
1. Go to the Start Menu\All Programs\Microsoft SQL Server 2008 R2\ and run SQL Server Configuration Manager
2. In the SQL Server Configuration Manager Window, expand the tree on the left and expand the SQL Server Network configuration element on the tree. Make sure that at least TCP/IP protocol is enabled.
3. Now Click on the SQL Server Services element on the tree and make sure that the SQL Server Browser service is running. It is needed in order to make other computer able to see your server.
If for some reason you have to use VC++ Express and you have projects that use ATL,
you will get annoying error messages like:
LINK : fatal error LNK1104: cannot open file 'atlthunk.lib'
Just follow the steps in this post and you well be ready to go:
http://www.quantcode.com/modules/smartfaq/faq.php?faqid=99