My goal is to speed up an MS-Access 2000 application consisting of two .MDB files (front-end and back-end).

The back-end resides on a remote server (across town over a slow pipe). The majority of the forms in the front-end use saved queries for their recordsource. All saved queries refer to linked tables corresponding with tables in the back-end. The front-end is secured using a .MDW file (i.e. the system requires the user to enter a password that it compares with an encrypted password in the .MDW file before it allows the user into the application). The back-end is NOT secured. The front-end runs on Windows XP Professional. The server OS is Windows NT. I am using "Active Data Objects 2.7".

Compared to the application’s performance on a previous LAN, everything is 2 to 3 times as slow. Someone suggested that I try ADO vs. DAO. I tried an experiment with a procedure that ran 10,000 iterations of a loop where records were retrieved from the backend. ADO worked much faster than DAO when I used a SQL query.

Now I am trying to figure out how to use ADO as the source for my forms.

I tried the following:

-------------------------------------

Private Sub Form_Open()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Me.AllowFilters = True

Set cnn = New ADODB.Connection

With cnn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open "C:\My_Folder_Path\" & _
                   "Dummy_Data.mdb" 'The back-end
End With 'cnn

Set rst = New ADODB.Recordset

With rst

' I tried using “CurrentProject.Connection but it did
' not work. The system told me the database
' was locked or I had an invalid password.

   .ActiveConnection = cnn

    .CursorType = adOpenKeyset
    .CursorLocation = adUseServer
    .LockType = adLockPessimistic
    .Open "SELECT * FROM tblTask", Options:=adCmdText

'This filter did not work here or below the “Open”.
    .Filter = "[Task_Nbr] = 10"

    Set Me.Recordset = rst

'This bound the form to the recordset, but I
'got ALL records instead of the filtered set.


'This filter did not work either.
    .Filter = "[Task_Nbr] = 10"

Also, I noticed that when using this method, the
following did not work:

   Me.Filter = "[Task_Nbr] = 10"
   Me.FilterOn = True

This filtering is important because I use several checkboxes in the user interface that set and turn on the filters. They do not work now.

As you can see, I used the SQL as follows in the .Open method:

   .Open "SELECT * FROM tblTask", Options:=adCmdText

How can I replace this SQL with my saved queries in the front-end?

Can I use "CurrentProject.Connection? Restrictions?

Is there any correlation between the tables linked using the "Linked Table Manager" and ADO?


Thank you ahead of time for your assistance!




--- Tom

__________________
--- Tom

How many users total use the database?



Sal

Access will not filter an ado recordset. Well, it filters it, but it will not re-populate the form. The form will not refresh the data. currentproject.connection should work fine as long as the tables are linked.

Do not populate the forms data until the user selected the criteria. Then you can pass the criteria to the sql string as a where clause. Besides, why bring all of the data to the local database if you are not going to use it. This will slow you down.





Sal

Sal,

    Thanks for your quick response. To answer your question, the number of users is under 10 with it being rare that any hit the database simultaneously. The fact that the application ran fast on another LAN (Windows 2000) and slow on this LAN (Windows XP client and Windows NT server) baffles me. The forms are simple forms using simple, single table queries from linked tables with less than 20 records and less than 20 fields. As I mentioned, data retrieval is fast with ADO and slow with DAO.

If I understand you correctly, you would skip the filter in favor of a SQL statment with a WHERE clause and set the form recordset to it, then when the user changes the criteria, reformulate the SQL with a new WHERE clause and re-set the form recordset to it? Did I get it straight?

Any suggestions on how to use my saved queries with ADO to form the basis for the form recordset?

Thanks ahead of time for your help!





--- Tom

Sal,

    The form I am working with is a continuous form that the user selects a record from. Each record is one of their projects. Some are active, some are not. The user needs to see all records. The inital filter excludes inactive tasks. When you remove the check from a checkbox called "Show Active", it turns off the filter and shows ALL records.

If I follow you, I would set my recordset as follows:

with rst
   .Open "SELECT * FROM xyz WHERE [Active] = True"
End with

    Set Me.Recordset = rst

Then when the user unchecks the checkbox, redo all of the above except take out the WHERE clause, open the new recordset, then: Set Me.Recordset = rst

Is this what you meant?






--- Tom

10 users max; I would be very suprised if there would ever be more than 2 hitting the backend at once; very small chance of trying to edit the same record at the same time.


--- Tom

Do not use your saved queries at all. You may use the code from the queries to open a recordset, but, using the queries will slow you down even more. Access Jet Engine will bring all records from all tables in a query to the users workstation and only then it will look at the where clause of your query. This is what makes access so slow.
Now, if you have a split database, your app may be slower. If on top of that you use Access security, you slow down the database app even more.

Try to move your app to MSDE (free SQL Server) It is more secure and it is much faster. Then ADO will improe your speed.


If you only have 10 users, MSDE will work for you. Your network connection will thank you.

Now, a users goes to a form with a task in mind. Make sure that they get only the data they need and no more. Why would a user get a filtered recordset and then require the complete recordset later?

Now, if you use ADO with a slow database, do not use currentproject.connection because you will be connecting to the linked tables. You want to get the data from the remote file, not from the local.






Sal