Problems getting to Data Warehouse with Access

[RSW]

I am a relatively inexperienced user of Microsoft Access, but my current problem has even the experts at my company stumped. I am hoping that someone here can help.

My company has an Oracle-based data warehouse. I can access its data just fine through TOAD and other applications.

However, when trying to link to the tables through Microsoft Access, it takes an extraordinarily long amount of time. My computer makes a noise as if it is searching for a file and not finding it, and Access goes into "Not responding" mode for five minutes or more. But--given enough time--the data WILL come up.

My Access can retrieve data from its own database, and other networked Access databases, just fine.

Does anyone know what might be causing this problem specifically between Access and Oracle?

Thanks in advance.

 

[KeithG]

Are you using ODBC? The slowness probably has to do with how big the Oracle table is.

 

[RSW]

Well...not only does it freeze up when I am trying to load tiny tables, but it USED to run quickly and efficiently. I do indeed use ODBC (although I'm not terribly familiar with what exactly that means). Any ideas?

 

[workmad3]

ODBC - Open Database Connections - is a standard way of communicating with databases.

In cases where efficiency has suddenly dropped, ave a look to things that have changed recently. Has your company upgraded access, oracle, the oracle drivers? anything like that could have had a detrimental effect on efficiency.

 

[RSW]

Well, today I discovered that the problem is apparently on my PC alone. I just cranked up Access on a conference room computer, and I can get to our data warehouse just fine using my regular account.

What drivers or programs on my computer could be messing this up? It's not merely a corrupted Access database, because no Access file, even a brand new one, will work.

 

[The_Doc_Man]

When you see a performance situation like this, my first thought is that your machine is somehow underpowered vs. machines that work better. The problem might not be ORACLE or Access, but WINDOWS.

Things to compare between your conf. room computer and your own workstation:

1. Physical memory. You need Access AND ORACLE's ODBC code co-resident. Windows is a notorious pig on a good day, Access is not a trivial program, and ORACLE is no lightweight either.

2. Swap space. If your physical memory is smaller, Windows has to work its butt off swapping code fragments to make room for what you need. You need to compare swap space to verify that you aren't fighting to make room for things. Windows will swap what it has to, but doesn't like swapping. And let me tell you, when Windows swaps, the world stops.

3. Really badly fragmented disk for the drive holding your Access front end. If your machine is making a lot of disk-seek noises, you could have a really bad problem of putting things on the drive in a scattered fashion. Windows will do that but it won't like it. It will have to do SPLIT I/O functions to do what are called scatter/gather transfers. This eats performance alive. Defrag your disk if you haven't done so recently.

If you can crank up any of the Windows performance tools on the two machines, see if you have something cranking memory around like there is no tomorrow. Look under Start >> Programs >> Accessories >> System Tools to find a performance monitor - unless you have a commercial performance tool installed from one of the advanced system maintenance suites. Defender, McAfee, and Norton have such things. I'm sure others do, too.

 

[RSW]

Thanks--I'll try all that today. The thing is, my PC should be FAR superior to the one in the conference room.

 

[RSW]

Well, not only did none of that work, but I completely removed and re-installed Access, and it still doesn't work. This is one of the most frustrating situations I have ever experienced.