I have an application that has to access up to 4 databases. On my home network it works reasonably fast, probably because nobody else is using the database. But at work it is very slow. It can take up to a minute to access the data. So I am looking for ways to speed up the process. I know that there are a lot of variables associated with database access so maybe I can optimize a few of them. This is the function I use to access the database. Maybe you guys can make a few recommendations.

Function Compland(SQLQuery As String) As ADODB.Recordset

Dim Database As ADODB.Connection
Dim Bestand As ADODB.Recordset
Set Database = New ADODB.Connection

Database.ConnectionString = "Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=d:\database\database.dbf ;adLockReadOnly "
Set Bestand = New ADODB.Recordset

With Bestand
.CursorLocation = adUseClient
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
Call Database.Open
Set .ActiveConnection = Database
Call .Open(SQLQuery)
Set .ActiveConnection = Nothing
End With
Database.Close
Set Compland = Bestand

End Function

Hi,

The connection buildup seems ok, but i haven't got much Dbase time in me
Here are some other things you might look at

- see what kind off db you have are the tables indexed and if yes where are they indexed
- how big are the tables you unleach your SQL (if there are ten rows or 1 mil)
- how is your SQL-structure (big selection, much calculations slows it down)
- how is the connection to the network db

Tip look if you can do the following : Make Access table link to the dbase tables and run your querry there if its slow there also you have a clue where to look, Sometimes the db-guys will give you less priority on your sql because there bussy them selfs

suc6 daar -^.^-

dBase, like Access, is a file-based database storage mechanism; the entire table structure(s) is copied over the network to the client before the query actually runs.