creating an access executable - will it speed up querying over odbc?

[SadOldGoth]

Hi Folks,

I'm currently linking to an Oracle database that stores all my data. I need to run queries on that data so I've linked those tables. Trouble is though it's running like a piglet with a blister. Because of the complexity of the queries being run, they're written in VBA.

The program I'm trying to replace has been written using Crytal Reports and takes a 10th of the time to retrieve the information. I've got the developers edition of Access which, I believe, has the access executable creator. Will using this improve the performance?

Thanks,

Jes

 

[Junior1544]

There is no such thing as an access exicutable... What you are thinking is the access run time...

I've never used it, So i don't think that will make any thing faster. But Makeing your mdb into an mde will make the code run faster...

Also, look at where your bottle neck is and see if you can make it run faster...

One thing, with access, to run a regulare access query, it pulls the whole table across the network... you said you're linking to an Oracle database... Can you use a passthrough query to make oracle do the work. It'll run faster that way...

Just my thoughts...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[SadOldGoth]

Hi James,

Thanks for the quick response.

Yep, you're right, I'm thinking on the runtime. Does an mde run faster - that's the first time I've heard that?

There aren't really any bottlenecks, so to speak, just lots of tables and lots of joins, unfortunately. I could import some to make it speedier, but as it's live data the benefits would proabably be minimal. Still there are some code tables I can import.

I would love to be using a pass through, but sadly, we don't own the 'data' source and the company who do have got it built into the contract that any alterations will invalidate our contract, so I can read but not touch. Also, I don't know the Oracle environment ;0) Still, our contract expires fairly soon, and we do have a test environment...hmm...

Once again, thanks,

Jes

 

[Junior1544]

yea, an mde run's faster... but not much... the only reason it run's faster is that all the code is compiled... with an mdb, the code needs to compile the first time it's run, so it's a little slower because it's compiling,...

but an mde is already compiled... There's more to it, but I don't understand the rest

as to the contract... I don't know what to say, if you've got the code as quick as it can be, then that's as good as I think you're gana get...

Sorry...

--James

(p.s. if you start to experiment with mde's, back up your mdb befor hand, because once you make an mde, it can NEVER be converted back to an mdb... so keep your mdb safe, and never deleet it...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.