Access 2013 local form development is slow as molasses, connected to Netezza d.b.

I jumped into Access 2013 and made good progress on a very simple system. The system is on my local machine, connected to Netezza at a local data center across the way 15 miles or so.

The tables I'm using from Netezza are very small, no more than 100 records each. I'm not planning on displaying any really big tables, just some meta tables on a system that uses Netezza.

But development is excruciatingly slow, so much so that it's almost unusable. I will click to run a form, and it's 30 s to 2 minutes chugging. This happens basically all the time. I did have the Access d.b. on a network server, and thought this was the issue. But after copying it to my PC, it's still chugging. So is there something I can do ? Maybe it's the Netezza d.b.

I have compacted and repaired the d.b.
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Netezza at a local data center across the way 15 miles or so.

Distance is not the issue.
Bandwidth quality is.
Access is a LAN technology.
Now, LAN back in the day was as slow as 10 mbps and Access dealt with that.
To get any kind of decent Access WAN performance you need:
1. Decent upload speed.  ISPs advertise the hell out of download speed, but if you can't push jack up, you're screwed.
2. Low latency.  When it takes a mouse-click 2-3 seconds to traverse the network, bad, bad experience results.
3. High reliability.  Dropped packets are death to Access.  A WAN where you have losses any greater than 1-2% won't work out well.

It sounds like you have a really poor connection to your Netezza back-end.
If you cannot fix that, you'll have to abandon Access, or get Remote Desktop to the netezza datacenter working.
Access cannot tolerate poor connections.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
You should be able to run nzsql on the local machine.  Try running your query there and see if there is any difference.  Then try on the Netezza server.

Could be the Netezza tables need to be groomed, but you would have to have a lot of update/delete activity for it to make that much of a difference.
Gadsden ConsultingIT SpecialistAuthor Commented:
Nick67, johnsone,

thanks for the tips. It's probably the bandwidth.

I got my system built and it seems to run ok. It's only when doing development that it starts choking.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also turn OFF Name Autocorrect. This seems to impact Design View as much as anything.

To do that go to Access Options - Current Database, and turn the Name Autocorrect OFF
Gadsden ConsultingIT SpecialistAuthor Commented:
Scott McDaniel - thank you. I'll see how it goes by tomorrow and close out.
Gadsden ConsultingIT SpecialistAuthor Commented:
Hi, I'm working locally, and even when I do a simple thing in designer, it hangs for more than 20 seconds, so I move on.

When running the forms, they run quickly, the Netezza d.b. seems fine. But it's in designer that it keeps choking. For example, its hanging when I close the Property sheet, when I close an open query in Designer, and other similar simple clicks.

I guess I could import the tables to eliminate any network traffic and see what happens. it just seems odd.

The show stopper is eliminated, good!
Now, first try renaming your file to less than eight characters (lessthan.mdb or lessthan.accdb) and place it at the root of a drive.
Even now, a file on \\server\sharename\deep\in\the\filesystem\longfilename.mdb can run REALLY slow where
M:\short.mdb won't

If you haven't turned off subdatasheets in each table already, do so.
They can be a source of slowness as well.

If those don't do the trick, we'll move on to more possibilities.
Gadsden ConsultingIT SpecialistAuthor Commented:
Nice67, excellent, I will try these now. Thx.
Gadsden ConsultingIT SpecialistAuthor Commented:
Gentlemen, I'm terribly sorry this fell off my radar. My project ended, I got a new job, and this flew off my radar. The help was excellent and I can refer to it next time I'm on Access 2013. Thanks, and please accept my apology.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.