Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access 2007 very slow with SQL server odbc connection (sql 2000)


Access 2007 very slow with SQL server odbc connection (sql 2000)

Author
Message
tw_fake
tw_fake
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 10
We recently bought some new pc's with office 2007. Before we only had office 2000 and 2003.

We use a mdb with a SQL server odbc connection and this works very well with access 2000 and 2003, but with access 2007 it's very slow.
If we create a new record, just typing is extremely slow.

Anyone has some suggestions how to resolve this?

Thanks in advance,

Tw
defiantclass
defiantclass
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 30
I have a similar situation. I have a form with only 6 fields in it. All the fields actually work well except for 1 date field. When we enter that field, there is a lag time of a good minute or so before you can enter the date in it.

My Access front end was originally created in Access 2000. Workstations running Office 2000 and 2003 do not see this issue. Only workstations running Office/Access 07 have the problem. I am connected to an SQL Server 2000 back end.

I have even converted the front end on these workstations to Access 2007 so they are not running in 2000 format. Still no good.
tw_fake
tw_fake
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 10
Has anyone a suggestion how to resolve this, or where I might look to find the problem??
I've tried to transfer the mdb to access2007 -> no good.
I've tried to copy everything to another form -> no good.

Typing lag is extreme. Not annoying, but the lag is to big to work with it.

Any help is appreciated,

TW
John Henry-436275
John Henry-436275
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
I'd love a solution on this as well...exactly the same issue, and now that I'm on a satellite broadband connection it's even worse than it was on dialup. (Yeah, I'm in the sticks, and the SQL server is remote)

I'm seriously about to roll back to Access03. I've got clients waiting for work and I've blown nearly a week trying to figure this mess out, even to the point of trying to download and install SS05Express, developing locally, and exporting (works great, except all the table names change on upload which hoses my query writing).

This is REALLY frustrating. Clues, anyone, please?
Karma-343206
Karma-343206
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 1100
Hi,

Patching to SP1 may resolve some responsiveness issues however i suspect that Access 2007 is a resource hog. I've only run some small tests as we have no intention of upgrading from 2003 at present. We found that apps that flew in 2003 were considerably slower in 2007 unless they were running on very well spec'd pc's and the development GUI was often ridiculously slow.

There's a fair amount of general gripping if you google for it - isn't there always - but i couldn't find anything specific other than making sure your on SP1.

K.
John Henry-436275
John Henry-436275
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Thanks for letting me know I'm not insane, anyway Smile

I re-installed Access 03 and am getting much better performance. Based on the particular nature of this issue, I have to assume that this is rooted in a greatly increased number of calls to the database when performing even simple tasks. After fiddling around with both apps for a while on the same remote SQL Server back end, the nature of the thing seems to be that where <= 03 was doing row-level writes on edit (that is, saving the communication with the database until you moved your cursor out of one record and into the next), 07 seems to be doing them at *field* level - meaning every time you enter or leave a field, it's sending/receiving. 07 also seems to move MORE data on each read/write; the lag is much lower on 03 even when changing records (and completely nonexistent when changing fields).

As for the box, I'm running a dual-core athlon 4800 with 4GB, Vista Ultimate 64, and a terabyte of hard drive space - not the fastest machine on the planet (anymore), but it ought to be more than enough to handle a fairly common consumer app like Access. It's also worth noting that the lag to a LAN installation of SQL05Express, while still on the laggy site, is nowhere near as problematic as trying to work on the web-based instance that runs my sites. There *may* be a small performance hit just because I'm hitting a much more active target there, but nothing I'm doing is at a level that should create that kind of damage when you're talking about the same database engine that runs NASDAQ, Hilton hotels, and other HYUUUUUGE databases running millions upon millions of transactions per day (or even per hour).

Without being a "real" programmer (meaning I couldn't decompile and understand the source code under Access - even if it were legal - to grok where the different really lies), I'm forced to go with my operating theory: Access 2007 simply makes many more calls to the source data in the course of everyday activity than Access '03 did.

I will, however, double-check the state of my service packs and patches - I *thought* I had SP1 done already, but maybe not. If it makes any appreciable difference, I'll report back here.
jfmccabe
jfmccabe
Old Hand
Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)Old Hand (361 reputation)

Group: General Forum Members
Points: 361 Visits: 440
We run an Access 2003 app against SQL server 2005. It works very well. In Access 2007 it runs OK. I'm running on a reasonably fast box and don't notice any obvious differences.

There are a number of problems and difficulties with Access 2007. Some of it is down to the code we use. Some of it is just plain weird.

Our basic record structure has a large number of fields - 120 split across 3 tables (don't ask!). It works fine in Access 2003 BUT when we attempt to add a new record in Access 2007 we hit major problems with the date of birth entry. We have played about with the data entry mask of the filed but no, it sticks.

This is mainly an Access interface problem because we have another routine which copies different views down to local tables, does some updates including inserts and then updates the master views. This works fine even though we have multiple dates in both views.



WILLIAM MITCHELL
WILLIAM MITCHELL
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1656 Visits: 3019
Access 2007 definitely has performance & stability issues in several areas.

Example: The user previews an Access report on-screen, and then sends it to Word in RTF format. With Office 2003 on an old Pentium 4, it took about 15 seconds. After upgrading to a new Xeon and Office 2007, the user waited for two hours before Ctl-Alt-Delete to kill the process. Solution: uninstall 2007 and install 2003. Problem solved.

At one customer mixing 2003 and 2007, 2007 would occasionally insert non-English characters into records.

At another customer mixing 2003 and 2007, Access 2007 corrupted the file (Unrecognized database format) that could not be repaired using either 2003 or 2007.
John Henry-436275
John Henry-436275
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 8
Finally got SP1 installed...seems to have made quite an improvement. Still not as quick as 2003, but close enough that it's usable, at least.

Thanks, everyone, for your help.
Conficker
Conficker
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 386
Hello Everyone,

We had same situation where we had application front end in Access 2003 and Backend SQL Server 2005, runs fast no probs.

Now we upgrade Access 2007 noticed perfomance issue, for example mostly works fine however it goes slow on one of recrod like list of dates and have to wait for long time wasn't previously.

Solution : i don't it's right solution or not but after long search i found that the ODBC Connection String ....

I had new ODBC connection string like in APP part it was 2007 Microsoft Office system
Now changed to Microsoft Office 2003 and works fine.....!

I would say look into your connection string and make changes to it and import table again and try it.

Good Luck.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search