Replies

Wendell, I’m using the Oracle driver, version 8.01.78.00
– will give the Microsoft driver a go and see if it it’s any better
So far I’ve tried creating a view in Oracle, and that seems to have greatly improved the situation, like my memory usage is about 1/20th of the previous

More on the memory leak. It’s not the code, it’s something even more basic than that.
If I ODBC link an Oracle table with approx. 10,000 records, create a simple query on it, no criteria, just select a few of the fields.
Watch the Msaccess.exe memory in TaskManager, open the query, and it increases by about 300K, but when I close the query, only about 1/2 of the memory comes back
Even worse, if I open the query, then use the CTRL + HOME / END to move forwards and backwards through the records, the memory use just keeps on climbing

And the memory use increases with more tables, e.g. single table 10K records, about 500K, two joined tables, both about 10K records, and memory use is 900K

The actual query that ends up using 1/2 Gb has 5 checks against settings, e.g. like a match for officer, case type, etc
– it’s almost as if it’s doing a cross join, even though the query definitely has a join in it

interesting
Thanks, that’s really interesting Hans, especially the more compact PNG creating a bigger MDB
– guess who got all his images in PNG format

Thanks Charlotte, just looked in my .scc folders and compared some of the ACR files, yeap with image about 600 Kb, without about 15 Kb.
I presume if I go along the route of using a single sub-report in each main report, it should help, as the main report will only have a sub-report control on it

Thanks Charlotte and Hans, for the good suggestions

I’ve tracked back through the report and underlying query.
The problem is in a function that the query uses, not the actual report.
Run the query and watch Task Manager, , there goes 1/2 Gb, yes Gb!, of memory, for only 60 records returned from the Oracle database
That’ll teach me to use someone elses code without proper checking, previously I’d been testing against an Oracle database with only a few record in, now there’s a lot more and it’s showing up the inefficency of the code
– time to see what I can do to improve it.

Hans, thanks for the response
Nope, I can’t guarantee where they’ll put them on their laptops
It’s good to know that the one sub-report means only one copy. I’ll also check out what can be done with reducing the actual images

Another thought I’d had was on what format of image to include
– is say JPEG better than GIF or PNG
I’m assuming that Access “processes” the image in someway, so is one format better than another

Thanks for the suggestion, but the files are already zipped at max(slow) compression setting

Charlotte,
very much appreciate the pointers.
Not sure which of the installers we have, I think it’s Wise. I’ve seen comments about SageKey, all pretty positive.
As to overhauling, well I’ve not got much hair left anyway but I’m with you on the

My feeling is, everything is being driven by the need for new sales
– which is interesting when a recent report highlighted the fact that it cost 5 times as much to get a new customer, as to keep an current customer

The office independence is partly driven by the company concerns, as the Office versus Star Office debate rages amongst our customers,
– should we provide reports in both Office and Star Office (no, please, no ),
– or do we provide in an independent format, thus my enquiry about alternatives to Access

Also my own concerns over the possible support issues if e.g. I went with say Office XP and distributed the run time.
What effects would there be with clients who’ve got full versions of either Office 97 or 2000 already installed

Web stuff, yeap, absolutely dripping
– that said, I’m really taken with the web deployment of a thick client with .NET, seems to be the best of both worlds, I can give them a nice thick and juicy client, but the deployment is just point at a web page.

Charlotte,
the current application(s) are written in Access, with some local tables, but mainly linking to an Oracle database

Agree, I really like Access, but I’m getting pushed for an alternative, factors like :-
– independent of Office version,
– ability to provide the reports via the web (both intra and internet),
– reports as a web service (don’t get me wrong, I like web services)
– simple ad-hoc user report designer, with which they can customise their reports
— Oh and this simple designer should allow users to change/add parameters, sorting & grouping, layout of report, etc.
I did suggest a little program I know called, now what was it ….. oh yes, Microsoft Access, but they didn’t bite

I think a lot of this is being driven by our sales & marketing people, who’ve seen some nice things, but there is just me who does the Access side, so they come up with “neat” ideas, which can be a LOT of work

Also many thanks to the rest with their suggestions. I’ve taken a quick look at Crystal (as part of Visual Studio .NET) and Active Reports.NET
– the plus for Active is the end user designer is royalty free
– the plus for Crystal is it’s used in a lot of places

Thanks for the response Wendell
– yeap, spotted that Compact removes the VSS properties, actually I was wondering how I’d manage to do this, until I did a compact
Have you seen any issues with Oracle linked tables
I rebuild a database from VSS, which had ODBC linked Oracle tables in, however at the time I did the rebuild, that Oracle database wasn’t available.
The mdb is the one I’m having problems with the IRR function, so that may be the cause

Thanks Wendell and Charlotte
this is the cause, as whatever it is, it completely crashes Access
Thankfully I’ve got a copy of Find & Replace, which is similar to Speed Ferret (cheaper, but only for Access), so I’ll give renaming a try

I’ve sort of solved it, using VBScript and AutoItX (the ActiveX version of AutoIt) for the control of the windows, dialogs, and sending key strokes
– the AutoIt is much easier than than VBScript SendKeys, as it can do things like WaitWinActivate, i.e. wait for a specified window to become active

Wendell,

tried this with partially success.
The 2002 to 2000 conversion worked fine
The 2002 to 97 conversion didn’t
– something weird happended to the forms, I could see them in the Forms tab, but couldn’t open them in either Design or Normal mode !!

If it do the process manually, i.e. using the menu options, both 2000 and 97 versions are fine.

I’ve tried using VBScript, using SendKeys with some success, but better is a freeware application, called AutoIt. This has excellent control of “Windows”, so e.g. it can wait until a specific Window is available, like a Open dialog. This avoids problems I was finding with VBScript where an unexpected window messed up the sequence

Wendell,

thanks for trying – glad it wasn’t just me finding the Help, a bit of a non-Help. I’m going to a Microsoft UK focus group on “Access” next month, so I’ll certainly try and bring it up the lack of Help issue

Poking around on the net found this on Google Groups

> But just to automate the process further I'd like to automatically 
> create the MDE when done.  But I haven't found any method of doing so.
Totally unsupported, undocumented, and prone to strange issues, but try:
    SysCmd 603,  , 
where  is the fully qualified path and name of the source database, 
and  is the fully qualified path and name of the mde you want created, 
such as:
    SysCmd 603, "c:myapp.mdb", "c:test.mde"
-- 
Dan Haught
FMS Inc. http://www.fmsinc.com

But while it works in Access 97, doesn’t seem to work in either Access 2000 or 2002
so I think it’ll have to be the SendKeys option, unless anyone knows the equivalent in 2000 and 2002