April 8, 2016 at 9:16 am
I have a sql table with about 10 columns. I link the table to an ms access application. When I open the table in ms access and want to edit a column, it takes 15 seconds for it to respond and change the value. After that i can edit the values of any record and it is instant. Why does it do that on the first edit?
April 8, 2016 at 9:57 am
GrassHopper (4/8/2016)
I have a sql table with about 10 columns. I link the table to an ms access application. When I open the table in ms access and want to edit a column, it takes 15 seconds for it to respond and change the value. After that i can edit the values of any record and it is instant. Why does it do that on the first edit?
why are you opening a table with 6 million rows?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 8, 2016 at 10:20 am
to edit it.... I know it's a large table, but our data is large. We may cut that down to 2 or 3 million. I don't want to get into why it's 6 million. I just want to know if there's a reason why it takes long to edit the first time and then after that it is instant.
April 8, 2016 at 10:24 am
well...I am no Access expert, but I am guessing that Access is pulling all 6 million rows into your access app in the first instance.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 8, 2016 at 11:54 am
GrassHopper (4/8/2016)
I have a sql table with about 10 columns. I link the table to an ms access application. When I open the table in ms access and want to edit a column, it takes 15 seconds for it to respond and change the value. After that i can edit the values of any record and it is instant. Why does it do that on the first edit?
This is an ODBC connection. I don't know the inner workings that cause the issue, but basically at a mile high level:
Whenever you open a linked table, Access pulls in the data row by row to fill the current screen. When you try to edit something, ODBC reopens the connection and pushes out whatever changes you've made - again, row by row. For whatever reason(s) the initial edits tend to be delayed.
If I recall correctly (sorry I don't have Access available but you can Google the details) there is a way to setup a persistent connection in ODBC that seems to bypass this delay.
That being said, be very careful editing your data via Access. Bear in mind that when you change something, you aren't changing it directly; Access is sending a message via ODBC to change the row based on some data in the row. I've seen issues where the wrong row(s) get updated, duplicates get created, etc. And you can also create locks against your tables while ODBC is mucking with them.
Access is fine for relatively small datasets, but when you start linking to millions of rows it just doesn't scale very well.
April 8, 2016 at 12:23 pm
thanks! I will look into persistent connections....
April 13, 2016 at 10:13 am
I think some of the advice you received needs to be tempered a bit. I have routinely worked with multi-million row tables in ODBC linked SQL Server databases over the past 20 years and have never encountered corruption or updates applied to the wrong record. However, I've never allowed users to edit at the table level - the users have forms that filter the records from the table to either a single record or at most a few records. Doing that gets acceptable performance - data being displayed for editing in a few hundred milliseconds, and edits being applied in less than half a second.
It is true that if you open a linked table in the Access user interface, it will bring in a large number of records, but in general not the entire table, unless you are working with a very old version of Access. And you can apply filters that will quickly get you the set of records you want. But letting users edit at the table level is considered a bad practice in most corners.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
April 15, 2016 at 7:56 am
Wendel, thanks for the great information and advice.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply