July 4, 2008 01:01pm pdt

1. vadimrapp1 36,550
2. LSMConsu... 20,043
3. boag2000 15,625
4. rorya 11,400
5. chapmandew 9,748
6. peter57r 9,550
7. angelIII 9,325
8. kelvinsp... 7,750
9. jimpen 7,625
10. TextReport 7,400
11. SidFishes 7,200
12. DatabaseMX 6,600
13. LPurvis 5,964
14. jimhorn 5,818
15. matthews... 5,400

1. LPurvis 89,936
2. peter57r 78,386
3. LSMConsu... 70,800
4. rockiroads 44,447
5. SidFishes 37,544
6. vadimrapp1 36,550
7. capricorn1 35,518
8. jimhorn 32,429
9. stevbe 31,815
10. nico5038 26,250
11. thenelson 24,639
12. JDettman 20,668
13. jefftwilley 19,800
14. angelIII 18,577
15. harfang 17,972

05.09.2008 at 10:54AM PDT, ID: 23390306

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

6.2

Access 2007 ADP performance stinks - would ODBC be faster?

Zones: Microsoft ADP, SQL Server 2005, Access Coding/Macros

Tags: ACCESS

Here I sit with what I thought would be a fairly straightforward upgrade from an Access2003 MDB to an Access2007 ADP using SSMA. After MONTHS of work, I've actually got the ADP working almost completely. The client has now migrated to it.

Now the performance issues jump out - using this ADP is disgustingly slow. Things that took a couple of seconds in an MDB in 2003 take a LONG time in the ADP. Just switching records on a form is painful.

The SQL database is working fine, and is fast when accessed by anything but the ADP.

I've been searching the internet and keep seeing mention of using an MDB with ODBC to connect to SQL instead of an ADP.

Would this help? Would this be reliable?

Is there a way to do this w/o significant rewrite of the ADP?

The performance currently is DISMAL and inexcusable (thanks Microsoft - apparently ADPs worked better in 2003). I need to improve it drastically.

Start your free trial to view this solution

Question Stats

Zone: Microsoft

Question Asked By: kuhngroup

Solution Provided By: kuhngroup

Participating Experts: 2

Solution Grade: B

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.09.2008 at 03:12PM PDT, ID: 21536694

vadimrapp1:

> upgrade from an Access2003 MDB to an Access2007 ADP using SSMA. After MONTHS of work, I've actually got the ADP working almost completely.

You are late to the party. It's exactly in Office 2007 Microsoft has finally decided that ADP will be depricated in the near future. It's still supported in 2007, but as you might have noticed, "ADP" has disappeared from "create" menu. And for a reason.

> keep seeing mention of using an MDB with ODBC to connect to SQL instead of an ADP.
> Would this help? Would this be reliable?

Yes, with returning to MDB, this will be indeed the only option. However, I wouldn't hold my breath regarding the performance. In these days, performance is not even in the first dozen of priorities. Open any technical magazine, and 9 out of 10 topics will be about security. That's what everybody should be thinking about. Another important consideration for Office 2007 developers was to turn it from a tool into Experience. Hence Ribbon etc. So, the answer to the 2nd question - whether anything related to Office 2007 would be reliable - is negative. Reliability is not a priority either. Security and Experience are. Have fun and be safe. Whore's values.

> Is there a way to do this w/o significant rewrite of the ADP?

No. MDB is totally different from ADP.

That said, try to compile the project into ADE, and use bound forms. When I was beta-testing it, I did not notice any significant performance problems. Are you connecting to sql server in local network?

05.09.2008 at 06:51PM PDT, ID: 21537529

kuhngroup:

Boy this is getting depressing.

What is an ADE (I'm not up on the myriad of Access file types..)??

05.09.2008 at 06:53PM PDT, ID: 21537531

kuhngroup:

And yes - this is on a local network and the server is an almost new, fast, SBS2003 R2 Premium server with SQL Server 2005 Standard.

05.09.2008 at 07:13PM PDT, ID: 21537580

vadimrapp1:

> What is an ADE

compiled ADP. In access 2003 it was under menu Tools/Make ADE file.

Look at http://blogs.msdn.com/access/archive/2007/09/10/company-meeting-and-on-to-m2.aspx

- you are not alone, and performance is not the only problem ahead. Are you sure you want 2007? while there are clearly problems, what advantages are you expecting compared to 2003? the Ribbon?

05.09.2008 at 07:23PM PDT, ID: 21537600

kuhngroup:

Current version of Office - that was the primary reason. Big mistake.

05.19.2008 at 08:26AM PDT, ID: 21598605

kuhngroup:

Performance is fixed (at least it is acceptable now).

I pinpointed the performance problem - using SQL Profiler I saw that Access was sending endless queries to the server trying to get schema info for each object in the database. The same queries over and over - 1000's per second.

These queries would start if a user opened a form, did a filter by form, and then closed the form (whether the filter was still on or not). As soon as they closed the form, the queries would start. They would not stop until the ADP was closed! Nice way to bring a network to its knees when a bunch of users are doing this.

I finally tried a desperation move. I created a new blank ADP in 2007 (which is still an Access 2003 format ADP apparently). I pointed it to my SQL database. I added all forms, reports, macros, etc from the problem ADP to the new one by copying/pasting between them.

The new project works well! No more endless queries. It does get all the schema info when the project starts (I assume to populate the Navigation bar with database info). It runs through the objects once (about 40 queries in my case) and is done. After that, traffic only happens when the project actually needs data.

Yay!

Accepted Solution

05.19.2008 at 11:01AM PDT, ID: 21600048

vadimrapp1:

That's nice to know. But still, could you do what I suggested - compile the slow project into ADE. If that does not help, then there's no objection to your solution.

05.19.2008 at 11:35AM PDT, ID: 21600323

kuhngroup:

I tried to make an ADE out of the ADP that had the performance problem, but Access gave me an error to the effect that the ADP was not an ACCESS 2007 ADP...??

At that point I realized that Access for whatever reason didn't want to play nice with the updated ADP. That is when I decided to create a new ADP and copy everything into it.

Even then, the titlebar of the new ADP says it is in Access 2000-2003 format. I bet I can make an ADE now (and I'll try it), but the performance problem is gone already.

I thank everyone for their suggestions on this by the way. I've learned to never touch an Access project again, but you folks have been very helpful!

05.19.2008 at 12:22PM PDT, ID: 21600710

vadimrapp1:

> I've learned to never touch an Access project again

This would be big mistake. Access ADP is excellent. It's Office 2007 that should be bypassed, same as Vista, same as any other product where Microsoft used offshored QA.

05.19.2008 at 12:44PM PDT, ID: 21600905

kuhngroup:

I agree on Office 2007 (especially Access) and Vista. I've really tried to give them a chance and have had a miserable run with them.

I'm primarily a .NET/SQL/VFP developer. I'm going to stick to those technologies.

The reason I took on this project was to just give my client more reliable data storage for their Access application as a temporary step prior to making a .NET varsion. According to SSMA, this should have taken 25 hours to port this to SQL. I've got over 200 in it so far.

After this nightmare (and I've only mentioned the tip of the iceberg re: the problems I've encountered), I'm going back to technologies that I have real control of.

05.19.2008 at 06:27PM PDT, ID: 21602910

modus_operandi:

A request has been made in Community Support to close this question:
http://www.experts-exchange.com/Q_23414588.html

If there are no objections, a moderator will finalize this question in approximately 4 days as follows:
PAQ with refund using {http:#a21598605}

Please leave any recommendations here.

modus_operandi
EE Moderator

05.23.2008 at 02:02PM PDT, ID: 21635821

Vee_Mod:

Closed, 250 points refunded.
Vee_Mod
Experts Exchange Moderator

vigge79

06.23.2008 at 08:44AM PDT, ID: 21847412

kuhngroup, how did you create a new 2007 adp? I don't have that option in Access 2007

kuhngroup

06.23.2008 at 08:51AM PDT, ID: 21847485

Nice feature in Access 2007 is that you do not have the direct option to create an ADP (Microsoft doesn't want you to do it anymore).

You can Create a new Access database project, and make the project name extension .ADP and it will treat it as a new ADP project.

It works.

20080236-EE-VQP-29 / EE_QW_2_20070628