It sounds like you are using what is called JET (that is the default
database engine for ms-access).
So, to be clear, ms-access is not a database system. Just like your vb.net
is not a database. Ms-access is a program development system that lets you
connect to Oracle, Sql server, or even JET (the native mdb file format). So,
likely, you are not using ms-access here, but JET.
In most tests, you will find that JET is faster then sql server, or even
Oracle. However, when a network connection is then involved, then the
reverse is usually true. That is because JET is not a client to server
system, but only a file share system. (all query processing occurs on each
client pc with JET, which is the exact opposite of a server based system).
Surely, you folks can't be writing software with such a lack of
understanding of the differences in these systems?
If you found sql server fast enough, then you can use the desktop version of
sql server in place of JET. In fact, us ms-access users have had that sql
server engine on the office cd for the last 2 versions of ms-access (and we
are now close to office 2003 release, so for almost 3 versions we had the
same performance of sql server). So, ms-access does not need to use JET, but
can use most server based system.
That desktop edition of sql server is the MSDE.
There is good white paper on optimizing JET. Most of it applies to DAO, but
many of concepts simply apply to how JET works through things. Check out:
http://support.microsoft.com/default.aspx?scid=kb;en-us;162701
Like all development platforms, it is takes a lot of developer experience to
write efficient database code. Further, some things in JET are counter
intuitive as compared to a server based system. For example, often using
in-line sql is faster then using a JET saved query! You can read why this is
so in the above link. However just this "one" issue is not going to be a
make, or break the performance issue here. It is going to be all things
considered in your designs, and a good deal of experience to find out what
works, and what does not takes time.
You can get better performance from JET as compared to most database server
based systems, but not when a network connection is involved.
