Comparing Data Access Techniques

[Toga]

We have an older Access app with the FE off at a remote site accessing the BE tables at our location. These are bound forms to linked in access tables so as you can imagine, dreadfully slow.

We're going to migrate the tables to the express edition of 2005 SQL Server.

Here's the mindset I'm working under:
Access is slow because in order to load a record, it's essentially having to pull / read all the records at the remote location in order to retreive the record I want.

A server is much faster because I can send it the info I want, it will do the search at the server side and only return the record I want.

The obvious option to me is to use a Pass Through query in order to accomplish this....

I have since learned that you can actually bind a form to a specific table / record in SQL server using an ADO recordset / OLEDB and then be able to update it.....which leads to my question.

When linking a form to a table in SQL Server using ADO, is that going to be more like linking to an access table (Deradfully slow) or like a Pass Through query?

I guess I'm trying to figure out how linking a form to a table in SQL Server using ADO actually works. Is it sending
individual request for a new record as you scroll through the recordset or does it pull the entire recordset to the FE App when you link it?

Any insight anyone might have would be appreciated.

 

[Toga]

One other question....again comparing a bound form to a specific table / record in SQL server using an ADO recordset / OLEDB vs a Pass Through query vs a BE Access table. If I have the form bound to only one record in SQL Server when I open it, is that going to be more similar to using a Pass Through Query to get that record or to an access BE table?

Thanks in advance for any insight (general or otherwise) anyone can offer.

 

[JoeAtWork]

I usually use ADO when I want to do some complex processing via a stored procedure on the SQL Server. This is primarily when the recordset is read-only, i.e. for populating a listbox, etc.

So, I use an ADO Command object and add all the required parameters, then execute it to retrieve the recordset. This is invariably faster than the equivalent queries that would be set up in Access. I then bind the control to the recordset.

I don't think I've tried this on a form that needs to be updateable. However, I believe the process would be to retrieve the recordset, disconnect it, and then bind to the form. When you wish to send updates back to the server, then reconnect the recordset, and do an UpdateBatch.