March 22, 2015 at 11:10 am
I have a .accdb file (MS Access 2010) connected to a SQL Server 2008R2 database using linked tables. All tables in the DB have integer primary keys and are related, as required using declarative referential integrity.
I've created a query which joins a main table (the table that the user is entering data into) to all of the tables which provide look-up values. I use this view as the basis for the main from for user interaction. The form it's self is displayed in split form view.
There are several forms of similar design. One of the forms, by default returns less than 500 records. Another returns about 7000. Users can apply additional filters to reduce the number of records.
All of this performs suitably in the office where database is hosted. However, this application exhibits SIGNIFICANT slowness when run from an office approximately 200 miles away.
By comparison I have a second database hosted on the same server which was built using a MS Access .adp file. It's main form can return 20000 records when not filtered. The .adp file does not experience the same slowness at all.
What am I doing wrong?
I know that MS has deprecated the .adp file format so that doesn't seem like a prudent choice but my .accdp based solution is almost unusable.
Edit;
I should also mention that I don't know the details of the network connecting the two offices but I'm confident that it's not a bandwidth issue. The network belongs to the provincial government and we use it daily for things such as video conference meetings and streaming video which works fine. I would think that passing a few hundred or thousand database records is pretty trivial by comparison.
March 22, 2015 at 11:46 am
March 22, 2015 at 2:50 pm
They are not passthrough queries at the moment. They are based on the linked tables. I did try to create them as passthrough queries but that made the form non editable. Is there a way for them to be editable?
March 22, 2015 at 5:16 pm
Did you include the primary key in the query?
March 22, 2015 at 8:04 pm
The primary key of the main table was included. I have learned that in order for a query to be editable the primary keys of all related tables as included in the query. I have revised the query to include them as well. Still no joy. I have further learned that a query with left joins is not editable. I have two of those (they are relationships to values which are not mandatory. I've replaced those with subqueries which retrieve the values as appropriate.
There's are two other wrinkles.
1) I have a computed column which is not editable. I think I can work around this by computing the value in the query rather than retrieving the column from the table
2) I also have a column which gets it's value from a stored procedure. Again, I think I can work around this by actually calling the function rather than retrieving the value from the table.
I will post back once I have tested it.
March 22, 2015 at 8:05 pm
Well, that didn't take long. No joy. The query is still not editable.
March 30, 2015 at 2:08 pm
Sorry to be so late - was travelling all of last week. Have you tried using an Indexed SQL Server view as the basis for your form, rather than using an Access Query? One of the issues with Access is that if 3 or more tables are involved in a query, rather than letting ODBC run the query in SQL Server, it wants to bring all of the data in all of the tables involved to the local workstation and then run the query there. Using a view in SQL Server resolves that issue. If it is indexed, then the view will also be updateable.
You may also want to consult the following resources:
Access to SQL Server Migration: Access Solutions using SQL Server, Part 2
Optimizing Microsoft Office Access Applications Linked to SQL Server
The first article has a number of links to additional resources, while the second describes the use of views in some detail, and is still valid 10 years later.
Finally, have you deployed the Access front-end to individual workstations, or are the remote users connecting to a local copy of the front-end?
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
March 30, 2015 at 3:37 pm
Thanks for following Wendell.
I will certainly review the references you have provided.
Much has transpired since my post.
I was never successful with a pass-through query since they are not updateable.
For several reasons, I am unable to create an indexed view.
1) My view joins a "main" table which has all of the updateable columns in it to several "reference" tables which allow me to get the attribute names for the lookup values in the main table. Several of these lookups are optional values i.e. they are not required. Because of this, the joins to these are left joins. It turns out that you can not create an index on a view which has left joins. I tried to use subqueries in the view. Turns out you can't do that either...
2) I use some functions to calculate values which need to be in the grid of the split form (yes, still using split grid). Turns out that you can't put an index on a view which uses functions....
So, where I'm at is that in order to quickly deploy a solution in the near term which performs better than what users have now, I've converted the solution to a .adp. I'm using stored procedures as the source for the forms as well as for the combo boxes (lovin' using stored procedures! I can use both, functions and subqueries.). Using strored procedures is also something that I can't seem to do with a .accdb file (Its possible that I haven't figured out the mojo for that yet but it wasn't immediately evident to me).
The .adp (ADO) based solution is WAY faster than the same same solution in .accdb (DAO). It would be an interesting exercise to see if anyone could make the .accdb solution perform as quickly as the .adp. I freely confess that I'm an Access novice and am almost certainly doing something (probably a bunch of things) wrong.
Once I get an adequately performing solution in the hands of users it will buy me time to figure out how to deploy it as a sustainable (read; not deprecated by Microsoft) solution. My current thinking is either;
1) refactor the solution to use DAO efficiently. This will likely take some significant redesign. A refactoring may make it possible to use an indexed view.
2) use ADO within a .accdb. This will require a fair amount of coding - manually declaring recordsets and such.
Finally;
At the moment, each user has their own copy of the Access file which they are running from their own computer and which connects to the database. Another option available to us, which would also have some other benefits, is to deploy it to a Citrix environment. We haven't pursued that yet.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply