Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Linked tables to Access as a front end for SQL Server
- Thread starter GSMike
- Start date
jimmythegeek
Programmer
Yes. All the data that is required to process the request (even the whole table, or tables) HAS to be sent to your desktop, because your desktop is where the processing takes place. This is not a true Client/Server application.
If you want a c/s application, you need the server to do the processing for you. So you need to either use Pass-through queries, or unbind your data, and use code (ADO) to handle your data requests. In this manner, the request is sent to the server, the server processes it, and ONLY the results are sent back.
In my opinion, the performance of an application using bound tables (especially SQL Server or Oracle class) will NEVER compete with unbound applications. At least in my experience, there is no comparison. Jim Lunde
compugeeks@hotmail.com
Custom Application Development
If you want a c/s application, you need the server to do the processing for you. So you need to either use Pass-through queries, or unbind your data, and use code (ADO) to handle your data requests. In this manner, the request is sent to the server, the server processes it, and ONLY the results are sent back.
In my opinion, the performance of an application using bound tables (especially SQL Server or Oracle class) will NEVER compete with unbound applications. At least in my experience, there is no comparison. Jim Lunde
compugeeks@hotmail.com
Custom Application Development
petermeachem
Programmer
A similar question came up in the SQL forum and I replied :-
Are you quite sure about this? If the data is held in Access, then it certainly copies the whole table across the network, but I'm pretty sure that Sql Server does not. I spent quite some time running queries. I've just tried it. When I ask for a single route (it's all to do with lorries!) I get 60k bytes transmitted. If I ask for all routes I get 300k. I get about 60k for 8 records and 80k for 24 records. Whether this has something to do with page size? With Access as a backend, I get the whole table copied over first time, and then no network activity after that cos it's all here by then.
So SQL Server is clearly doing the work and sending back a subset of the data at least without me changing a thing.
I knocked up a vb6 programme that replicates one of the Access forms. The display in the (Janus) grid on vb is virtually instant whereas the Access one draws it in line by line. The amount of data transferred is however exactly the same in both cases. ( I am looking at 'non paged read bytes requested' in the NT Diagnostics prog. I hope this is a reasonable measure)
All I have done to the Access programme is replace the Access backend linked tables with SQL Server Linked tables and do me.RecordSource = "SELECT a,b,c FROM Orders WHERE Route = " & n
In VB6 I have
csql = "SELECT a,b,c FROM Orders WHERE Route = " & n
adoPrimaryRS.Open csql, db, adOpenStatic, adLockOptimistic
Set GridEX1.ADORecordset = adoPrimaryRS
So the question is why does Access appear to look so dreadfully slow when the back end is SQL when the network traffic is the same as VB, and what can I do about it. I'd like to stick with Access and SQL if possible as it's so easy.
Are you quite sure about this? If the data is held in Access, then it certainly copies the whole table across the network, but I'm pretty sure that Sql Server does not. I spent quite some time running queries. I've just tried it. When I ask for a single route (it's all to do with lorries!) I get 60k bytes transmitted. If I ask for all routes I get 300k. I get about 60k for 8 records and 80k for 24 records. Whether this has something to do with page size? With Access as a backend, I get the whole table copied over first time, and then no network activity after that cos it's all here by then.
So SQL Server is clearly doing the work and sending back a subset of the data at least without me changing a thing.
I knocked up a vb6 programme that replicates one of the Access forms. The display in the (Janus) grid on vb is virtually instant whereas the Access one draws it in line by line. The amount of data transferred is however exactly the same in both cases. ( I am looking at 'non paged read bytes requested' in the NT Diagnostics prog. I hope this is a reasonable measure)
All I have done to the Access programme is replace the Access backend linked tables with SQL Server Linked tables and do me.RecordSource = "SELECT a,b,c FROM Orders WHERE Route = " & n
In VB6 I have
csql = "SELECT a,b,c FROM Orders WHERE Route = " & n
adoPrimaryRS.Open csql, db, adOpenStatic, adLockOptimistic
Set GridEX1.ADORecordset = adoPrimaryRS
So the question is why does Access appear to look so dreadfully slow when the back end is SQL when the network traffic is the same as VB, and what can I do about it. I'd like to stick with Access and SQL if possible as it's so easy.
jimmythegeek
Programmer
Peter,
You are correct in that SQL Server does not send all the data, only the results.
One thing. You said you did a test of linked tables in Access, then linked tables in SQL Server. Have you tried running tests using unbound forms, and ADO to retrieve the data? Everything that I have done proves that unbound applications using ADO outperform any linked table applications. I am curious to see if you have found the same. Jim Lunde
compugeeks@hotmail.com
Custom Application Development
You are correct in that SQL Server does not send all the data, only the results.
One thing. You said you did a test of linked tables in Access, then linked tables in SQL Server. Have you tried running tests using unbound forms, and ADO to retrieve the data? Everything that I have done proves that unbound applications using ADO outperform any linked table applications. I am curious to see if you have found the same. Jim Lunde
compugeeks@hotmail.com
Custom Application Development
petermeachem
Programmer
I read that ADO and Access 97 means read only datasets so I haven't really tried it.
I can see how to display a single record as unbound, but I can't for the life of me see how to relate an ADO recordset, or whatever it is called now with a continuous form. Or even a DAO one.
If I could get the Access display speed up to something sensible, I'd be more than pleased. I don't understand why Access is so slow and the VB6 grid is so fast when the code is basically identical.
I can see how to display a single record as unbound, but I can't for the life of me see how to relate an ADO recordset, or whatever it is called now with a continuous form. Or even a DAO one.
If I could get the Access display speed up to something sensible, I'd be more than pleased. I don't understand why Access is so slow and the VB6 grid is so fast when the code is basically identical.
jimmythegeek
Programmer
You can have editable recordsets with a SQL Server back-end no problem.
We used pass-through queries as the source of most forms that were read-only.
If a form needed to be editable, we would either use a read only form (ptq) for a continuous, or listing, with an Edit button which opened on unbound form and populated the form (ADO) with data for a particular record. On that form was either an update, or save button. When they clicked that button, it would write the data back to SQL (ADO). OR, we simply used unbound forms in the method just mentioned if you only needed to work with one record at a time.
One thing you can't do, is have the record source of a subform be a pass-through query.
It actually works very well, and very quick Jim Lunde
compugeeks@hotmail.com
Custom Application Development
We used pass-through queries as the source of most forms that were read-only.
If a form needed to be editable, we would either use a read only form (ptq) for a continuous, or listing, with an Edit button which opened on unbound form and populated the form (ADO) with data for a particular record. On that form was either an update, or save button. When they clicked that button, it would write the data back to SQL (ADO). OR, we simply used unbound forms in the method just mentioned if you only needed to work with one record at a time.
One thing you can't do, is have the record source of a subform be a pass-through query.
It actually works very well, and very quick Jim Lunde
compugeeks@hotmail.com
Custom Application Development
- Thread starter
- #8
With respect to this topic, let me ask this related question:
Why is it that MS Access can dispaly 600k records on the screen with no problems, but when I run a query on SQL Server that returns 20k records, I get a system message saying that my computer is running out of memory?
If I launch Task Manager, while SQL Server is returning the recordset, I can watch the memory usage steadily rise.
Thanks.
Mike
Why is it that MS Access can dispaly 600k records on the screen with no problems, but when I run a query on SQL Server that returns 20k records, I get a system message saying that my computer is running out of memory?
If I launch Task Manager, while SQL Server is returning the recordset, I can watch the memory usage steadily rise.
Thanks.
Mike
- Status
Similar threads
- Locked
- Question
- Question
- Locked
- Question