i am developing a application in using vb and Access. if number of records in database increased then it affects speed of application. Is there any solution for this problem.

Hi,
Your title is not entirely correct. You only get a result by compressing a DB when records are deleted, not added.

Usually, the biggest performance hit is when programmers open a table and iterate through the records (using MoveNext). Only the data you require should be retrieved from the database (ie. Joins and WHERE clauses)

As you get more records, inevitably you will see a degradation in performance, however, I wouldn't expect the user to start noticing before 20,000 records.

Hope this helps,
Kieron


____________________________
Build it better, faster, quicker, easier.. then fix it (non-offical MS mission statement)

Hello, and excuse me for the delay, I am a bit busy in this period.

Time ago, I wrote a small function. I post it now and hope it's useful. It requires a reference to DAO.

' compact and repair a .mdb database ' =========== Method using DAO ========== Public Function CompactDb(ByVal DBPath As String, _     Optional password As String) As Long On Error GoTo ErrRoutine     Dim NewName As String, OldName As String     Dim NewString As String, OldString As String     Dim dbExt As String     CompactDb = 0     ' rename the file to compact     NewName = DBPath     dbExt = Right$(DBPath, 4)     OldName = Replace(DBPath, dbExt, ".old", , , vbTextCompare)     ' The Name function can only be executed on closed files. If the file     ' is open, it raises the error 75. This is useful also when we later need     ' to compact the file, as we can't compact an open db     Name NewName As OldName      ' rinomina il file     If Not password = vbNullString Then password = ";pwd=" & password     DBEngine.CompactDatabase OldName, NewName, dbVersion40, , password     ' deletes the original non-compacted file     Kill OldName ExitRoutine:     Exit Function ErrRoutine:     CompactDb = Err.number     Resume ExitRoutine End Function

I wrote also another version, using JRO (MS Jet And Replication Objects), with basically the same working algorithm. The difference is on the internal management of the password, by a connection string. You can alternately try the following one.

' compact and repair a .mdb database ' ========= Method using JRO ============ ' (requires a MS Jet And Replication Objects (JRO) reference in References menu) Public Function CompactDb(ByVal DBPath As String, _     Optional password As String) As Long On Error GoTo ErrRoutine     Dim NewName As String, OldName As String     Dim NewString As String, OldString As String     Dim dbExt As String     Dim dbEng As JRO.JetEngine     CompactDb = 0     ' rename the file to compact     NewName = DBPath     dbExt = Right$(DBPath, 4)     OldName = Replace(DBPath, dbExt, ".old", , , vbTextCompare)     ' The Name function can only be executed on closed files. If the file     ' is open, it raises the error 75. This is useful also when we later need     ' to compact the file, as we can't compact an open db     Name NewName As OldName      ' rinomina il file     If Not password = vbNullString Then password = ";Jet OLEDB:" _         & "Database Password=" & password     Set dbEng = New JRO.JetEngine     OldString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & OldName & password     NewString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NewName & password     dbEng.CompactDatabase OldString, NewString      ' crea un file compattato     Set dbEng = Nothing     ' deletes the original non-compacted file     Kill OldName ExitRoutine:     Exit Function ErrRoutine:     CompactDb = Err.number     Resume ExitRoutine End Function

However, keep in mind that, if you use MSAccess, and plan to have it installed on target machine, you can also declare a new Access object and then call the RunCommand method to invoke the Compact database Access' menu voice, like following:

Dim AccObj as Access.Application     Set AccRep = New Access.Application     AccObj.RunCommand acCmdCompactDatabase     Set AccObj = Nothing

This execution is hidden to the user. Only, this way requires Access installed on target machine.

Hope it may help

____________________________
Real Programmer can count up to 1024 on his fingers