November 21, 2019 at 10:28 pm
I have only very rarely seen blocking issues in access, even with hundreds of users. A common issue is that people bind a dropdown to something like an order ID, so that access start pulling all the records into the dropdown. The technique probably worked well when you had a couple of hundred orders in there, but as your volume increases its a bad idea to bind a combo/dropdown/listbox to it.
And no experienced developer would to that (I should hope not anway). And of course there are things that as a developer you should know when you start scaling the system. But there are also so many apps made by amateurs that solve their bushiness needs just fine, without ever reaching more than a few thousand records, or a few users. I mean do you really want to make a web app to share between 5 users?
November 22, 2019 at 5:37 pm
From what I recall, there are two ways to query SQL Server from MS Access. One method is to create linked tables, where you pulls rows across the wire and join the linked tables locally in MS Access, which is... wack. The other method is using pass-through queries, where the SQL runs entirely on the server, and then results are sent back to MS Access, which is a lot better.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 22, 2019 at 5:49 pm
Actually, that's not quite how Access works with linked tables, unless, of course, you don't know what you're doing.
This query, run against an ODBC linked SQL Server table in Access, returns either an empty recordset or a recordset containing ONE record from the database.
"SELECT PrimaryKeyField, ValueOneField, ValueTwoField FROM TableOnSQLServer WHERE TableOnSQLServer.PrimaryKeyField = 1"
The myth that Access pulls all records down regardless has been around for a long time, but it's simply never been true.
There are ways to create truly wack joins that force Access to retrieve recordsets inappropriately, but that's in the same category of poor design problems that is one of the main threads of this conversation: amateur folks simply make amateur mistakes.
November 22, 2019 at 6:21 pm
If I remember correctly, you have choices in addition to passthrough or linked tables. You can open an "ado" recordset or a "dao" recordset (with ado being the newer method).
November 22, 2019 at 6:45 pm
Actually, that's not quite how Access works with linked tables, unless, of course, you don't know what you're doing. This query, run against an ODBC linked SQL Server table in Access, returns either an empty recordset or a recordset containing ONE record from the database.
"SELECT PrimaryKeyField, ValueOneField, ValueTwoField FROM TableOnSQLServer WHERE TableOnSQLServer.PrimaryKeyField = 1"
The myth that Access pulls all records down regardless has been around for a long time, but it's simply never been true.
I wouldn't say it's a myth. While in most cases even a non-passthrough query will evaluate as you have said, depending on the complexity of the WHERE clause and joins I have witnessed from the database server side it pulling the entire table. It's been over a decade since I've worked with it so I don't remember the exact details but it did sometimes happen.
November 22, 2019 at 7:04 pm
George Hepworth wrote:Actually, that's not quite how Access works with linked tables, unless, of course, you don't know what you're doing. This query, run against an ODBC linked SQL Server table in Access, returns either an empty recordset or a recordset containing ONE record from the database.
"SELECT PrimaryKeyField, ValueOneField, ValueTwoField FROM TableOnSQLServer WHERE TableOnSQLServer.PrimaryKeyField = 1"
The myth that Access pulls all records down regardless has been around for a long time, but it's simply never been true.
I wouldn't say it's a myth. While in most cases even a non-passthrough query will evaluate as you have said, depending on the complexity of the WHERE clause and joins I have witnessed from the database server side it pulling the entire table. It's been over a decade since I've worked with it so I don't remember the exact details but it did sometimes happen.
I remember this too, I think its along the lines of writing an access query that joins two linked tables, I don't think it pushes the join to the remote server, instead it would evaluate the join locally, and I seem to remember this resulting in a very busy network connection.
November 22, 2019 at 7:14 pm
Access forms are bound to tables or queries. The navigation from one record to another is done automatically (i.e. you don't need to code anything). A table can be local, a linked table, or it can be linked directly to a view.
So, for the untrained eye, looking at an Access form with a recordsource "Select * from Customers" might give rise to the myth that Access will pull everything from the server. What happens though is that Access will start by getting a keyset of the records, and then pull as many records as is required to fully populate the screen, (so somewhere between 10 and 100 records depending on your monitor).
As the user navigates, Access will pull more records. If the user decides to click the "Goto Last" record, then access will, navigate the keyset to the end, and then retrieve as many records as required to populate the screen. All of this is to give a great user experience, and a rapid response for the user, and mind you with NO coding required, no code to write to handle pagination etc.
The above is the NORMAL behavior.
The Access engine is actually quite clever about what it does. Say you call "Select Field1,Field2,VBAExpression(Field3) from YourTable", then Access will do as previously described, and get first the keyset for navigating, then fields 1,2,3 for the records it intends to display, and then calculate locally the VBA expression, and again, just for the records required. In many ways Access is super-optimized, and has had this behavior "out of the box" for 20 years now.
Now the issue comes when a developer (Or shall we say information worker in over his head) decides to do something like:
Select * from MyTable Where VBAExpression(SomeDate)=2019 Order by VBASomeOtherExpression(SomeDate)
Now, in order to do the filtering, loads of data needs to be pulled down, and processed locally. Before Access can even start displaying the first record, it must process VBASomeOtherExpression for all the records, so it can sort them. I'm sure as a SQL DBA you can see where this goes wrong, even with the best of software, there is just no optimization that can compensate for this developer error. And an error like this one is certainly NOT limited to Access, nor VBA. It's a common anti-pattern where ever SQL is concerned, and really no different than:
Select * from Table where Year(Date)=2019. It worked excellent when there was 100 records, but now its starting to slow down the system..well no wonder.
If we touch briefly on queries just to make sure we have mentioned them as well. Access can have local queries, that joins tables (linked or local). The Access query engine will try to its best to process the query in a way that can ship it off to the server. In some cases Access gives up, and pulls down the data from both tables, and joins them locally. In effect the tables are treated as heterogeneous datasources. The preferred solution is to define a view on the server (thus forcing the join to be handled server side). You could also use passthrough with the sql defined, or use passthrough to a stored proc, or use ADO to a stored proc. In general, as the data size and complexity increases, locally defined queries should be avoided (even noting that not all locally defined queries are handled locally, the simpler ones are shipped to the server as well)
The main point I'm trying to convey, and clarify is that Access is super-optimized for working with SQL sources without pulling all data, but, yes it suffers from poor usage just as much well as the next coding language.
November 22, 2019 at 9:10 pm
What I don't agree with is your enthusiasm for implementing Access apps. Any "genius" that you see in Access is confined to the workstation. I can completely understand how an IT operation could end up supporting an Access solution that started small and grew because it met a need. I think that is a perfectly acceptable use case. I disagree completely with architecting a solution this way on purpose. The reason is the support available for such a solution: Microsoft Premiere and consultants. It is much less risky to implement a solution using the correct technology. That has been my consulting advice for years and I see no chance of that changing anytime soon.
November 22, 2019 at 9:29 pm
What I don't agree with is your enthusiasm for implementing Access apps. Any "genius" that you see in Access is confined to the workstation. I can completely understand how an IT operation could end up supporting an Access solution that started small and grew because it met a need. I think that is a perfectly acceptable use case. I disagree completely with architecting a solution this way on purpose. The reason is the support available for such a solution: Microsoft Premiere and consultants. It is much less risky to implement a solution using the correct technology. That has been my consulting advice for years and I see no chance of that changing anytime soon.
I don't think anyone is saying large projects should be written using Access. Scalability and security issues kill that idea. However, if the max user base is going to be around 20-25 concurrent users (such as a department) a developer who knows Access + Jet/Ace can create a robust (yes, I said it :)) application in 1/10 the time VS + T/SQL would take. And that's not an exaggeration.
That's also assuming a LAN and workstations/laptops. You want phones? Nope. You want web? Nope. But that still leaves a VAST ocean of possible database applications. Not everything has to be Facebook scale to be useful.
November 22, 2019 at 10:37 pm
We are probably working in different market segments.
My market segment is clients who have very specific needs and they feel that the "shelf" software available either
A) Doesn't meet their specific requirements
B) Does too much, takes ages to learn, or requires that the whole company uses e.g. SAP
Our role, is then to produce software for them, usually in less than a month, that solves their business needs. The number of users is typically between 5 and 100. No it doesn't work on the web, nor on a mobile. They don't need it to. If they to, we refer them elsewhere.
November 23, 2019 at 4:05 pm
Once again, the claim that all joins force Access to retrieve all records and work locally is simply NOT true.
It IS true that some complex SQL can force that to happen, but it is also true that Access optimizes queries pretty well.
See Anders extended comments for more details.
What I suggest is this: Instead of repeating what we heard a decade ago, we actually TRY IT OUT OURSELVES.
Sometimes trial and error and good benchmarking really can teach us something.
It shouldn't be hard to study the results of queries running from an Access accdb against ODBC-linked tables in a SQL Server database. Does SSMS provide tools for exactly that purpose?
BTW: It is true that if you link Access to two different remote SQL Server databases, perhaps on the same server or on two different servers, there is no way for Access to optimize those and, yes, that will result in full table downloads. But, that's a corner case and there are work-arounds even for that.
Viewing 11 posts - 61 through 70 (of 70 total)
You must be logged in to reply to this topic. Login to reply