I am having a client/server application project with Vb
6.0 as front end and MS Access 2000 as back-end.
That is not really a client to server application. The more correct term is
a file share. (clinet to server would suggest that you are connection to
some database on the server side...which in your case is a simple file that
you open).
I have
used ODBC connectivity.
While you may be using a odbc string, in fact, you are opening the file
accross your network (the odbc connection ALWAYS resolves to a actaul file
path. (I not nit picking here...but most of the time when you use ODBC to a
server, you don't actaully resolve to a file path, but in fact resolve to a
socket connection (IP address).
Right now the problem that is
being faced is as the number of records are increasing,
accessing the application is becoming slower and
slower.How do I speed up the acccessing process of the
database or to be specific, how do I optimize the database?
You don't mention the number of records. However, the first things to check
is can you retrieve the record(s) via some key or field that has a index on
the field. This can dramatic speed things up. If you try and grab a few
records via a sql statement and the where clause cannot be optimized, then
all records are pulled to the client. If you have 50,000 records and grab
ONE record via a invoice number (for example), then only ONE record is
transferred across the network IF THE invoice field has a index. If no index
is present, then all 50,000 records are transferred.
Don't go overboard on indexing too many fields, as that can cost update time
also. (you have to strike a balance here).
Of course, stuff like opening a reocrdset can be real costly in terms of
time, so avoid any opening/creating of a reocrdste inside of a loop. Most of
these types of loops can be replaced with sql update statement.
There is good list of things to check here: (most apply to ms-access, but
some do apply to JET)
http://www.granite.ab.ca/access/performancefaq.htm