Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Access 2007 very slow with SQL server odbc connection (sql 2000) Expand / Collapse
Author
Message
Posted Thursday, February 28, 2008 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 7, 2009 6:29 AM
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
Post #461479
Posted Wednesday, March 5, 2008 8:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 2, 2013 11:26 AM
Points: 17, Visits: 22
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.
Post #464505
Posted Friday, March 7, 2008 4:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 7, 2009 6:29 AM
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
Post #466203
Posted Monday, April 21, 2008 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 27, 2008 3:29 AM
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?
Post #487915
Posted Tuesday, April 22, 2008 5:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 3:38 AM
Points: 279, Visits: 1,089
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.
Post #488506
Posted Tuesday, April 22, 2008 8:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 27, 2008 3:29 AM
Points: 3, Visits: 8
Thanks for letting me know I'm not insane, anyway :)

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.
Post #488673
Posted Tuesday, April 22, 2008 8:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 14, 2010 1:01 AM
Points: 351, 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.



Post #488684
Posted Tuesday, April 22, 2008 9:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 8:49 AM
Points: 1,508, Visits: 2,683
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.
Post #488696
Posted Sunday, April 27, 2008 3:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, April 27, 2008 3:29 AM
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.
Post #491115
Posted Friday, March 9, 2012 3:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
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.
Post #1264214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse